
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 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 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');
