CREATE TABLE billing_schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    job_id INT NULL,
    quote_id INT NULL,
    contract_id INT NULL,
    client_id INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    schedule_type VARCHAR(40) NOT NULL,
    contract_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    currency VARCHAR(3) NOT NULL DEFAULT 'GBP',
    status VARCHAR(50) NOT NULL DEFAULT 'draft',
    created_by INT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE TABLE billing_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    billing_schedule_id INT NOT NULL,
    sequence_number INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    trigger_type VARCHAR(40) NOT NULL,
    trigger_value VARCHAR(190) NULL,
    billing_method VARCHAR(40) NOT NULL,
    percentage DECIMAL(7,4) NULL,
    amount DECIMAL(12,2) NULL,
    due_offset_days INT NULL,
    invoice_id INT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    ready_at DATETIME NULL,
    invoiced_at DATETIME NULL,
    paid_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE TABLE payment_schedule_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE TABLE payment_schedule_template_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_id INT NOT NULL,
    sequence_number INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    trigger_type VARCHAR(40) NOT NULL,
    billing_method VARCHAR(40) NOT NULL,
    percentage DECIMAL(7,4) NULL,
    amount DECIMAL(12,2) NULL,
    due_offset_days INT NULL
);

ALTER TABLE invoices ADD COLUMN billing_point_id INT NULL AFTER quote_id;

CREATE INDEX idx_billing_schedules_tenant_quote ON billing_schedules (tenant_id, quote_id);
CREATE INDEX idx_billing_schedules_tenant_job ON billing_schedules (tenant_id, job_id);
CREATE INDEX idx_billing_points_schedule ON billing_points (tenant_id, billing_schedule_id);
CREATE INDEX idx_billing_points_invoice ON billing_points (tenant_id, invoice_id);