-- Reset DB to Parties-only structure (safe for experimental data)
-- Run this AFTER importing your DB dump.

SET FOREIGN_KEY_CHECKS=0;

-- Helper: drop FK if exists
-- Usage: set @tbl='table'; set @fk='fk_name'; source below block.

-- Drop known legacy FKs that reference customers/suppliers
SET @db := DATABASE();

-- items.default_supplier_id -> suppliers
SET @fk := (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='items' AND REFERENCED_TABLE_NAME='suppliers' LIMIT 1);
SET @sql := IF(@fk IS NULL, 'SELECT 1', CONCAT('ALTER TABLE `items` DROP FOREIGN KEY `', @fk, '`'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- purchase_returns.supplier_id -> suppliers
SET @fk := (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='purchase_returns' AND REFERENCED_TABLE_NAME='suppliers' LIMIT 1);
SET @sql := IF(@fk IS NULL, 'SELECT 1', CONCAT('ALTER TABLE `purchase_returns` DROP FOREIGN KEY `', @fk, '`'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- supplier_ledger.supplier_id -> suppliers
SET @fk := (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='supplier_ledger' AND REFERENCED_TABLE_NAME='suppliers' LIMIT 1);
SET @sql := IF(@fk IS NULL, 'SELECT 1', CONCAT('ALTER TABLE `supplier_ledger` DROP FOREIGN KEY `', @fk, '`'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- cash_payments.supplier_id -> suppliers
SET @fk := (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='cash_payments' AND REFERENCED_TABLE_NAME='suppliers' LIMIT 1);
SET @sql := IF(@fk IS NULL, 'SELECT 1', CONCAT('ALTER TABLE `cash_payments` DROP FOREIGN KEY `', @fk, '`'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- customer_receipts.customer_id -> customers
SET @fk := (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='customer_receipts' AND REFERENCED_TABLE_NAME='customers' LIMIT 1);
SET @sql := IF(@fk IS NULL, 'SELECT 1', CONCAT('ALTER TABLE `customer_receipts` DROP FOREIGN KEY `', @fk, '`'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- sales_invoices.customer_id -> customers
SET @fk := (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND REFERENCED_TABLE_NAME='customers' LIMIT 1);
SET @sql := IF(@fk IS NULL, 'SELECT 1', CONCAT('ALTER TABLE `sales_invoices` DROP FOREIGN KEY `', @fk, '`'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- purchase_invoices.supplier_id -> suppliers
SET @fk := (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS
            WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='purchase_invoices' AND REFERENCED_TABLE_NAME='suppliers' LIMIT 1);
SET @sql := IF(@fk IS NULL, 'SELECT 1', CONCAT('ALTER TABLE `purchase_invoices` DROP FOREIGN KEY `', @fk, '`'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Ensure Parties tables exist (created by app helpers too)
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`)
) 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;

-- Add party_id columns to legacy tables (nullable)
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`;

-- Make legacy IDs nullable
ALTER TABLE `sales_invoices` MODIFY `customer_id` bigint(20) UNSIGNED NULL;
ALTER TABLE `purchase_invoices` MODIFY `supplier_id` bigint(20) UNSIGNED NULL;
ALTER TABLE `purchase_returns` MODIFY `supplier_id` bigint(20) UNSIGNED NULL;
ALTER TABLE `customer_receipts` MODIFY `customer_id` bigint(20) UNSIGNED NULL;
ALTER TABLE `cash_payments` MODIFY `supplier_id` bigint(20) UNSIGNED NULL;
ALTER TABLE `items` MODIFY `default_supplier_id` bigint(20) UNSIGNED NULL;

-- OPTIONAL: remove experimental transactional data
TRUNCATE TABLE `party_ledger`;
TRUNCATE TABLE `parties`;
TRUNCATE TABLE `customer_receipts`;
TRUNCATE TABLE `cash_payments`;
TRUNCATE TABLE `sales_invoices`;
TRUNCATE TABLE `sales_items`;
TRUNCATE TABLE `purchase_invoices`;
TRUNCATE TABLE `purchase_invoice_lines`;
TRUNCATE TABLE `purchase_returns`;
TRUNCATE TABLE `purchase_return_lines`;

-- Legacy tables: keep empty to avoid breakage if any old page still exists
TRUNCATE TABLE `customers`;
TRUNCATE TABLE `suppliers`;
TRUNCATE TABLE `customer_ledger`;
TRUNCATE TABLE `supplier_ledger`;

SET FOREIGN_KEY_CHECKS=1;