ALTER TABLE plans
    ADD COLUMN IF NOT EXISTS price_yearly DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER price_monthly;

ALTER TABLE subscriptions
    ADD COLUMN IF NOT EXISTS trial_ends_at DATETIME NULL AFTER starts_at,
    ADD COLUMN IF NOT EXISTS current_period_start DATETIME NULL AFTER trial_ends_at,
    ADD COLUMN IF NOT EXISTS current_period_end DATETIME NULL AFTER current_period_start,
    ADD COLUMN IF NOT EXISTS next_billing_at DATETIME NULL AFTER current_period_end,
    ADD COLUMN IF NOT EXISTS last_payment_at DATETIME NULL AFTER next_billing_at,
    ADD COLUMN IF NOT EXISTS failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER last_payment_at;

CREATE TABLE IF NOT EXISTS invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NOT NULL,
    invoice_number VARCHAR(60) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('open', 'paid', 'void', 'failed') NOT NULL DEFAULT 'open',
    due_at DATETIME NOT NULL,
    paid_at DATETIME NULL,
    period_start DATETIME NULL,
    period_end DATETIME NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_invoices_number (invoice_number),
    INDEX idx_invoices_tenant_status_due (tenant_id, status, due_at),
    CONSTRAINT fk_invoices_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_invoices_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NULL,
    invoice_id BIGINT UNSIGNED NULL,
    provider ENUM('manual', 'stripe', 'paypal', 'mercadopago') NOT NULL DEFAULT 'manual',
    provider_payment_id VARCHAR(190) NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    status ENUM('succeeded', 'failed', 'pending') NOT NULL DEFAULT 'pending',
    failure_reason VARCHAR(255) NULL,
    processed_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_payment_transactions_tenant_status (tenant_id, status, created_at),
    CONSTRAINT fk_payment_transactions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_payment_transactions_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL,
    CONSTRAINT fk_payment_transactions_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    run_type ENUM('cycle', 'retry', 'manual') NOT NULL DEFAULT 'cycle',
    invoices_created INT UNSIGNED NOT NULL DEFAULT 0,
    invoices_paid INT UNSIGNED NOT NULL DEFAULT 0,
    invoices_failed INT UNSIGNED NOT NULL DEFAULT 0,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    notes VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_billing_runs_tenant_created (tenant_id, created_at),
    CONSTRAINT fk_billing_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE plans
SET price_yearly = CASE code
    WHEN 'starter' THEN 490.00
    WHEN 'pro' THEN 990.00
    WHEN 'enterprise' THEN 2990.00
    ELSE price_yearly
END
WHERE code IN ('starter', 'pro', 'enterprise');

INSERT INTO subscriptions (
    tenant_id,
    plan_id,
    provider,
    billing_cycle,
    status,
    starts_at,
    trial_ends_at,
    current_period_start,
    current_period_end,
    next_billing_at
)
SELECT
    t.id,
    t.plan_id,
    'manual',
    'monthly',
    CASE
        WHEN t.status = 'trialing' THEN 'trialing'
        WHEN t.status = 'past_due' THEN 'past_due'
        WHEN t.status = 'cancelled' THEN 'cancelled'
        ELSE 'active'
    END,
    NOW(),
    CASE WHEN t.status = 'trialing' THEN COALESCE(t.trial_ends_at, DATE_ADD(NOW(), INTERVAL 14 DAY)) ELSE NULL END,
    NOW(),
    DATE_ADD(NOW(), INTERVAL 1 MONTH),
    DATE_ADD(NOW(), INTERVAL 1 MONTH)
FROM tenants t
LEFT JOIN subscriptions s ON s.tenant_id = t.id
WHERE s.id IS NULL AND t.plan_id IS NOT NULL;
