-- ============================================
-- MIGRACIÓN: Sistema de Usuarios y Autenticación
-- Fecha: 2026-01-26
-- Descripción: Extender sistema de usuarios con autenticación moderna
-- ============================================

-- ============================================
-- PASO 1: EXTENDER TABLA sec_users O CREAR users
-- ============================================

-- Verificar si existe sec_users y extenderla, o crear nueva tabla users
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    tenant_id INT UNSIGNED NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'lector',
    activo TINYINT(1) NOT NULL DEFAULT 1,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_tenant (tenant_id),
    INDEX idx_tenant_email (tenant_id, email),
    INDEX idx_role (role),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

-- Si existe sec_users, migrar datos (opcional)
-- INSERT INTO users (email, password_hash, nombre, tenant_id, role, activo)
-- SELECT 
--     COALESCE(email, CONCAT(login, '@aprsolo.local')) as email,
--     password as password_hash,
--     COALESCE(name, login) as nombre,
--     1 as tenant_id,
--     COALESCE(role, 'lector') as role,
--     COALESCE(priv_admin, 0) as activo
-- FROM sec_users
-- WHERE NOT EXISTS (SELECT 1 FROM users WHERE users.email = sec_users.email);

-- ============================================
-- PASO 2: CREAR USUARIO ADMIN POR DEFECTO
-- ============================================
-- Password: admin123 (debe cambiarse en producción)
-- Hash bcrypt: $2a$10$rOzJqJqJqJqJqJqJqJqJqO (ejemplo, usar bcrypt real)

-- INSERT INTO users (email, password_hash, nombre, tenant_id, role, activo)
-- VALUES (
--     'admin@aprsolo.local',
--     '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', -- admin123
--     'Administrador',
--     1,
--     'admin',
--     1
-- ) ON DUPLICATE KEY UPDATE email=email;

-- ============================================
-- PASO 3: TABLA DE SESIONES (opcional, para NextAuth)
-- ============================================

CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    tenant_id INT UNSIGNED NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_tenant (tenant_id),
    INDEX idx_expires (expires_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

-- ============================================
-- VERIFICACIÓN
-- ============================================
-- SELECT COUNT(*) as total_users FROM users;
-- SELECT tenant_id, role, COUNT(*) as usuarios FROM users GROUP BY tenant_id, role;