-- ============================================================
--  Billing migration v2 — simple per-user pricing with Stripe
--  $29 / user / month, duration-based (3 / 6 / 12 months)
--  Run: DROP the old tables first if upgrading from v1
-- ============================================================

DROP TABLE IF EXISTS billing_invoices;
DROP TABLE IF EXISTS subscriptions;
DROP TABLE IF EXISTS billing_plans;

CREATE TABLE subscriptions (
    id                   INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT UNSIGNED   NOT NULL,
    status               ENUM('trialing','active','expired') NOT NULL DEFAULT 'trialing',
    trial_ends_at        DATE           NULL,
    expires_at           DATE           NULL  COMMENT 'When current paid access ends; NULL = trial only',
    user_count           INT            NOT NULL DEFAULT 1,
    stripe_customer_id   VARCHAR(100)   NULL,
    created_at           TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant  (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE billing_invoices (
    id                        INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    tenant_id                 INT UNSIGNED   NOT NULL,
    invoice_number            VARCHAR(50)    NOT NULL,
    user_count                INT            NOT NULL,
    duration_months           INT            NOT NULL,
    amount_cents              INT            NOT NULL  COMMENT 'Total in USD cents',
    status                    ENUM('pending','paid','void') NOT NULL DEFAULT 'pending',
    stripe_payment_intent_id  VARCHAR(100)   NULL,
    paid_at                   TIMESTAMP      NULL,
    created_at                TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_invoice     (tenant_id, invoice_number),
    INDEX idx_tenant          (tenant_id),
    INDEX idx_stripe_pi       (stripe_payment_intent_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
