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

-- ============================================================
-- DROP ALL TABLES (in reverse dependency order)
-- ============================================================

-- Drop dependent tables first


-- ============================================================
-- 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;

-- ============================================================
-- 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;

-- 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);
