SET @schema_name := DATABASE();

SET @sql := (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = @schema_name
              AND table_name = 'product_plans'
              AND column_name = 'monthly_price'
        ),
        'SELECT 1',
        'ALTER TABLE product_plans ADD COLUMN monthly_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00 AFTER description'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = @schema_name
              AND table_name = 'product_plans'
              AND column_name = 'setup_fee'
        ),
        'SELECT 1',
        'ALTER TABLE product_plans ADD COLUMN setup_fee DECIMAL(10, 2) NOT NULL DEFAULT 0.00 AFTER monthly_price'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = @schema_name
              AND table_name = 'product_plans'
              AND column_name = 'currency'
        ),
        'SELECT 1',
        'ALTER TABLE product_plans ADD COLUMN currency CHAR(3) NOT NULL DEFAULT ''BRL'' AFTER setup_fee'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = @schema_name
              AND table_name = 'product_plans'
              AND column_name = 'billing_cycle'
        ),
        'SELECT 1',
        'ALTER TABLE product_plans ADD COLUMN billing_cycle ENUM(''monthly'', ''quarterly'', ''semiannual'', ''annual'') NOT NULL DEFAULT ''monthly'' AFTER currency'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = @schema_name
              AND table_name = 'product_plans'
              AND column_name = 'grace_days'
        ),
        'SELECT 1',
        'ALTER TABLE product_plans ADD COLUMN grace_days TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER billing_cycle'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = @schema_name
              AND table_name = 'product_plans'
              AND column_name = 'external_product_id'
        ),
        'SELECT 1',
        'ALTER TABLE product_plans ADD COLUMN external_product_id VARCHAR(80) NULL AFTER grace_days'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS 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,
    first_due_date DATE 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 IF NOT EXISTS 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;