-- Credit sales, debtors, and admin-only creditors.
-- Built against Latest_dump.sql sold_items/users/branches schema.

CREATE TABLE IF NOT EXISTS credit_accounts (
    credit_id INT NOT NULL AUTO_INCREMENT,
    credit_number VARCHAR(40) NOT NULL,
    branch_id INT NOT NULL,
    transaction_id VARCHAR(80) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(30) DEFAULT NULL,
    total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    status ENUM('pending_controller_approval','approved','rejected','completed') NOT NULL DEFAULT 'pending_controller_approval',
    created_by INT NOT NULL,
    approved_by INT DEFAULT NULL,
    approved_at DATETIME DEFAULT NULL,
    approval_notes TEXT DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (credit_id),
    UNIQUE KEY uq_credit_number (credit_number),
    KEY idx_credit_branch_status (branch_id, status),
    KEY idx_credit_transaction (transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS credit_sale_items (
    id INT NOT NULL AUTO_INCREMENT,
    credit_id INT NOT NULL,
    sale_id INT NOT NULL,
    item_id INT DEFAULT NULL,
    unique_code VARCHAR(255) NOT NULL,
    item_name VARCHAR(255) DEFAULT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id),
    KEY idx_credit_items_credit (credit_id),
    KEY idx_credit_items_sale (sale_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS credit_payments (
    payment_id INT NOT NULL AUTO_INCREMENT,
    credit_id INT NOT NULL,
    branch_id INT NOT NULL,
    payment_date DATETIME NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    payment_method VARCHAR(40) NOT NULL DEFAULT 'cash',
    notes VARCHAR(255) DEFAULT NULL,
    recorded_by INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (payment_id),
    KEY idx_credit_payments_credit (credit_id),
    KEY idx_credit_payments_branch_date (branch_id, payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS creditors (
    creditor_id INT NOT NULL AUTO_INCREMENT,
    creditor_name VARCHAR(255) NOT NULL,
    creditor_phone VARCHAR(30) DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    amount_owed DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    status ENUM('open','settled') NOT NULL DEFAULT 'open',
    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 (creditor_id),
    KEY idx_creditors_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;