# CATEGORY MIGRATION
INSERT INTO kbrl.categories
    (select c.id,
            (CASE
                 WHEN c.parent_id = 1 then null
                 ELSE c.parent_id
                END)                    as parent_id,
            ct.name,
            ct.slug,
            (CASE
                 WHEN parent_id is null or parent_id = 0 THEN ct.slug
                 ELSE CONCAT((SELECT slug from kbrl_ecom_live.category_translations where category_id = parent_id), '/',
                             ct.slug)
                END)                    as url_postfix,
            c.position,
            (CASE
                 WHEN ct.emi_month IS NULL then 0
                 ELSE ct.emi_month END) as emi_month,
            ct.preorder_qty,
            ct.cashback_amount,
            ct.preorder_amount,
            c.display_mode,
            c.status,
            ct.is_cashback_active,
            ct.is_emi_active,
            ct.is_pre_register_active,
            ct.is_preorder_active,
            ct.product_listing_badge_flag,
            ct.product_pdp_badge_flag,
            ct.hide_oos,
            ct.order_by_oos,
            ct.meta_title,
            ct.meta_description,
            ct.meta_keywords,
            ct.description,
            ct.cashback_html_list,
            ct.cashback_html_view,
            ct.cashback_pdp_offer,
            ct.emi_text,
            ct.preorder_liner,
            ct.preorder_desc,
            ct.product_listing_badge_description,
            ct.product_pdp_badge_description,
            ct.banner_html_mobile,
            ct.banner_html_web,
            ct.pdp_description,
            ct.pdp_description_style,
            null                        as video,
            NOW()                       as created_at,
            NOW()                       as updated_at
     from kbrl_ecom_live.categories as c
              inner join kbrl_ecom_live.category_translations as ct on ct.category_id = c.id
     where c.id != 1);

update kbrl.categories
set product_listing_badge_description = '<span class="ctm-oos-list-badge" style="display: contents;">
<span class="badge badge-danger">Out of Stock </span>
</span>';

update kbrl.categories
set product_pdp_badge_description = '<span class="ctm-oos-pdp-badge" style="display: contents;">
<span class="badge badge-danger">Out Of Stock</span>
</span>';

update kbrl.categories
set cashback_html_list = '<span class="cashbackInfo">
    <small class="inclusiveOnSet">HDFC Cards Offer Price:&nbsp;</small><strong>[[final_price]]</strong>
    <small class="inclusiveOnSet"> inclusive of </small> <strong>[[cashback_price]]</strong>
    <small class="inclusiveOnSet"> cashback</small>
</span>';

update kbrl.categories
set cashback_html_view = '<span class="cashbackInfo">
    <small class="inclusiveOnSet">HDFC Cards Offer Price:&nbsp;</small><strong>[[final_price]]</strong>
    <small class="inclusiveOnSet"> inclusive of </small> <strong>[[cashback_price]]</strong>
    <small class="inclusiveOnSet"> cashback</small>
</span>';

update kbrl.categories
set emi_text = '<span class="specialPrice"><strong>[[final_price]]</strong> <span class="priceEmi">or EMI [[emi_per_month]]*</span></span>';

# PRODUCT MIGRATION
Insert into kbrl.products (
    select p.id,
           p.sku,
           p.type,
           p.attribute_family_id,
           109                           as category_id,
           pf.name,
           pf.url_key                    as slug,
           pf.url_key                    as url_postfix,
           pf.new,
           pf.featured,
           pf.status,
           pf.price,
           (CASE
                WHEN pf.special_price is null then 0
                ELSE pf.special_price
               END)                      as special_price,
           pf.short_description,
           pf.meta_title,
           pf.meta_keywords,
           pf.meta_description,
           (CASE
                WHEN pf.PartNo is null then ''
                ELSE pf.PartNo
               END)                      as part_no,
           pf.part_no_secondary,
           pf.Standard_EMI_CC            as standard_emi_cc,
           (CASE
                WHEN pf.No_Cost_EMI_Finance is null then 0
                ELSE pf.No_Cost_EMI_Finance
               END)                      as no_cost_emi_finance,
           pf.No_Cost_EMI_Finance_Months as no_cost_emi_finance_months,
           pf.Cashback                   as cashback,
           (CASE
                WHEN pf.cashback_amount is null then 0
                ELSE pf.cashback_amount
               END)                      as cashback_amount,
           pf.cashback_pdp_offer,
           (CASE
                WHEN pf.product_order is null then 0
                ELSE pf.product_order
               END)                      as product_order,
           pf.Standard_EMI_DC            as standard_emi_dc,
           (CASE
                WHEN pf.No_Cost_EMI is null then 0
                ELSE pf.No_Cost_EMI
               END)                      as no_cost_emi,
           pf.pdp_description,
           pf.pdp_description_style,
           pf.video_url,
           pf.created_at,
           pf.updated_at
    from kbrl_ecom_live.products as p
             inner join kbrl_ecom_live.product_flat as pf on p.id = pf.product_id);

# PRODUCT INVENTORIES
insert into kbrl.product_inventories
    (select pi.id,
            pi.product_id,
            pi.qty,
            pi.local_warehouse_qty,
            pi.mumbai_warehouse_qty,
            pi.include_local_warehouse_qty,
            pi.include_mumbai_warehouse_qty,
            now(),
            now()
     from kbrl.products as p
              inner join kbrl_ecom_live.product_inventories as pi on p.id = pi.product_id);

#product_categories
INSERT INTO kbrl.category_products
    (select pc.product_id as products_id,
            pc.category_id
     from kbrl_ecom_live.product_categories as pc
              inner join kbrl.products as p on pc.product_id = p.id
              inner join kbrl.categories as c on pc.category_id = c.id);

/*url_postfix and parent category*/
update kbrl.products as a inner join
    (select p.id,
            p.name,
            cp.*,
            c.id                               as temp_cate_id,
            c.slug,
            c.url_postfix,
            concat(c.url_postfix, '/', p.slug) as p_slug
     from kbrl.products as p
              inner join kbrl.category_products as cp on p.id = cp.products_id
              inner join kbrl.categories as c on cp.category_id = c.id
     group by p.id) as b on b.id = a.id
set a.category_id = b.category_id,
    a.url_postfix = b.p_slug;

# View category products
create or replace view vw_category_products as
select `cp`.`products_id`                                                                                  AS `products_id`,
       `cp`.`category_id`                                                                                  AS `category_id`,
       `p`.`product_order`                                                                                 AS `product_order`,
       (case when ((case when (`p`.`type` = 'virtual') then 1 else `pi`.`qty` end) > 0) then 1 else 0 end) AS `in_stock`
from ((`kbrl`.`category_products` `cp` join `kbrl`.`products` `p` on ((`cp`.`products_id` = `p`.`id`)))
         join `kbrl`.`product_inventories` `pi` on ((`pi`.`product_id` = `p`.`id`)));



# VIEW ORDERS...
create definer = root@`%` view vw_orders as
select `o`.`id`                                         AS `id`,
       `o`.`customer_id`                                AS `customer_id`,
       `o`.`coupon_id`                                  AS `coupon_id`,
       `o`.`increment_id`                               AS `increment_id`,
       `o`.`payment_status`                             AS `payment_status`,
       `o`.`order_status`                               AS `order_status`,
       `o`.`payment_response_dump`                      AS `payment_response_dump`,
       `o`.`items_count`                                AS `items_count`,
       `o`.`base_sub_total`                             AS `base_sub_total`,
       `o`.`base_discount_amount`                       AS `base_discount_amount`,
       `o`.`base_grand_total`                           AS `base_grand_total`,
       `o`.`base_paid_amount`                           AS `base_paid_amount`,
       `o`.`base_balance_amount`                        AS `base_balance_amount`,
       `o`.`is_invoiced`                                AS `is_invoiced`,
       `o`.`is_shipped`                                 AS `is_shipped`,
       `o`.`is_delivered`                               AS `is_delivered`,
       `o`.`is_cancelled`                               AS `is_cancelled`,
       `o`.`is_refunded`                                AS `is_refunded`,
       `o`.`is_balance_pay_active`                      AS `is_balance_pay_active`,
       `o`.`created_at`                                 AS `created_at`,
       `o`.`updated_at`                                 AS `updated_at`,
       ((case
             when ((`o`.`base_balance_amount` > 0) and (`p`.`payment_key` <> 'CASH-ON-DELIVERY') and
                   (`p`.`payment_key` <> 'BAJAJ-FINANCE')) then 'YES'
             else 'NO' end) collate utf8mb4_general_ci) AS `is_pre_order`,
       `p`.`payment_key`                                AS `payment_key`,
       `oa`.`name`                                      AS `name`,
       `oa`.`city`                                      AS `city`,
       `oa`.`state`                                     AS `state`,
       `oi`.`invoice_no`                                AS `invoice_no`,
       `oi`.`invoice_date`                              AS `invoice_date`,
       `opu`.`pickedup_at`                              AS `pickedup_at`,
       `od`.`delivery_date`                             AS `delivery_date`,
       group_concat(`oii`.`branch` separator ',')       AS `invoice_branchs`
from ((((((`kbrl`.`orders` `o`
    join `kbrl`.`order_payments` `p` on ((`o`.`id` = `p`.`order_id`)))
    join `kbrl`.`order_addresses` `oa` on ((`o`.`id` = `oa`.`order_id`)))
    left join `kbrl`.`order_invoices` `oi` on ((`oi`.`order_id` = `o`.`id`)))
    left join `kbrl`.`order_invoice_items` `oii` on ((`oi`.`id` = `oii`.`invoice_id`)))
    left join `kbrl`.`order_pick_ups` `opu` on ((`opu`.`order_id` = `o`.`id`)))
         left join `kbrl`.`order_deliveries` `od` on ((`od`.`order_id` = `o`.`id`)))
where (`oa`.`address_type` = 'billing')
group by `o`.`id`;

# PAGES MIGRATION
INSERT INTO kbrl.cms_pages (
    select cms_page_translations.id,
           cms_page_translations.meta_description,
           cms_page_translations.meta_keywords,
           cms_page_translations.meta_title,
           cms_page_translations.page_title,
           cms_page_translations.url_key,
           cms_page_translations.html_content,
           cms_page_translations.is_compare_page,
           now() as created_at,
           now() as updated_at
    from kbrl_ecom_live.cms_page_translations
);

# PRODUCT ADDONS
insert into kbrl.product_addons (select ap.addon_product_id as id,
                                        ap.child_product_id as product_id,
                                        ap.parent_id,
                                        (CASE
                                             when ap.addon_type = 'cate' then 'App\\Models\\Category'
                                             when ap.addon_type = 'pro' then 'App\\Models\\Products'
                                            END)            as parent_type,
                                        ap.created_at,
                                        ap.updated_at
                                 from kbrl_ecom_live.addon_products as ap);

# PRODUCT RELATED for PRODUCTS
insert into kbrl.product_related (select (@cnt := @cnt + 1)      AS id,
                                         pr.child_id             as product_id,
                                         pr.parent_id,
                                         'App\\Models\\Products' as parent_type,
                                         now()                   as created_at,
                                         now()                   as updated_at
                                  from kbrl_ecom_live.product_relations as pr
                                           CROSS JOIN (SELECT @cnt := 0) AS dummy);

# PRODUCT RELATED FOR CATEGORIES
insert into kbrl.product_related (select (@cnt := @cnt + 1)      AS id,
                                         crp.child_product_id    as product_id,
                                         crp.parent_id,
                                         'App\\Models\\Category' as parent_type,
                                         now()                   as created_at,
                                         now()                   as updated_at
                                  from kbrl_ecom_live.category_related_products as crp
                                           CROSS JOIN (SELECT @cnt := 832) AS dummy);

# UpsellingProducts ===> products
insert into kbrl.product_up_sellings (select (@cnt := @cnt + 1)      AS id,
                                             pr.child_id             as product_id,
                                             pr.parent_id,
                                             'App\\Models\\Products' as parent_type,
                                             now()                   as created_at,
                                             now()                   as updated_at
                                      from kbrl_ecom_live.product_up_sells as pr
                                               CROSS JOIN (SELECT @cnt := 0) AS dummy);

# UpsellingProducts ===> category
insert into kbrl.product_up_sellings (select (@cnt := @cnt + 1)      AS id,
                                             crp.child_product_id    as product_id,
                                             crp.parent_id,
                                             'App\\Models\\Category' as parent_type,
                                             now()                   as created_at,
                                             now()                   as updated_at
                                      from kbrl_ecom_live.category_up_selling_products as crp
                                               CROSS JOIN (SELECT @cnt := 27) AS dummy);

# CrosssellingProducts ===> products
insert into kbrl.product_cross_sellings (select (@cnt := @cnt + 1)      AS id,
                                                pr.child_id             as product_id,
                                                pr.parent_id,
                                                'App\\Models\\Products' as parent_type,
                                                now()                   as created_at,
                                                now()                   as updated_at
                                         from kbrl_ecom_live.product_cross_sells as pr
                                                  CROSS JOIN (SELECT @cnt := 0) AS dummy);

# UpsellingProducts ===> category
insert into kbrl.product_cross_sellings (select (@cnt := @cnt + 1)      AS id,
                                                crp.child_product_id    as product_id,
                                                crp.parent_id,
                                                'App\\Models\\Category' as parent_type,
                                                now()                   as created_at,
                                                now()                   as updated_at
                                         from kbrl_ecom_live.category_cross_selling_products as crp
                                                  CROSS JOIN (SELECT @cnt := 2) AS dummy);

# ADMIN roles migration
insert into kbrl.admin_roles (select id, name, permission_type, permissions, created_at, updated_at
                              from kbrl_ecom_live.roles);

# Admin user data migration
insert into kbrl.admins (select id,
                                role_id,
                                admins.name,
                                email,
                                password,
                                status,
                                0 as is_ip_enabled,
                                admins.created_at,
                                admins.updated_at
                         from kbrl_ecom_live.admins);


create trigger on_update_category
    before update
    on categories
    for each row
BEGIN
    DECLARE url_postfix varchar(500);

    IF (NEW.parent_id IS NOT NULL OR NEW.parent_id <> '') THEN
        SET @url_postfix := (select categories.url_postfix from kbrl.categories where id = NEW.parent_id);
    ELSE
        SET @url_postfix := null;
    END IF;

    IF (@url_postfix IS NOT NULL OR @url_postfix <> '') THEN
        SET NEW.url_postfix = concat(@url_postfix, '/', NEW.slug);
    ELSE
        SET NEW.url_postfix = NEW.slug;
    END IF;
END;

create trigger on_update_product
    before update
    on products
    for each row
BEGIN
    DECLARE url_postfix varchar(500);
    SET @url_postfix := (select categories.url_postfix from kbrl.categories where id = NEW.category_id);
    SET NEW.url_postfix = concat(@url_postfix, '/', NEW.slug);
END;


# CUSTOMER data migration
insert into kbrl.customers (
    select c.id,
           c.name   as full_name,
           c.email,
           c.phone,
           c.status,
           1        as is_verified,
           c.is_opt_in,
           c.password,
           (CASE
                WHEN c.notes = 'Facebook' then 'Facebook'
                WHEN c.notes = 'Google' then 'Google'
                ELSE 'Web'
               END) as registration_type,
           c.remember_token,
           null     as last_login_at,
           c.created_at,
           c.updated_at
    from kbrl_ecom_live.customers c
);
insert into kbrl.customer_addresses (
    select ca.id,
           ca.customer_id,
           ca.name,
           (CASE
                WHEN ca.email IS NULL then '-'
                ELSE ca.email
               END) as email,
           (CASE
                WHEN ca.address1 IS NULL then '-'
                ELSE ca.address1
               END) as address,
           (CASE
                WHEN ca.country IS NULL then '-'
                ELSE ca.country
               END) as country,
           (CASE
                WHEN ca.state IS NULL then '-'
                ELSE ca.state
               END) as state,
           (CASE
                WHEN ca.city IS NULL then '-'
                ELSE ca.city
               END) as city,
           (CASE
                WHEN CHAR_LENGTH(ca.postcode) > 10 THEN '-'
                WHEN ca.postcode IS NULL then '-'
                ELSE ca.postcode
               END) as postcode,
           ca.phone,
           ca.company_name,
           ca.gst_no,
           ca.created_at,
           ca.updated_at
    from kbrl_ecom_live.customer_addresses ca
             join kbrl.customers c on ca.customer_id = c.id
);

# ORDERS DATA MIGRATION
INSERT INTO kbrl.orders(
    select o.id,
           o.customer_id,
           null     as coupon_id,
           o.increment_id,
           o.status as payment_status,
           o.order_status,
           o.payment_response_dump,
           o.total_item_count,
           o.base_sub_total,
           o.base_discount_amount,
           o.base_grand_total,
           o.base_paid_amount,
           o.base_balance_amount,
           (CASE
                WHEN o.grand_total_invoiced > 0 then 1
                ELSE 0
               END) as is_invoiced,
           (CASE
                WHEN o.order_status = 'ORDER SHIPMENT INITIATED' then 1
                WHEN o.order_status = 'ORDER DELIVERED' then 1
                ELSE 0
               END) as is_picked_up,
           (CASE
                WHEN o.order_status = 'ORDER SHIPMENT INITIATED' then 1
                WHEN o.order_status = 'ORDER DELIVERED' then 1
                ELSE 0
               END) as is_shipped,
           (CASE
                WHEN o.order_status = 'ORDER DELIVERED' then 1
                ELSE 0
               END) as is_delivered,
           (CASE
                WHEN o.order_status = 'ORDER CANCELLED - PAYMENT FAILED' then 1
                WHEN o.order_status = 'ORDER CANCELLED' then 1
                WHEN o.order_status = 'ORDER CANCELLED - REFUND INITIATED' then 1
                ELSE 0
               END) as is_cancelled,
           (CASE
                WHEN o.order_status = 'REFUND PROCESSED' then 1
                ELSE 0
               END) as is_shipped,
           0        as is_force_confirmed,
           o.is_balance_pay_active,
           o.created_at,
           o.updated_at
    from kbrl_ecom_live.orders o
             join kbrl.customers c on o.customer_id = c.id
    where o.order_status <> 'ORDER CANCELLED'
);
#ORDER ADDRESS
INSERT INTO kbrl.order_addresses(
    select oa.id,
           oa.order_id,
           oa.name,
           oa.email,
           oa.address1,
           oa.country,
           oa.state,
           oa.city,
           oa.postcode,
           oa.phone,
           oa.company_name,
           oa.gst_no,
           oa.address_type,
           oa.created_at,
           oa.updated_at
    from kbrl_ecom_live.order_address oa
             join kbrl.orders o on oa.order_id = o.id
);
#Order Items
INSERT INTO kbrl.order_items(
    select oi.id,
           oi.order_id,
           oi.product_id,
           oi.qty_ordered,
           p.price as base_price,
           oi.base_price as base_final_price,
           oi.base_discount_amount,
           oi.base_total,
           oi.created_at,
           oi.updated_at
    from kbrl_ecom_live.order_items oi
             join kbrl.products p on oi.product_id = p.id
             join kbrl.orders o on oi.order_id = o.id
);
#ORDER INVOICES
INSERT INTO kbrl.order_invoices(
    select i.id,
           i.order_id,
           i.invoice_no,
           i.invoice_date,
           '-' as invoice_file_path,
           i.e_way_bill_no,
           '-' as e_way_bill_file,
           i.created_at,
           i.updated_at
    from kbrl_ecom_live.invoices i
             #join kbrl.products p on oi.product_id = p.id
             join kbrl.orders o on i.order_id = o.id
);

Set foreign_key_checks = 0;
INSERT INTO kbrl.order_invoice_items(
    select ii.id,
           ii.invoice_id,
           ii.order_item_id,
           (CASE
                WHEN ii.branch IS NULL then '-'
                ELSE ii.branch
               END) as branch,
           (CASE
                WHEN ii.serial_imei_no IS NULL then '-'
                ELSE ii.serial_imei_no
               END) as serial_imei_no,
           ii.qty,
           ii.created_at,
           ii.updated_at
    from kbrl_ecom_live.invoice_items ii
             join kbrl.order_invoices oi on oi.id = ii.invoice_id
);

#ORDER SHIPMENT DATA MIGRATION
INSERT INTO kbrl.order_shipments(
    select s.id,
           s.order_id,
           (CASE
                WHEN s.carrier_title = 'Blue Dart' then 'blue_dart'
                ELSE s.carrier_title
               END) as carrier_title,
           s.track_number,
           s.created_at,
           s.updated_at
    from kbrl_ecom_live.shipments s
             join kbrl.orders o on s.order_id = o.id
);

Set foreign_key_checks = 0;
INSERT INTO kbrl.order_shipment_items(
    select si.id,
           si.shipment_id,
           si.order_item_id,
           si.qty,
           si.created_at,
           si.updated_at
    from kbrl_ecom_live.shipment_items si
             join kbrl.order_shipments os on os.id = si.shipment_id
);

#ORDER PICKUP DATA MIGRATION
INSERT INTO kbrl.order_pick_ups(
    select op.id,
           op.order_id,
           op.pickedup_at,
           op.created_at,
           op.updated_at
    from kbrl_ecom_live.order_pickups op
             join kbrl.orders o on op.order_id = o.id
);

#ORDER DELIVERY DATA MIGRATION
INSERT INTO kbrl.order_deliveries(
    select od.id,
           od.order_id,
           od.delivery_date,
           od.created_at,
           od.updated_at
    from kbrl_ecom_live.order_deliveries od
             join kbrl.orders o on od.order_id = o.id
);

#ORDER REFUNDS
INSERT INTO kbrl.order_refunds(
    select r.id,
           r.order_id,
           r.refund_id,
           r.refund_amount,
           r.cancellation_amount,
           r.created_at,
           r.updated_at
    from kbrl_ecom_live.refunds r
             join kbrl.orders o on r.order_id = o.id
);

#ORDER pAYMENTS
INSERT INTO kbrl.order_payments(
    select op.id,
           op.order_id,
           (CASE
                WHEN op.method = 'cashondelivery' then 'CASH-ON-DELIVERY'
                WHEN op.method = 'razorpay' then 'RAZOR-PAY'
                WHEN op.method = 'moneytransfer' then 'MONEY-TRANSFER'
                WHEN op.method = 'pinelab' then 'PINELAB-CC-DC'
                WHEN op.method = 'PINELAB-CC/DC' then 'PINELAB-CC-DC'
                ELSE op.method
               END) as payment_key,
           (CASE
                WHEN op.cod_mode IS NULL then 'full'
                ELSE op.cod_mode
               END) as mode,
           (CASE
                WHEN op.cod_value IS NULL then 0
                ELSE op.cod_value
               END) as mode_value,
           op.created_at,
           op.updated_at
    from kbrl_ecom_live.order_payment op
             join kbrl.orders o on op.order_id = o.id
);

#PRE-REGISTER DATA MIGRATION
Set FOREIGN_KEY_CHECKS = 0;
insert into kbrl.pre_register_products (select (@cnt := @cnt + 1)      AS id,
                                               prp.customer_id,
                                               prp.product_id,
                                               prp.name,
                                               prp.email,
                                               prp.phone,
                                               prp.created_at,
                                               prp.updated_at
                                        from kbrl_ecom_live.pre_register_products prp
                                                 join kbrl.products p on prp.product_id = p.id
                                                 CROSS JOIN (SELECT @cnt := 0) AS dummy);

#NOTIFY ME
Set FOREIGN_KEY_CHECKS = 0;
insert into kbrl.notify_me_products (select rnm.id,
                                            rnm.customer_id,
                                            rnm.product_id,
                                            rnm.name,
                                            rnm.email,
                                            rnm.phone,
                                            rnm.created_at,
                                            rnm.updated_at
                                     from kbrl_ecom_live.rnr_notify_me rnm
                                              join kbrl.products p on rnm.product_id = p.id);

#STOCK CHECKER
insert into kbrl.check_stocks (
    select sc.id,
           sc.part_no,
           sc.primary_local,
           sc.secondary_local,
           sc.primary_mumbai,
           sc.secondary_mumbai,
           sc.created_at,
           sc.updated_at
    from kbrl_ecom_live.stock_checker sc
);

#video DATA MIGRATION
update kbrl.categories a inner join (
    select *
    from kbrl_ecom_live.videos v where v.type = 'CATEGORY'
) as b SET a.video = b.path where a.id = b.foreign_table_id;
