USE safedelivery_courierdb;

TRUNCATE TABLE delivery_branch_transfers;
TRUNCATE TABLE delivery_branch_transfer_details;

TRUNCATE TABLE parcels;
TRUNCATE TABLE parcel_logs;
TRUNCATE TABLE parcel_delivery_payments;
TRUNCATE TABLE parcel_delivery_payment_details;
TRUNCATE TABLE parcel_merchant_delivery_payments;
TRUNCATE TABLE parcel_merchant_delivery_payment_details;

TRUNCATE TABLE parcel_payment_requests;
TRUNCATE TABLE parcel_pickup_requests;

TRUNCATE TABLE return_branch_transfers;
TRUNCATE TABLE return_branch_transfer_details;

TRUNCATE TABLE rider_payments;
TRUNCATE TABLE rider_runs;
TRUNCATE TABLE rider_run_details;

SELECT merchant_id, COUNT(*) AS occurrence_count
FROM merchant_service_area_return_charges
GROUP BY merchant_id
HAVING COUNT(*) > 3;

DELETE merchants, merchant_shops
FROM merchants
LEFT JOIN merchant_shops ON merchants.id = merchant_shops.merchant_id
WHERE merchants.m_id = 'M-0304';

DELETE m, ms
FROM merchants m
LEFT JOIN merchant_shops ms ON m.id = ms.merchant_id
WHERE m.m_id = 'M-0132';

DELETE ms, m FROM merchants m JOIN merchant_shops ms ON m.id = ms.merchant_id WHERE m.m_id ='M-0243';

UPDATE `parcels` SET `return_charge` = '0' WHERE `return_charge` != 0 AND `delivery_type` IN (1,2);

SET @time := NOW();

UPDATE merchants
SET created_at = (@time := DATE_ADD(@time, INTERVAL 1 SECOND))
ORDER BY id ASC;

UPDATE riders
SET contact_number = CONCAT('0', contact_number);

INSERT INTO merchant_shops (shop_name, shop_address, merchant_id, branch_id, district_id, area_id, contact_number, status, is_default, created_at, updated_at)
SELECT
    company_name, 
    address, 
    id AS merchant_id, 
    branch_id, 
    district_id, 
    area_id, 
    contact_number, 
    1 AS status, 
    1 AS is_default, 
    NOW() AS created_at, 
    NOW() AS updated_at
FROM merchants;

UPDATE parcels
SET parcel_code = FLOOR(1000 + RAND() * 8000)
WHERE status IN (14, 24);

SELECT * FROM parcel_merchant_delivery_payments WHERE total_payment_amount != 0 AND total_payment_received_amount = 0 AND status = 2

SELECT p.id
FROM parcels p
JOIN parcel_merchant_delivery_payment_details pd
    ON p.id = pd.parcel_id
WHERE p.merchant_id = 444
  AND p.payment_type != 5;

UPDATE rider_run_details
SET 
    complete_note = 'Out Of Coverage',
    collected_amount = 0,
    status = 7,
    delivery_type = 4
WHERE rider_run_id = 15071;

UPDATE `rider_run_details`
SET 
    `complete_note` = 'Out Of Coverage',
    `collected_amount` = 0,
    `status` = 7,
    `delivery_type` = 4
WHERE `rider_run_id` = 15071;

UPDATE `parcels` SET 
 `status` = '24'
 WHERE `parcel_invoice` IN (

);

UPDATE parcels
SET cod_charge = 0, cod_percent = 0
WHERE merchant_id = 345 AND cod_percent = 1;

ALTER TABLE merchant_shops CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO merchant_shops (shop_name, shop_address, merchant_id, branch_id, district_id, area_id, contact_number, status, is_default, created_at, updated_at)
SELECT 
    company_name AS shop_name, 
    address AS shop_address, 
    id AS merchant_id, 
    branch_id, 
    district_id, 
    area_id, 
    contact_number, 
    1 AS status, 
    1 AS is_default,
    NOW() AS created_at,
    NOW() AS updated_at
FROM 
    merchants;

TRUNCATE TABLE easyfast_courierdb.delivery_branch_transfers;
TRUNCATE TABLE easyfast_courierdb.delivery_branch_transfer_details;

TRUNCATE TABLE easyfast_courierdb.parcels;
TRUNCATE TABLE easyfast_courierdb.parcel_logs;
TRUNCATE TABLE easyfast_courierdb.parcel_delivery_payments;
TRUNCATE TABLE easyfast_courierdb.parcel_delivery_payment_details;

TRUNCATE TABLE easyfast_courierdb.parcel_merchant_delivery_payments;
TRUNCATE TABLE easyfast_courierdb.parcel_merchant_delivery_payment_details;

TRUNCATE TABLE easyfast_courierdb.parcel_payment_requests;
TRUNCATE TABLE easyfast_courierdb.parcel_pickup_requests;


TRUNCATE TABLE easyfast_courierdb.pathao_orders;
TRUNCATE TABLE easyfast_courierdb.pathao_order_details;
TRUNCATE TABLE easyfast_courierdb.pathao_order_detail_logs;
TRUNCATE TABLE easyfast_courierdb.pathao_order_details;


TRUNCATE TABLE easyfast_courierdb.return_branch_transfers;
TRUNCATE TABLE easyfast_courierdb.return_branch_transfer_details;

TRUNCATE TABLE easyfast_courierdb.rider_payments;
TRUNCATE TABLE easyfast_courierdb.rider_runs;
TRUNCATE TABLE easyfast_courierdb.rider_run_details;






TRUNCATE TABLE easyfast_courierdb.branches;
TRUNCATE TABLE easyfast_courierdb.branch_users;

TRUNCATE TABLE easyfast_courierdb.branch_users;

TRUNCATE TABLE easyfast_courierdb.expenses;
TRUNCATE TABLE easyfast_courierdb.email_verifications;

TRUNCATE TABLE easyfast_courierdb.merchants;
TRUNCATE TABLE easyfast_courierdb.merchant_service_area_charges;
TRUNCATE TABLE easyfast_courierdb.merchant_service_area_cod_charges;
TRUNCATE TABLE easyfast_courierdb.merchant_service_area_return_charges;

TRUNCATE TABLE easyfast_courierdb.news_letters;
TRUNCATE TABLE easyfast_courierdb.notifications;

TRUNCATE TABLE easyfast_courierdb.riders;
TRUNCATE TABLE easyfast_courierdb.send_boxes;

TRUNCATE TABLE easyfast_courierdb.service_area_weight_package;
TRUNCATE TABLE easyfast_courierdb.staff;
TRUNCATE TABLE easyfast_courierdb.staff_payments;
TRUNCATE TABLE easyfast_courierdb.visitor_messages;

DELETE FROM `merchants` WHERE `id` NOT IN ( SELECT DISTINCT `merchant_id` FROM `parcels` WHERE `merchant_id` IS NOT NULL );