-- ============================================================
-- TAPP CARDS - Linktree-like Digital Cards System
-- ============================================================

-- ============================================================
-- 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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) 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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    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,
    category_id INT NOT NULL,
    profile_type ENUM('empresa', 'comercial', 'operativo') NOT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_category_profile (category_id, profile_type),
    FOREIGN KEY (category_id) REFERENCES link_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- INSERT DEFAULT LINK CATEGORIES
-- ============================================================
INSERT INTO link_categories (id, nombre, nombre_display, icono, orden) VALUES
(1, 'contactos', 'CONTACTOS', 'bi-telephone', 1),
(2, 'redes_sociales', 'REDES SOCIALES', 'bi-share', 2),
(3, 'multimedia', 'MULTIMEDIA', 'bi-play-circle', 3),
(4, 'metodos_pago', 'MÉTODOS DE PAGO', 'bi-credit-card', 4),
(5, 'otros', 'OTROS LINKS', 'bi-link-45deg', 5);

-- ============================================================
-- INSERT DEFAULT LINK TYPES
-- ============================================================
INSERT INTO link_types (category_id, nombre, nombre_display, tipo, color, icono, orden) VALUES
-- CONTACTOS (1)
(1, 'telefono', 'Teléfono', 'phone', '#28a745', 'bi-telephone', 1),
(1, 'whatsapp', 'WhatsApp', 'whatsapp', '#25D366', 'bi-whatsapp', 2),
(1, 'email', 'Email', 'email', '#EA4335', 'bi-envelope', 3),
(1, 'sitio_web', 'Sitio Web', 'website', '#4285F4', 'bi-globe', 4),
(1, 'mensajes', 'Mensajes Móvil', 'sms', '#34B7F1', 'bi-chat-dots', 5),
(1, 'google_maps', 'Google Maps', 'maps', '#34A853', 'bi-geo-alt', 6),
(1, 'telegram', 'Telegram', 'telegram', '#0088cc', 'bi-telegram', 7),
(1, 'discord', 'Discord', 'discord', '#5865F2', 'bi-discord', 8),

-- REDES SOCIALES (2)
(2, 'facebook', 'Facebook', 'facebook', '#1877F2', 'bi-facebook', 1),
(2, 'instagram', 'Instagram', 'instagram', '#E4405F', 'bi-instagram', 2),
(2, 'tiktok', 'TikTok', 'tiktok', '#000000', 'bi-tiktok', 3),
(2, 'twitter', 'X (Twitter)', 'twitter', '#000000', 'bi-twitter-x', 4),
(2, 'threads', 'Threads', 'threads', '#000000', 'bi-threads', 5),
(2, 'youtube', 'YouTube', 'youtube', '#FF0000', 'bi-youtube', 6),
(2, 'linkedin', 'LinkedIn', 'linkedin', '#0A66C2', 'bi-linkedin', 7),
(2, 'pinterest', 'Pinterest', 'pinterest', '#BD081C', 'bi-pinterest', 8),
(2, 'snapchat', 'Snapchat', 'snapchat', '#FFFC00', 'bi-snapchat', 9),
(2, 'twitch', 'Twitch', 'twitch', '#9146FF', 'bi-twitch', 10),
(2, 'reddit', 'Reddit', 'reddit', '#FF4500', 'bi-reddit', 11),

-- MULTIMEDIA (3)
(3, 'youtube_video', 'YouTube Video', 'youtube', '#FF0000', 'bi-youtube', 1),
(3, 'tiktok_video', 'TikTok Video', 'tiktok', '#000000', 'bi-tiktok', 2),
(3, 'vimeo_video', 'Vimeo Video', 'vimeo', '#1AB7EA', 'bi-vimeo', 3),
(3, 'video_streaming', 'Video Streaming', 'video', '#9146FF', 'bi-play-btn', 4),
(3, 'pdf', 'PDF / Documento', 'pdf', '#DC3545', 'bi-file-pdf', 5),
(3, 'musica', 'Música', 'music', '#1DB954', 'bi-music-note-beamed', 6),
(3, 'spotify', 'Spotify', 'spotify', '#1DB954', 'bi-spotify', 7),
(3, 'apple_music', 'Apple Music', 'apple', '#FC3C44', 'bi-apple', 8),
(3, 'soundcloud', 'SoundCloud', 'soundcloud', '#FF5500', 'bi-cloud', 9),

-- MÉTODOS DE PAGO (4)
(4, 'paypal', 'PayPal', 'paypal', '#003087', 'bi-paypal', 1),
(4, 'qr_banco', 'QR Banco', 'qr', '#0066CC', 'bi-qr-code', 2),
(4, 'nequi', 'Nequi', 'nequi', '#FF0080', 'bi-currency-dollar', 3),
(4, 'daviplata', 'Daviplata', 'daviplata', '#FF6600', 'bi-currency-exchange', 4),
(4, 'link_pago', 'Link de Pago PSE', 'payment', '#28a745', 'bi-link-45deg', 5),

-- OTROS LINKS (5)
(5, 'google_drive', 'Google Drive', 'drive', '#4285F4', 'bi-drive', 1),
(5, 'google_calendar', 'Google Calendar', 'calendar', '#4285F4', 'bi-calendar-event', 2),
(5, 'canva', 'Canva', 'canva', '#00C4CC', 'bi-palette', 3),
(5, 'google_review', 'Calificación Google', 'review', '#34A853', 'bi-star', 4),
(5, 'mensaje_destacado', 'Mensaje Destacado', 'text', '#6c757d', 'bi-chat-quote', 5);

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

-- ============================================================
-- INSERT DEFAULT ADMIN USER
-- Password: admin123
-- ============================================================
INSERT INTO users (username, email, password_hash, role) VALUES
('admin', 'admin@tappcards.com.co', 'scrypt:32768:8:1$kvxfMRKG6QBlmj8R$315d271fc5ec56cc1bc2b263df39852a8fcee5b83752df45ccffa77cc7504e3d4d8ef8be459540452baa9baf23421134378ce809c3a4e2e3207a1a67b47a10ea', 'admin');

-- ============================================================
-- INSERT DEFAULT COMPANY EXAMPLE
-- ============================================================
INSERT INTO companies (slug, nombre, descripcion, has_company_tapp, has_commercial_tapp, has_operational_tapp, estado) VALUES
('mi-empresa', 'Mi Empresa Demo', 'Bienvenido a Tapp Cards - Tu tarjeta digital personal', TRUE, TRUE, TRUE, 'activo');

-- ============================================================
-- INSERT DEFAULT COMPANY USER
-- Password: empresa123
-- ============================================================
INSERT INTO users (username, email, password_hash, role, company_id) VALUES
('empresa', 'empresa@miempresa.com', 'scrypt:32768:8:1$MzOpi0BgvlHIG8VW$580cedf988aa053ec7d2be70fc8c15e59b0f7a64442104eb15e501a0cb8f8e69780f61f3f98710ab1e21f3e265bbf9d55c91c2c4b7d7d77940e39e47728a8ebb', 'company', 1);