-- ============================================================
-- BLOQUE 1: BD MAESTRA DEL SISTEMA CSN
-- Registra TODOS los clientes que tienen sistemas instalados.
-- El setup wizard inserta aqui cada nuevo cliente.
-- El login unificado consulta aqui para saber a que folder/BD ir.
-- ============================================================

CREATE DATABASE IF NOT EXISTS `me_csn_master`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `me_csn_master`;

-- ============================================================
-- BLOQUE 2: TABLA DE CLIENTES REGISTRADOS
-- ============================================================
CREATE TABLE IF NOT EXISTS `csn_clients` (
    id INT(11) NOT NULL AUTO_INCREMENT,                       -- ID interno
    client_name VARCHAR(100) NOT NULL,                        -- Nombre completo del cliente
    client_user VARCHAR(50) NOT NULL,                         -- Username del owner
    client_email VARCHAR(150) NOT NULL,                       -- Email del owner
    client_folder VARCHAR(100) NOT NULL,                      -- Folder en el servidor (ej: Anaya_ME)
    client_db VARCHAR(100) NOT NULL,                          -- Nombre de la BD (ej: anaya_data_base)
    client_prefix VARCHAR(50) NOT NULL,                       -- Prefijo de tablas (ej: anaya)
    number_companies INT(11) NOT NULL DEFAULT 0,              -- Cuantas empresas tiene autorizadas
    system_type ENUM('buy','lease','trial') NOT NULL DEFAULT 'buy',
    system_price DECIMAL(10,2) NOT NULL DEFAULT 0,
    system_acquisition_date DATE NULL,
    system_next_payment DATE NULL,
    hosting_price DECIMAL(10,2) NOT NULL DEFAULT 0,
    hosting_acquisition_date DATE NULL,
    hosting_next_payment DATE NULL,
    status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login DATETIME NULL,
    last_ip VARCHAR(45) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_client_folder (client_folder),              -- Folder unico
    UNIQUE KEY uk_client_db (client_db),                      -- BD unica
    UNIQUE KEY uk_client_email (client_email),                -- Email unico
    UNIQUE KEY uk_client_user (client_user),                  -- Username unico
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
