SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE user_consents (
    id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    document_key VARCHAR(80) NOT NULL,
    document_version VARCHAR(40) NOT NULL,
    source VARCHAR(40) NOT NULL,
    evidence_json JSON NULL,
    accepted_at DATETIME(6) NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY user_consents_document_unique (user_id, document_key, document_version),
    KEY user_consents_user_date_index (user_id, accepted_at),
    CONSTRAINT user_consents_user_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE auth_otp_challenges (
    id CHAR(36) NOT NULL,
    email VARCHAR(254) NOT NULL,
    purpose VARCHAR(24) NOT NULL,
    code_hash CHAR(64) NOT NULL,
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 5,
    ip_hash CHAR(64) NOT NULL,
    metadata_json JSON NULL,
    expires_at DATETIME(6) NOT NULL,
    consumed_at DATETIME(6) NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY auth_otp_email_created_index (email, created_at),
    KEY auth_otp_ip_created_index (ip_hash, created_at),
    KEY auth_otp_expiry_index (expires_at),
    CONSTRAINT auth_otp_purpose_check CHECK (purpose IN ('LOGIN', 'REGISTER'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE auth_refresh_sessions (
    id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    workspace_id CHAR(36) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    csrf_hash CHAR(64) NOT NULL,
    user_agent_hash CHAR(64) NOT NULL,
    ip_hash CHAR(64) NOT NULL,
    rotation_count INT UNSIGNED NOT NULL DEFAULT 0,
    expires_at DATETIME(6) NOT NULL,
    last_used_at DATETIME(6) NOT NULL,
    revoked_at DATETIME(6) NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY auth_refresh_token_unique (token_hash),
    KEY auth_refresh_user_expiry_index (user_id, expires_at),
    KEY auth_refresh_workspace_index (workspace_id, created_at),
    CONSTRAINT auth_refresh_user_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT auth_refresh_workspace_fk FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE plans (
    id CHAR(36) NOT NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE',
    credits_per_cycle INT UNSIGNED NOT NULL,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    limits_json JSON NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY plans_code_unique (code),
    KEY plans_status_index (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE plan_prices (
    id CHAR(36) NOT NULL,
    plan_id CHAR(36) NOT NULL,
    currency CHAR(3) NOT NULL,
    amount DECIMAL(14, 2) NOT NULL,
    billing_period VARCHAR(24) NOT NULL DEFAULT 'MONTH',
    tax_included TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY plan_prices_plan_currency_period_unique (plan_id, currency, billing_period),
    CONSTRAINT plan_prices_plan_fk FOREIGN KEY (plan_id) REFERENCES plans (id) ON DELETE CASCADE,
    CONSTRAINT plan_prices_currency_check CHECK (currency IN ('COP', 'USD'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE subscriptions (
    id CHAR(36) NOT NULL,
    workspace_id CHAR(36) NOT NULL,
    plan_id CHAR(36) NOT NULL,
    provider VARCHAR(40) NOT NULL,
    provider_subscription_id VARCHAR(180) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    currency CHAR(3) NOT NULL,
    amount DECIMAL(14, 2) NOT NULL,
    current_period_starts_at DATETIME(6) NULL,
    current_period_ends_at DATETIME(6) NULL,
    cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY subscriptions_workspace_status_index (workspace_id, status),
    UNIQUE KEY subscriptions_provider_reference_unique (provider, provider_subscription_id),
    CONSTRAINT subscriptions_workspace_fk FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
    CONSTRAINT subscriptions_plan_fk FOREIGN KEY (plan_id) REFERENCES plans (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE checkout_sessions (
    id CHAR(36) NOT NULL,
    workspace_id CHAR(36) NOT NULL,
    plan_id CHAR(36) NOT NULL,
    provider VARCHAR(40) NOT NULL,
    provider_session_id VARCHAR(180) NULL,
    idempotency_key VARCHAR(180) NOT NULL,
    currency CHAR(3) NOT NULL,
    subtotal DECIMAL(14, 2) NOT NULL,
    tax DECIMAL(14, 2) NOT NULL DEFAULT 0,
    total DECIMAL(14, 2) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'CREATED',
    checkout_url VARCHAR(1024) NULL,
    expires_at DATETIME(6) NULL,
    completed_at DATETIME(6) NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY checkout_workspace_idempotency_unique (workspace_id, idempotency_key),
    UNIQUE KEY checkout_provider_reference_unique (provider, provider_session_id),
    KEY checkout_workspace_status_index (workspace_id, status, created_at),
    CONSTRAINT checkout_workspace_fk FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
    CONSTRAINT checkout_plan_fk FOREIGN KEY (plan_id) REFERENCES plans (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE billing_webhook_events (
    id CHAR(36) NOT NULL,
    provider VARCHAR(40) NOT NULL,
    provider_event_id VARCHAR(180) NOT NULL,
    event_type VARCHAR(120) NOT NULL,
    signature_valid TINYINT(1) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    payload_json JSON NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'RECEIVED',
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    processed_at DATETIME(6) NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY billing_webhook_provider_event_unique (provider, provider_event_id),
    KEY billing_webhook_status_index (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE campaign_drafts (
    id CHAR(36) NOT NULL,
    workspace_id CHAR(36) NOT NULL,
    payload_json JSON NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'DRAFT',
    version INT UNSIGNED NOT NULL DEFAULT 1,
    created_by CHAR(36) NOT NULL,
    updated_by CHAR(36) NOT NULL,
    approved_by CHAR(36) NULL,
    approved_at DATETIME(6) NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY campaign_drafts_workspace_unique (workspace_id),
    KEY campaign_drafts_workspace_status_index (workspace_id, status, updated_at),
    CONSTRAINT campaign_drafts_workspace_fk FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE,
    CONSTRAINT campaign_drafts_created_by_fk FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE RESTRICT,
    CONSTRAINT campaign_drafts_updated_by_fk FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE RESTRICT,
    CONSTRAINT campaign_drafts_approved_by_fk FOREIGN KEY (approved_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT campaign_drafts_status_check CHECK (status IN ('DRAFT', 'APPROVED'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO plans (id, code, name, status, credits_per_cycle, sort_order, limits_json)
VALUES
    (
        '10000000-0000-4000-8000-000000000001',
        'IMPULSO',
        'Impulso',
        'ACTIVE',
        250,
        10,
        JSON_OBJECT(
            'businesses', 1,
            'active_campaigns', 2,
            'members', 1,
            'featured', FALSE,
            'public_features', JSON_ARRAY('1 negocio', '2 campañas activas', 'Uso individual', 'AlexIA y Kit de Campaña')
        )
    ),
    (
        '10000000-0000-4000-8000-000000000002',
        'CRECIMIENTO',
        'Crecimiento',
        'ACTIVE',
        800,
        20,
        JSON_OBJECT(
            'businesses', 1,
            'active_campaigns', 8,
            'members', 3,
            'featured', TRUE,
            'public_features', JSON_ARRAY('1 negocio', '8 campañas activas', 'Hasta 3 miembros', 'Mejor costo por crédito')
        )
    ),
    (
        '10000000-0000-4000-8000-000000000003',
        'ESCALA',
        'Escala',
        'ACTIVE',
        2200,
        30,
        JSON_OBJECT(
            'businesses', 3,
            'active_campaigns', 24,
            'members', 10,
            'featured', FALSE,
            'public_features', JSON_ARRAY('Hasta 3 negocios', '10 miembros', 'Analítica avanzada', 'Atención prioritaria')
        )
    );

INSERT INTO plan_prices (id, plan_id, currency, amount, billing_period, tax_included, status)
VALUES
    ('20000000-0000-4000-8000-000000000001', '10000000-0000-4000-8000-000000000001', 'COP', 119000, 'MONTH', 0, 'ACTIVE'),
    ('20000000-0000-4000-8000-000000000002', '10000000-0000-4000-8000-000000000001', 'USD', 29, 'MONTH', 0, 'ACTIVE'),
    ('20000000-0000-4000-8000-000000000003', '10000000-0000-4000-8000-000000000002', 'COP', 279000, 'MONTH', 0, 'ACTIVE'),
    ('20000000-0000-4000-8000-000000000004', '10000000-0000-4000-8000-000000000002', 'USD', 69, 'MONTH', 0, 'ACTIVE'),
    ('20000000-0000-4000-8000-000000000005', '10000000-0000-4000-8000-000000000003', 'COP', 599000, 'MONTH', 0, 'ACTIVE'),
    ('20000000-0000-4000-8000-000000000006', '10000000-0000-4000-8000-000000000003', 'USD', 149, 'MONTH', 0, 'ACTIVE');