-- ============================================================
-- TAPP CARDS - Fix for cPanel MySQL (older versions)
-- Fix: Only one TIMESTAMP with CURRENT_TIMESTAMP per table
-- ============================================================

-- ============================================================
-- DROP ALL TABLES (in reverse dependency order)
-- ============================================================
SET FOREIGN_KEY_CHECKS = 0;

-- Drop dependent tables first
DROP TABLE IF EXISTS employee_links;
DROP TABLE IF EXISTS company_links;
DROP TABLE IF EXISTS link_types;
DROP TABLE IF EXISTS link_categories;
DROP TABLE IF EXISTS profile_settings;
DROP TABLE IF EXISTS field_values;
DROP TABLE IF EXISTS fields;
DROP TABLE IF EXISTS field_categories;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS password_reset_tokens;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS settings;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- SETTINGS TABLE
-- ============================================================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(255) UNIQUE NOT NULL,
    key_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COMPANIES TABLE
-- ============================================================
CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(255) UNIQUE NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    nit VARCHAR(50) NULL,
    descripcion TEXT NULL,
    email VARCHAR(255) NULL,
    telefono VARCHAR(50) NULL,
    direccion VARCHAR(500) NULL,
    imagen_perfil VARCHAR(500) NULL,
    imagen_portada VARCHAR(500) NULL,
    facebook VARCHAR(500) NULL,
    instagram VARCHAR(500) NULL,
    twitter VARCHAR(500) NULL,
    linkedin VARCHAR(500) NULL,
    youtube VARCHAR(500) NULL,
    whatsapp VARCHAR(50) NULL,
    web VARCHAR(500) NULL,
    color_principal VARCHAR(20) DEFAULT '#000000',
    color_fondo VARCHAR(20) DEFAULT '#ffffff',
    has_company_tapp BOOLEAN DEFAULT TRUE,
    has_commercial_tapp BOOLEAN DEFAULT FALSE,
    has_operational_tapp BOOLEAN DEFAULT FALSE,
    inactive_message_commercial TEXT DEFAULT 'Este carnet se encuentra INACTIVO. Por favor destruya o elimine este carnet y contacte a la empresa.',
    inactive_message_operational TEXT DEFAULT 'Este carnet se encuentra INACTIVO. Por favor destruya o elimine este carnet y contacte a la empresa.',
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    INDEX idx_slug (slug),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- USERS TABLE
-- ============================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'company') NOT NULL,
    company_id INT NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_company_id (company_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EMPLOYEES TABLE
-- ============================================================
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    hash VARCHAR(64) UNIQUE NOT NULL,
    tipo ENUM('comercial', 'operativo') NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    cargo VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    telefono VARCHAR(50) NULL,
    foto_perfil VARCHAR(500) NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    -- Campos operativos
    document_type ENUM('CC', 'CE', 'PPT', 'TI', 'PP') NULL,
    document_number VARCHAR(50) NULL,
    rh ENUM('O+', 'O-', 'A+', 'A-', 'B+', 'B-', 'AB+', 'AB-') NULL,
    eps VARCHAR(255) NULL,
    pension_fund VARCHAR(255) NULL,
    arl VARCHAR(255) NULL,
    compensation_box VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    INDEX idx_hash (hash),
    INDEX idx_company_id (company_id),
    INDEX idx_tipo (tipo),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PASSWORD RESET TOKENS TABLE
-- ============================================================
CREATE TABLE password_reset_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) UNIQUE NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_token (token),
    INDEX idx_user_id (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LINK CATEGORIES TABLE (NEW - Linktree System)
-- ============================================================
CREATE TABLE link_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    nombre_display VARCHAR(100) NOT NULL,
    icono VARCHAR(100) DEFAULT 'bi-link',
    orden INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_orden (orden),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LINK TYPES TABLE (NEW - Linktree System)
-- ============================================================
CREATE TABLE link_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    nombre_display VARCHAR(100) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    color VARCHAR(20) DEFAULT '#000000',
    icono VARCHAR(100) DEFAULT 'bi-link',
    base_url VARCHAR(500) NULL,
    orden INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_active (is_active),
    FOREIGN KEY (category_id) REFERENCES link_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COMPANY LINKS TABLE (NEW - Linktree System)
-- ============================================================
CREATE TABLE company_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    link_type_id INT NOT NULL,
    valor VARCHAR(500) NOT NULL,
    label_custom VARCHAR(255) NULL,
    orden INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    INDEX idx_company (company_id),
    INDEX idx_link_type (link_type_id),
    INDEX idx_orden (orden),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (link_type_id) REFERENCES link_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EMPLOYEE LINKS TABLE (NEW - Linktree System)
-- ============================================================
CREATE TABLE employee_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    link_type_id INT NOT NULL,
    valor VARCHAR(500) NOT NULL,
    label_custom VARCHAR(255) NULL,
    orden INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    INDEX idx_employee (employee_id),
    INDEX idx_link_type (link_type_id),
    INDEX idx_orden (orden),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (link_type_id) REFERENCES link_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PROFILE SETTINGS TABLE (NEW - Linktree System)
-- ============================================================
CREATE TABLE profile_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_type ENUM('empresa', 'comercial', 'operativo') NOT NULL,
    category_id INT NOT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    UNIQUE KEY unique_profile_category (profile_type, category_id),
    FOREIGN KEY (category_id) REFERENCES link_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COMPANY STATS TABLE (NEW - Statistics System)
-- ============================================================
CREATE TABLE company_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    profile_type ENUM('empresa', 'comercial', 'operativo') NOT NULL,
    event_type ENUM('escaneo', 'descarga_vcard', 'clic_enlace') NOT NULL,
    employee_id INT NULL,
    link_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_company_id (company_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- INSERT DEFAULT DATA
-- ============================================================

-- Default admin user (password: admin123)
INSERT INTO users (username, email, password_hash, role, estado) VALUES 
('admin', 'admin@tappcards.com', 'scrypt:32768:8:1$glm28wX4RbqqELDw$b1a06382cd4603c4bdbe3ba4c602b89d6db90f62d79c66d60153a647a31faa1b055196aabb66463097376d29029021f00f0bbd8cce7d4e3a2ed8ab290cc4aba0', 'admin', 'activo');

-- Default link categories
INSERT INTO link_categories (nombre, nombre_display, icono, orden) VALUES
('CONTACTOS', 'Contactos', 'bi-person-lines-fill', 1),
('REDES_SOCIALES', 'Redes Sociales', 'bi-share-fill', 2),
('MULTIMEDIA', 'Multimedia', 'bi-collection-play-fill', 3),
('METODOS_PAGO', 'Métodos de Pago', 'bi-credit-card-fill', 4),
('OTROS_LINKS', 'Otros Links', 'bi-link-45deg', 5);

-- Default link types for CONTACTOS
INSERT INTO link_types (category_id, nombre, nombre_display, tipo, color, icono, base_url, orden) VALUES
(1, 'telefono', 'Teléfono', 'link', '#25D366', 'bi-telephone', 'tel:+', 1),
(1, 'whatsapp', 'WhatsApp', 'link', '#25D366', 'bi-whatsapp', 'https://wa.me/', 2),
(1, 'email', 'Email', 'link', '#EA4335', 'bi-envelope', 'mailto:', 3),
(1, 'sms', 'SMS', 'link', '#34B7F1', 'bi-chat-text', 'sms:', 4);

-- Enable categories for all profiles
INSERT INTO profile_settings (profile_type, category_id, is_enabled) VALUES
('empresa', 1, TRUE), ('empresa', 2, TRUE), ('empresa', 3, TRUE), ('empresa', 4, TRUE), ('empresa', 5, TRUE),
('comercial', 1, TRUE), ('comercial', 2, TRUE), ('comercial', 3, TRUE), ('comercial', 4, TRUE), ('comercial', 5, TRUE),
('operativo', 1, TRUE), ('operativo', 2, TRUE), ('operativo', 3, TRUE), ('operativo', 4, TRUE), ('operativo', 5, TRUE);