-- Migration 016 — Monetización, roles extendidos, vendedores y comisiones
-- Compatible MySQL 5.7+. Seguro de re-ejecutar (IF NOT EXISTS / CONTINUE HANDLER).

DROP PROCEDURE IF EXISTS migrate_016;

DELIMITER //
CREATE PROCEDURE migrate_016()
BEGIN
    DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; -- Duplicate column
    DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; -- Duplicate key
    DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; -- Can't DROP key

    -- ── 1. Extender ENUM role en users (owner|staff → + seller|support) ──────
    -- MySQL no permite ADD TO ENUM directamente; hay que MODIFY COLUMN.
    -- Si ya tiene los valores, este MODIFY es idempotente.
    ALTER TABLE users
        MODIFY COLUMN role ENUM('owner','staff','seller','support') NOT NULL DEFAULT 'owner';

    -- Columna seller_profile_id en users (FK a sellers, sólo para role=seller)
    ALTER TABLE users
        ADD COLUMN seller_profile_id INT UNSIGNED NULL AFTER role;

    -- ── 2. Columnas de billing en tenants ─────────────────────────────────────
    ALTER TABLE tenants
        ADD COLUMN plan               VARCHAR(16)    NOT NULL DEFAULT 'free'
            COMMENT 'free|basic|premium|custom';

    ALTER TABLE tenants
        ADD COLUMN billing_method     VARCHAR(16)    NULL
            COMMENT 'stripe|cash|bac';

    ALTER TABLE tenants
        ADD COLUMN billing_status     VARCHAR(16)    NOT NULL DEFAULT 'free'
            COMMENT 'trial|free|active|past_due|canceled';

    ALTER TABLE tenants
        ADD COLUMN current_period_end DATE           NULL;

    ALTER TABLE tenants
        ADD COLUMN mrr                DECIMAL(8,2)   NOT NULL DEFAULT 0;

    ALTER TABLE tenants
        ADD COLUMN setup_paid         TINYINT(1)     NOT NULL DEFAULT 0;

    ALTER TABLE tenants
        ADD COLUMN seller_id          INT UNSIGNED   NULL
            COMMENT 'FK a sellers.id — vendedor que activó este tenant';

    ALTER TABLE tenants
        ADD COLUMN convos_used_month  INT UNSIGNED   NOT NULL DEFAULT 0
            COMMENT 'Se resetea el 1ro de cada mes';

    ALTER TABLE tenants
        ADD COLUMN stripe_customer_id VARCHAR(64)    NULL;

    ALTER TABLE tenants
        ADD COLUMN stripe_sub_id      VARCHAR(64)    NULL;

    ALTER TABLE tenants
        ADD COLUMN trial_ends_at      DATE           NULL;

    -- ── 3. Tabla sellers ──────────────────────────────────────────────────────
    CREATE TABLE IF NOT EXISTS sellers (
        id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        nombre               VARCHAR(120) NOT NULL,
        telefono             VARCHAR(32)  NULL,
        email                VARCHAR(160) NOT NULL,
        comision_setup_pct   DECIMAL(5,2) NOT NULL DEFAULT 35.00,
        comision_mrr_pct     DECIMAL(5,2) NOT NULL DEFAULT 15.00,
        comision_meses       INT UNSIGNED NOT NULL DEFAULT 12,
        activo               TINYINT(1)   NOT NULL DEFAULT 1,
        created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_seller_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    -- ── 4. Tabla commissions ──────────────────────────────────────────────────
    CREATE TABLE IF NOT EXISTS commissions (
        id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        seller_id   INT UNSIGNED NOT NULL,
        tenant_id   BIGINT UNSIGNED NOT NULL,
        tipo        ENUM('setup','mrr') NOT NULL,
        monto       DECIMAL(8,2) NOT NULL,
        periodo     CHAR(7)      NULL COMMENT 'YYYY-MM para tipo=mrr',
        pagada      TINYINT(1)   NOT NULL DEFAULT 0,
        created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_comm_seller  (seller_id),
        INDEX idx_comm_tenant  (tenant_id),
        INDEX idx_comm_pagada  (pagada)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    -- ── 5. Tabla staff (sub-usuarios del tenant sin acceso al dashboard completo) ─
    CREATE TABLE IF NOT EXISTS staff (
        id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        tenant_id   BIGINT UNSIGNED NOT NULL,
        nombre      VARCHAR(120) NOT NULL,
        rol         VARCHAR(40)  NOT NULL DEFAULT 'barbero',
        activo      TINYINT(1)   NOT NULL DEFAULT 1,
        created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_staff_tenant (tenant_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    -- ── 6. Tabla stripe_events (idempotencia de webhooks) ────────────────────
    CREATE TABLE IF NOT EXISTS stripe_events (
        event_id    VARCHAR(64) PRIMARY KEY,
        processed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    -- ── 7. Índices adicionales en tenants ─────────────────────────────────────
    ALTER TABLE tenants ADD INDEX idx_tenants_plan          (plan);
    ALTER TABLE tenants ADD INDEX idx_tenants_billing_status (billing_status);
    ALTER TABLE tenants ADD INDEX idx_tenants_seller_id     (seller_id);

END //
DELIMITER ;

CALL migrate_016();
DROP PROCEDURE IF EXISTS migrate_016;