ALTER TABLE product_plans
    ADD COLUMN monthly_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00 AFTER description,
    ADD COLUMN setup_fee DECIMAL(10, 2) NOT NULL DEFAULT 0.00 AFTER monthly_price,
    ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'BRL' AFTER setup_fee,
    ADD COLUMN billing_cycle ENUM('monthly', 'quarterly', 'semiannual', 'annual') NOT NULL DEFAULT 'monthly' AFTER currency,
    ADD COLUMN grace_days TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER billing_cycle,
    ADD COLUMN external_product_id VARCHAR(80) NULL AFTER grace_days;

CREATE TABLE company_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    plan_code VARCHAR(50) NOT NULL,
    status ENUM('trialing', 'active', 'past_due', 'suspended', 'canceled') NOT NULL DEFAULT 'trialing',
    amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'BRL',
    billing_cycle ENUM('monthly', 'quarterly', 'semiannual', 'annual') NOT NULL DEFAULT 'monthly',
    start_date DATE NOT NULL,
    current_period_start DATE NOT NULL,
    current_period_end DATE NOT NULL,
    due_date DATE NOT NULL,
    payment_due_day TINYINT UNSIGNED NOT NULL,
    grace_days TINYINT UNSIGNED NOT NULL DEFAULT 0,
    last_paid_at DATETIME NULL,
    last_status ENUM('pending', 'paid', 'overdue', 'failed', 'refunded') NULL,
    next_charge_at DATE NULL,
    gateway VARCHAR(50) NULL,
    external_subscription_id VARCHAR(120) NULL,
    external_customer_id VARCHAR(120) NULL,
    billing_name VARCHAR(160) NULL,
    billing_email VARCHAR(160) NULL,
    billing_document VARCHAR(32) NULL,
    billing_phone VARCHAR(32) NULL,
    notes VARCHAR(255) NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_subscription_company (company_id),
    INDEX idx_company_subscriptions_status (status, due_date),
    CONSTRAINT fk_company_subscriptions_company FOREIGN KEY (company_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_company_subscriptions_plan FOREIGN KEY (plan_code) REFERENCES product_plans (code) ON DELETE CASCADE,
    CONSTRAINT fk_company_subscriptions_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_company_subscriptions_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subscription_id BIGINT UNSIGNED NOT NULL,
    company_id INT UNSIGNED NOT NULL,
    plan_code VARCHAR(50) NULL,
    amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'BRL',
    due_date DATE NOT NULL,
    paid_at DATETIME NULL,
    status ENUM('pending', 'paid', 'overdue', 'failed', 'refunded', 'canceled') NOT NULL DEFAULT 'pending',
    payment_method ENUM('pix', 'boleto', 'credit_card', 'transfer', 'manual', 'gateway') NOT NULL DEFAULT 'manual',
    gateway VARCHAR(50) NULL,
    transaction_reference VARCHAR(120) NULL,
    external_invoice_id VARCHAR(120) NULL,
    payment_url VARCHAR(255) NULL,
    receipt_url VARCHAR(255) NULL,
    receipt_path VARCHAR(255) NULL,
    notes VARCHAR(255) NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscription_payments_company (company_id, due_date),
    INDEX idx_subscription_payments_status (status, due_date),
    CONSTRAINT fk_subscription_payments_subscription FOREIGN KEY (subscription_id) REFERENCES company_subscriptions (id) ON DELETE CASCADE,
    CONSTRAINT fk_subscription_payments_company FOREIGN KEY (company_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_subscription_payments_plan FOREIGN KEY (plan_code) REFERENCES product_plans (code) ON DELETE SET NULL,
    CONSTRAINT fk_subscription_payments_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
