-- Phase 01: unify customers/suppliers under parties
-- Run after importing the dump, then replace project files with the updated zip.

SET FOREIGN_KEY_CHECKS=0;
SET @db := DATABASE();

CREATE TABLE IF NOT EXISTS `parties` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(30) DEFAULT NULL,
  `name` varchar(160) NOT NULL,
  `phone` varchar(60) DEFAULT NULL,
  `phone2` varchar(60) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `tax_no` varchar(60) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `opening_balance` decimal(14,2) NOT NULL DEFAULT 0.00,
  `credit_limit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `is_customer` tinyint(1) NOT NULL DEFAULT 0,
  `is_supplier` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_parties_code` (`code`),
  KEY `idx_parties_name` (`name`),
  KEY `idx_parties_phone` (`phone`),
  KEY `idx_parties_roles` (`is_customer`,`is_supplier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `party_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `party_id` bigint(20) UNSIGNED NOT NULL,
  `doc_type` varchar(40) NOT NULL,
  `doc_id` bigint(20) UNSIGNED NOT NULL,
  `doc_no` varchar(50) DEFAULT NULL,
  `doc_date` date NOT NULL,
  `debit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `notes` varchar(255) DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_pl_party_date` (`party_id`,`doc_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `sales_invoices` ADD COLUMN IF NOT EXISTS `party_id` bigint(20) UNSIGNED NULL AFTER `customer_id`;
ALTER TABLE `purchase_invoices` ADD COLUMN IF NOT EXISTS `party_id` bigint(20) UNSIGNED NULL AFTER `supplier_id`;
ALTER TABLE `purchase_returns` ADD COLUMN IF NOT EXISTS `party_id` bigint(20) UNSIGNED NULL AFTER `supplier_id`;
ALTER TABLE `customer_receipts` ADD COLUMN IF NOT EXISTS `party_id` bigint(20) UNSIGNED NULL AFTER `customer_id`;
ALTER TABLE `cash_payments` ADD COLUMN IF NOT EXISTS `party_id` bigint(20) UNSIGNED NULL AFTER `supplier_id`;

INSERT IGNORE INTO permissions (`code`,`name`,`module`) VALUES
('parties.view','عرض الأطراف','parties'),
('parties.edit','إضافة/تعديل الأطراف','parties');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, p.id FROM permissions p WHERE p.code IN ('parties.view','parties.edit');

-- Backfill parties from customers
INSERT INTO parties (code,name,phone,phone2,email,address,tax_no,notes,opening_balance,credit_limit,is_customer,is_supplier,is_active,created_by)
SELECT c.code,c.name,c.phone,NULL,c.email,c.address,NULL,NULL,COALESCE(c.opening_balance,0),COALESCE(c.credit_limit,0),1,0,COALESCE(c.is_active,1),NULL
FROM customers c
LEFT JOIN parties p ON (
    (c.code IS NOT NULL AND c.code <> '' AND p.code = c.code)
    OR (p.name = c.name AND COALESCE(p.phone,'') = COALESCE(c.phone,''))
)
WHERE c.name IS NOT NULL AND c.name <> '' AND p.id IS NULL;

UPDATE parties p
JOIN customers c ON (
    (c.code IS NOT NULL AND c.code <> '' AND p.code = c.code)
    OR (p.name = c.name AND COALESCE(p.phone,'') = COALESCE(c.phone,''))
)
SET p.is_customer = 1,
    p.credit_limit = GREATEST(COALESCE(p.credit_limit,0), COALESCE(c.credit_limit,0)),
    p.is_active = GREATEST(COALESCE(p.is_active,1), COALESCE(c.is_active,1));

-- Backfill parties from suppliers
INSERT INTO parties (code,name,phone,phone2,email,address,tax_no,notes,opening_balance,credit_limit,is_customer,is_supplier,is_active,created_by)
SELECT s.code,s.name,s.phone,NULL,s.email,s.address,s.tax_no,NULL,COALESCE(s.opening_balance,0),0,0,1,COALESCE(s.is_active,1),NULL
FROM suppliers s
LEFT JOIN parties p ON (
    (s.code IS NOT NULL AND s.code <> '' AND p.code = s.code)
    OR (p.name = s.name AND COALESCE(p.phone,'') = COALESCE(s.phone,''))
)
WHERE s.name IS NOT NULL AND s.name <> '' AND p.id IS NULL;

UPDATE parties p
JOIN suppliers s ON (
    (s.code IS NOT NULL AND s.code <> '' AND p.code = s.code)
    OR (p.name = s.name AND COALESCE(p.phone,'') = COALESCE(s.phone,''))
)
SET p.is_supplier = 1,
    p.tax_no = COALESCE(NULLIF(p.tax_no,''), s.tax_no),
    p.is_active = GREATEST(COALESCE(p.is_active,1), COALESCE(s.is_active,1));

-- Try to backfill party_id on operational tables using name/phone/code matches.
UPDATE sales_invoices si
JOIN customers c ON c.id = si.customer_id
JOIN parties p ON ((c.code IS NOT NULL AND c.code <> '' AND p.code = c.code) OR (p.name = c.name AND COALESCE(p.phone,'') = COALESCE(c.phone,'')))
SET si.party_id = p.id
WHERE si.party_id IS NULL;

UPDATE customer_receipts cr
JOIN customers c ON c.id = cr.customer_id
JOIN parties p ON ((c.code IS NOT NULL AND c.code <> '' AND p.code = c.code) OR (p.name = c.name AND COALESCE(p.phone,'') = COALESCE(c.phone,'')))
SET cr.party_id = p.id
WHERE cr.party_id IS NULL;

UPDATE purchase_invoices pi
JOIN suppliers s ON s.id = pi.supplier_id
JOIN parties p ON ((s.code IS NOT NULL AND s.code <> '' AND p.code = s.code) OR (p.name = s.name AND COALESCE(p.phone,'') = COALESCE(s.phone,'')))
SET pi.party_id = p.id
WHERE pi.party_id IS NULL;

UPDATE purchase_returns pr
JOIN suppliers s ON s.id = pr.supplier_id
JOIN parties p ON ((s.code IS NOT NULL AND s.code <> '' AND p.code = s.code) OR (p.name = s.name AND COALESCE(p.phone,'') = COALESCE(s.phone,'')))
SET pr.party_id = p.id
WHERE pr.party_id IS NULL;

UPDATE cash_payments cp
JOIN suppliers s ON s.id = cp.supplier_id
JOIN parties p ON ((s.code IS NOT NULL AND s.code <> '' AND p.code = s.code) OR (p.name = s.name AND COALESCE(p.phone,'') = COALESCE(s.phone,'')))
SET cp.party_id = p.id
WHERE cp.party_id IS NULL;

SET FOREIGN_KEY_CHECKS=1;