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

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

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