CREATE TABLE IF NOT EXISTS monthly_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    invoice_number VARCHAR(40) NOT NULL COMMENT 'e.g. SN-INV-2026-000124',
    customer_id BIGINT UNSIGNED NOT NULL,

    -- Always stored as the first of the month for a clean unique constraint.
    billing_month DATE NOT NULL,

    monthly_fee DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    previous_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,

    -- total_payable = monthly_fee - discount_amount + previous_balance
    -- Calculated at generation time and stored (not a generated column)
    -- so historical invoices remain stable even if fee logic changes later.
    total_payable DECIMAL(12,2) NOT NULL,
    amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0.00,

    due_date DATE NOT NULL,
    status ENUM('upcoming', 'pending', 'partially_paid', 'paid', 'overdue', 'cancelled')
        NOT NULL DEFAULT 'upcoming',

    -- Unguessable public identifier — never expose the row ID publicly.
    public_token CHAR(64) NOT NULL,

    cancelled_at DATETIME NULL,
    cancelled_by BIGINT UNSIGNED NULL,
    cancellation_reason VARCHAR(255) NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_invoices_number (invoice_number),
    UNIQUE KEY uq_invoices_public_token (public_token),
    UNIQUE KEY uq_invoices_customer_month (customer_id, billing_month),
    KEY idx_invoices_status (status),
    KEY idx_invoices_due_date (due_date),

    CONSTRAINT fk_invoices_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_invoices_cancelled_by
        FOREIGN KEY (cancelled_by) REFERENCES users (id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;