-- Migration 0018: create_saas_platform
-- Hand-written (not auto-generated) — see database/schema.sql SECTION 18
-- for the documented copy of this same change. Kept as a standalone
-- migration file (rather than folded into 0002_create_rbac_and_auth.sql
-- via build_migrations.php) so it applies cleanly on top of databases
-- that already ran migrations 0001-0017, without needing --fresh.
--
-- Turns `business_lines` from "the two brands under one company" into
-- "one row per paying tenant company." Every other table already
-- shards its data by business_line_id, so no other table needs to
-- change — a tenant's leads/customers/appointments/etc. are already
-- fully isolated by that foreign key. What's new here is: (1) billing
-- state on business_lines itself, (2) subscription codes an admin can
-- hand out instead of a card, (3) a platform_admins table for the
-- software owner, who sits above every tenant and is deliberately NOT
-- a row in `users` (that table is tenant-scoped via user_business_lines).

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 18 — SAAS PLATFORM (multi-tenant billing)
-- =====================================================================

-- `slug` (already unique) now doubles as the subdomain: a tenant with
-- slug 'acme' is reached at acme.<APP_DOMAIN>. See App\Core\Tenant.
ALTER TABLE business_lines
    ADD COLUMN plan                   VARCHAR(40)  NULL AFTER brand_color,
    ADD COLUMN subscription_status    ENUM('trialing','active','past_due','expired','canceled')
                                                    NOT NULL DEFAULT 'trialing' AFTER plan,
    ADD COLUMN billing_cycle          ENUM('yearly') NOT NULL DEFAULT 'yearly' AFTER subscription_status,
    ADD COLUMN billing_email          VARCHAR(191) NULL AFTER billing_cycle,
    ADD COLUMN trial_ends_at          DATETIME     NULL AFTER billing_email,
    ADD COLUMN subscription_expires_at DATETIME    NULL COMMENT 'Access is blocked once now() passes this, unless subscription_status = active with a live Stripe subscription' AFTER trial_ends_at,
    ADD COLUMN stripe_customer_id     VARCHAR(191) NULL AFTER subscription_expires_at,
    ADD COLUMN stripe_subscription_id VARCHAR(191) NULL AFTER stripe_customer_id,
    ADD COLUMN activation_source      ENUM('signup_stripe','signup_code','manual_admin') NULL AFTER stripe_subscription_id,
    ADD COLUMN onboarded_by_admin_id  BIGINT UNSIGNED NULL AFTER activation_source,
    ADD KEY idx_business_lines_subscription_status (subscription_status),
    ADD KEY idx_business_lines_stripe_customer (stripe_customer_id);

-- The platform owner (you) — deliberately separate from `users`/`roles`,
-- since those are tenant-scoped (a user only ever belongs to the tenant(s)
-- in user_business_lines). A platform admin belongs to no tenant and can
-- see/manage all of them.
CREATE TABLE IF NOT EXISTS platform_admins (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(191)    NOT NULL,
    email          VARCHAR(191)    NOT NULL,
    password_hash  VARCHAR(255)    NOT NULL,
    is_active      TINYINT(1)      NOT NULL DEFAULT 1,
    last_login_at  DATETIME        NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_platform_admins_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE business_lines
    ADD CONSTRAINT fk_business_lines_onboarded_by
        FOREIGN KEY (onboarded_by_admin_id) REFERENCES platform_admins(id) ON DELETE SET NULL ON UPDATE CASCADE;

-- A code an admin generates, either pre-assigned to one tenant (renewal)
-- or blank/unassigned (redeemed by whichever new company signs up with
-- it, activating their account instead of paying by card). Only the
-- SHA-256 hash + a short display prefix are stored, same pattern as
-- api_keys — the plaintext code is shown once, at generation time.
CREATE TABLE IF NOT EXISTS subscription_codes (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code_prefix        VARCHAR(12)     NOT NULL COMMENT 'Non-secret, shown in the admin UI list, e.g. SUB-7F2A',
    code_hash          CHAR(64)        NOT NULL COMMENT 'SHA-256 of the full plaintext code',
    intent             ENUM('activate_new','renew_existing','either') NOT NULL DEFAULT 'either',
    business_line_id   BIGINT UNSIGNED NULL COMMENT 'Set when pre-assigned to one tenant (renewal); NULL = redeemable by any new signup',
    duration_months    SMALLINT UNSIGNED NOT NULL DEFAULT 12,
    max_uses           SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    use_count          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    expires_at         DATETIME        NULL COMMENT 'Code itself expires if unredeemed by this date; NULL = no expiry',
    revoked_at         DATETIME        NULL,
    created_by_admin_id BIGINT UNSIGNED NOT NULL,
    created_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_subscription_codes_hash (code_hash),
    KEY idx_subscription_codes_business_line (business_line_id),
    CONSTRAINT fk_subscription_codes_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_subscription_codes_admin FOREIGN KEY (created_by_admin_id) REFERENCES platform_admins(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every redemption of a code — kept even though subscription_codes
-- already has use_count/max_uses, because "which tenant used which
-- code, and when" is exactly what a billing support conversation needs.
CREATE TABLE IF NOT EXISTS subscription_code_redemptions (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subscription_code_id BIGINT UNSIGNED NOT NULL,
    business_line_id    BIGINT UNSIGNED NOT NULL,
    redeemed_at          DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_scr_code (subscription_code_id),
    KEY idx_scr_business_line (business_line_id),
    CONSTRAINT fk_scr_code FOREIGN KEY (subscription_code_id) REFERENCES subscription_codes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_scr_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Raw audit trail of billing events (Stripe webhook deliveries and
-- manual admin actions both land here) — separate from webhook_events
-- (which is a generic inbound-payload log) because this one is
-- specifically "what happened to this tenant's subscription and why,"
-- readable without decoding a JSON payload.
CREATE TABLE IF NOT EXISTS subscription_events (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id BIGINT UNSIGNED NOT NULL,
    event_type       VARCHAR(60)     NOT NULL COMMENT 'trial_started, checkout_completed, renewed, payment_failed, code_redeemed, manually_activated, canceled...',
    detail           VARCHAR(500)    NULL,
    stripe_event_id  VARCHAR(191)    NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_subscription_events_business_line (business_line_id, created_at),
    CONSTRAINT fk_subscription_events_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
