SET FOREIGN_KEY_CHECKS=0;
START TRANSACTION;

USE `sales_stock_v1`;

-- 1) Remove employee/payroll data model completely
DROP TABLE IF EXISTS payroll_payments;
DROP TABLE IF EXISTS payroll_run_items;
DROP TABLE IF EXISTS payroll_runs;
DROP TABLE IF EXISTS employee_adjustments;
DROP TABLE IF EXISTS employee_custody_settlements;
DROP TABLE IF EXISTS employee_custodies;
DROP TABLE IF EXISTS employee_advance_settlements;
DROP TABLE IF EXISTS employee_advances;
DROP TABLE IF EXISTS employees;

-- Remove old employee links left in payments if the column still exists
SET @has_emp_col := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='cash_payments' AND COLUMN_NAME='employee_custody_id'
);
SET @sql := IF(@has_emp_col>0, 'ALTER TABLE cash_payments DROP COLUMN employee_custody_id', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2) Remove legacy customer/supplier ledgers
DROP TABLE IF EXISTS customer_ledger;
DROP TABLE IF EXISTS supplier_ledger;

-- 3) Remove legacy master tables now that parties is the source of truth
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS suppliers;

-- 4) Remove employee/payroll permissions and role links
DELETE rp FROM role_permissions rp
JOIN permissions p ON p.id = rp.permission_id
WHERE p.code LIKE 'employees.%' OR p.code LIKE 'payroll.%';

DELETE FROM permissions
WHERE code LIKE 'employees.%' OR code LIKE 'payroll.%';

-- 5) Normalize party flags from transactional use
UPDATE parties p
SET is_customer = 1
WHERE EXISTS (
  SELECT 1 FROM sales_invoices si WHERE COALESCE(si.party_id, si.customer_id) = p.id
)
   OR EXISTS (
  SELECT 1 FROM customer_receipts cr WHERE COALESCE(cr.party_id, cr.customer_id) = p.id
)
   OR EXISTS (
  SELECT 1 FROM sales_returns sr WHERE COALESCE(sr.party_id, sr.customer_id) = p.id
);

UPDATE parties p
SET is_supplier = 1
WHERE EXISTS (
  SELECT 1 FROM purchase_invoices pi WHERE COALESCE(pi.party_id, pi.supplier_id) = p.id
)
   OR EXISTS (
  SELECT 1 FROM supplier_payments sp WHERE COALESCE(sp.party_id, sp.supplier_id) = p.id
)
   OR EXISTS (
  SELECT 1 FROM purchase_returns pr WHERE COALESCE(pr.party_id, pr.supplier_id) = p.id
)
   OR EXISTS (
  SELECT 1 FROM cash_payments cp WHERE cp.party_id = p.id
)
   OR EXISTS (
  SELECT 1 FROM items i WHERE i.default_supplier_id = p.id
);

-- 6) Backfill party_id in any remaining legacy rows
UPDATE sales_invoices SET party_id = customer_id WHERE party_id IS NULL AND customer_id IS NOT NULL;
UPDATE sales_returns SET party_id = customer_id WHERE party_id IS NULL AND customer_id IS NOT NULL;
UPDATE customer_receipts SET party_id = customer_id WHERE party_id IS NULL AND customer_id IS NOT NULL;
UPDATE purchase_invoices SET party_id = supplier_id WHERE party_id IS NULL AND supplier_id IS NOT NULL;
UPDATE purchase_returns SET party_id = supplier_id WHERE party_id IS NULL AND supplier_id IS NOT NULL;
UPDATE supplier_payments SET party_id = supplier_id WHERE party_id IS NULL AND supplier_id IS NOT NULL;
UPDATE cash_payments SET party_id = supplier_id WHERE party_id IS NULL AND supplier_id IS NOT NULL;

COMMIT;
SET FOREIGN_KEY_CHECKS=1;