-- ============================================================================
-- Referidos robusto (administrativo + código único por usuario)
-- Base de datos objetivo: atencion_cliente
-- ============================================================================

CREATE TABLE IF NOT EXISTS portal_referidor_codigos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username_referidor VARCHAR(64) NOT NULL,
    codigo_referido VARCHAR(20) NOT NULL,
    source ENUM('portal_auto','admin_manual','import') NOT NULL DEFAULT 'portal_auto',
    created_by VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_username_referidor (username_referidor),
    UNIQUE KEY uk_codigo_referido (codigo_referido),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS portal_referidos_admin_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    referral_id INT DEFAULT NULL,
    action VARCHAR(40) NOT NULL,
    admin_user VARCHAR(100) NOT NULL,
    username_referidor VARCHAR(64) DEFAULT NULL,
    codigo_referido VARCHAR(20) DEFAULT NULL,
    payload_json JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_referral_id (referral_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
