-- Customer master and invoice payment ledgers.
-- Built against Latest_dump.sql sold_items plus credit sales tables.

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    customer_code VARCHAR(40) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(30) DEFAULT NULL,
    customer_email VARCHAR(120) DEFAULT NULL,
    customer_address VARCHAR(255) DEFAULT NULL,
    buyer_tin VARCHAR(30) DEFAULT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id),
    UNIQUE KEY uq_customer_code (customer_code),
    KEY idx_customers_name (customer_name),
    KEY idx_customers_phone (customer_phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS customer_ledger (
    ledger_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    branch_id INT DEFAULT NULL,
    invoice_type ENUM('sale','credit_sale') NOT NULL DEFAULT 'sale',
    invoice_number VARCHAR(120) NOT NULL,
    transaction_id VARCHAR(120) DEFAULT NULL,
    credit_id INT DEFAULT NULL,
    sale_id INT DEFAULT NULL,
    entry_type ENUM('invoice','payment','adjustment') NOT NULL,
    debit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    balance_after DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    payment_method VARCHAR(40) DEFAULT NULL,
    notes VARCHAR(255) DEFAULT NULL,
    entry_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    recorded_by INT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ledger_id),
    KEY idx_customer_ledger_customer (customer_id, entry_date),
    KEY idx_customer_ledger_invoice (invoice_type, invoice_number),
    KEY idx_customer_ledger_credit (credit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE sold_items ADD COLUMN IF NOT EXISTS customer_id INT DEFAULT NULL;
ALTER TABLE credit_accounts ADD COLUMN IF NOT EXISTS customer_id INT DEFAULT NULL;
-- Seed the customer master from existing sales and credit accounts so lookup works immediately.
INSERT INTO customers (customer_code, customer_name, customer_phone, created_by)
SELECT CONCAT('CUST-LEG-', MIN(si.sale_id)) AS customer_code,
       TRIM(si.customer_name) AS customer_name,
       NULLIF(TRIM(si.customer_phone), '') AS customer_phone,
       MIN(si.sold_by) AS created_by
FROM sold_items si
WHERE TRIM(COALESCE(si.customer_name, '')) <> ''
  AND NOT EXISTS (
      SELECT 1 FROM customers c
      WHERE c.customer_name = TRIM(si.customer_name)
        AND COALESCE(c.customer_phone, '') = COALESCE(NULLIF(TRIM(si.customer_phone), ''), '')
  )
GROUP BY TRIM(si.customer_name), NULLIF(TRIM(si.customer_phone), '');

INSERT INTO customers (customer_code, customer_name, customer_phone, created_by)
SELECT CONCAT('CUST-CR-', MIN(ca.credit_id)) AS customer_code,
       TRIM(ca.customer_name) AS customer_name,
       NULLIF(TRIM(ca.customer_phone), '') AS customer_phone,
       MIN(ca.created_by) AS created_by
FROM credit_accounts ca
WHERE TRIM(COALESCE(ca.customer_name, '')) <> ''
  AND NOT EXISTS (
      SELECT 1 FROM customers c
      WHERE c.customer_name = TRIM(ca.customer_name)
        AND COALESCE(c.customer_phone, '') = COALESCE(NULLIF(TRIM(ca.customer_phone), ''), '')
  )
GROUP BY TRIM(ca.customer_name), NULLIF(TRIM(ca.customer_phone), '');

UPDATE sold_items si
JOIN customers c
  ON c.customer_name = TRIM(si.customer_name)
 AND COALESCE(c.customer_phone, '') = COALESCE(NULLIF(TRIM(si.customer_phone), ''), '')
SET si.customer_id = c.customer_id
WHERE si.customer_id IS NULL
  AND TRIM(COALESCE(si.customer_name, '')) <> '';

UPDATE credit_accounts ca
JOIN customers c
  ON c.customer_name = TRIM(ca.customer_name)
 AND COALESCE(c.customer_phone, '') = COALESCE(NULLIF(TRIM(ca.customer_phone), ''), '')
SET ca.customer_id = c.customer_id
WHERE ca.customer_id IS NULL
  AND TRIM(COALESCE(ca.customer_name, '')) <> '';
