-- Add category support to multilink_items table
ALTER TABLE multilink_items 
ADD COLUMN category VARCHAR(50) DEFAULT 'general' AFTER icon,
ADD COLUMN display_order INT DEFAULT 0 AFTER category,
ADD COLUMN is_enabled TINYINT(1) DEFAULT 1 AFTER display_order;

-- Create link_categories table for predefined categories
CREATE TABLE IF NOT EXISTS link_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    icon VARCHAR(100),
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert predefined categories
INSERT INTO link_categories (name, slug, icon, display_order) VALUES
('Social Media', 'social', 'fa-share-alt', 1),
('Tourism & Travel', 'tourism', 'fa-plane', 2),
('Contact', 'contact', 'fa-phone', 3),
('Services', 'services', 'fa-briefcase', 4),
('Commerce', 'commerce', 'fa-shopping-cart', 5),
('Location', 'location', 'fa-map-marker-alt', 6),
('General', 'general', 'fa-link', 7);

-- Create link_templates table for predefined platform links
CREATE TABLE IF NOT EXISTS link_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED,
    platform_name VARCHAR(100) NOT NULL,
    platform_slug VARCHAR(100) NOT NULL UNIQUE,
    icon VARCHAR(100),
    base_url VARCHAR(255),
    url_pattern VARCHAR(255),
    placeholder_text VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES link_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Social Media Platforms
INSERT INTO link_templates (category_id, platform_name, platform_slug, icon, base_url, url_pattern, placeholder_text, display_order) VALUES
-- Social Media
(1, 'Facebook', 'facebook', 'fab fa-facebook', 'https://facebook.com/', 'https://facebook.com/{username}', 'Enter Facebook page/profile URL', 1),
(1, 'Instagram', 'instagram', 'fab fa-instagram', 'https://instagram.com/', 'https://instagram.com/{username}', 'Enter Instagram username', 2),
(1, 'LinkedIn', 'linkedin', 'fab fa-linkedin', 'https://linkedin.com/', 'https://linkedin.com/company/{company}', 'Enter LinkedIn company/profile URL', 3),
(1, 'TikTok', 'tiktok', 'fab fa-tiktok', 'https://tiktok.com/', 'https://tiktok.com/@{username}', 'Enter TikTok username', 4),
(1, 'YouTube', 'youtube', 'fab fa-youtube', 'https://youtube.com/', 'https://youtube.com/{channel}', 'Enter YouTube channel URL', 5),
(1, 'X (Twitter)', 'twitter', 'fab fa-twitter', 'https://twitter.com/', 'https://twitter.com/{username}', 'Enter X/Twitter username', 6),
(1, 'Pinterest', 'pinterest', 'fab fa-pinterest', 'https://pinterest.com/', 'https://pinterest.com/{username}', 'Enter Pinterest username', 7),
(1, 'Snapchat', 'snapchat', 'fab fa-snapchat', 'https://snapchat.com/', 'https://snapchat.com/add/{username}', 'Enter Snapchat username', 8),
(1, 'Telegram', 'telegram', 'fab fa-telegram', 'https://t.me/', 'https://t.me/{username}', 'Enter Telegram username/channel', 9),
(1, 'WhatsApp Channel', 'whatsapp-channel', 'fab fa-whatsapp', 'https://whatsapp.com/', 'https://whatsapp.com/channel/{id}', 'Enter WhatsApp channel link', 10),
(1, 'Discord', 'discord', 'fab fa-discord', 'https://discord.gg/', 'https://discord.gg/{invite}', 'Enter Discord invite code', 11);

-- Insert Tourism & Travel Platforms
INSERT INTO link_templates (category_id, platform_name, platform_slug, icon, base_url, url_pattern, placeholder_text, display_order) VALUES
(2, 'TripAdvisor', 'tripadvisor', 'fas fa-map-signs', 'https://tripadvisor.com/', 'https://tripadvisor.com/{listing}', 'Enter TripAdvisor listing URL', 1),
(2, 'Booking.com', 'booking', 'fas fa-hotel', 'https://booking.com/', 'https://booking.com/hotel/{property}', 'Enter Booking.com property URL', 2),
(2, 'Airbnb', 'airbnb', 'fab fa-airbnb', 'https://airbnb.com/', 'https://airbnb.com/rooms/{listing}', 'Enter Airbnb listing URL', 3),
(2, 'Expedia', 'expedia', 'fas fa-suitcase', 'https://expedia.com/', 'https://expedia.com/{listing}', 'Enter Expedia listing URL', 4),
(2, 'Agoda', 'agoda', 'fas fa-bed', 'https://agoda.com/', 'https://agoda.com/{property}', 'Enter Agoda property URL', 5),
(2, 'Google Travel', 'google-travel', 'fab fa-google', 'https://google.com/travel/', 'https://google.com/travel/{destination}', 'Enter Google Travel URL', 6),
(2, 'Google Maps', 'google-maps', 'fas fa-map-marker-alt', 'https://maps.google.com/', 'https://maps.google.com/?q={location}', 'Enter location or coordinates', 7),
(2, 'GetYourGuide', 'getyourguide', 'fas fa-compass', 'https://getyourguide.com/', 'https://getyourguide.com/{tour}', 'Enter GetYourGuide tour URL', 8),
(2, 'Viator', 'viator', 'fas fa-route', 'https://viator.com/', 'https://viator.com/{tour}', 'Enter Viator tour URL', 9),
(2, 'Klook', 'klook', 'fas fa-ticket-alt', 'https://klook.com/', 'https://klook.com/{activity}', 'Enter Klook activity URL', 10),
(2, 'Hotels.com', 'hotels', 'fas fa-building', 'https://hotels.com/', 'https://hotels.com/{property}', 'Enter Hotels.com property URL', 11),
(2, 'Skyscanner', 'skyscanner', 'fas fa-plane-departure', 'https://skyscanner.com/', 'https://skyscanner.com/{route}', 'Enter Skyscanner search URL', 12);

-- Insert Contact Platforms
INSERT INTO link_templates (category_id, platform_name, platform_slug, icon, base_url, url_pattern, placeholder_text, display_order) VALUES
(3, 'Phone Call', 'phone', 'fas fa-phone', 'tel:', 'tel:{number}', 'Enter phone number', 1),
(3, 'Email', 'email', 'fas fa-envelope', 'mailto:', 'mailto:{email}', 'Enter email address', 2),
(3, 'WhatsApp Business', 'whatsapp', 'fab fa-whatsapp', 'https://wa.me/', 'https://wa.me/{number}', 'Enter WhatsApp number (with country code)', 3),
(3, 'SMS', 'sms', 'fas fa-sms', 'sms:', 'sms:{number}', 'Enter SMS number', 4);

-- Insert Commerce Platforms
INSERT INTO link_templates (category_id, platform_name, platform_slug, icon, base_url, url_pattern, placeholder_text, display_order) VALUES
(5, 'Online Shop', 'shop', 'fas fa-shopping-bag', '', '{url}', 'Enter shop URL', 1),
(5, 'Booking System', 'booking-system', 'fas fa-calendar-check', '', '{url}', 'Enter booking system URL', 2),
(5, 'Event Tickets', 'tickets', 'fas fa-ticket-alt', '', '{url}', 'Enter ticketing URL', 3);

-- Insert Location/Services
INSERT INTO link_templates (category_id, platform_name, platform_slug, icon, base_url, url_pattern, placeholder_text, display_order) VALUES
(4, 'Company Website', 'website', 'fas fa-globe', '', '{url}', 'Enter website URL', 1),
(4, 'Product Page', 'product', 'fas fa-box', '', '{url}', 'Enter product page URL', 2),
(4, 'Service Page', 'service', 'fas fa-concierge-bell', '', '{url}', 'Enter service page URL', 3),
(6, 'Branch Location', 'branch', 'fas fa-map-pin', '', '{url}', 'Enter Google Maps URL', 1),
(6, 'Event Location', 'event-location', 'fas fa-map-marked-alt', '', '{url}', 'Enter event location URL', 2);

-- Add indexes for performance
CREATE INDEX idx_multilink_items_category ON multilink_items(category);
CREATE INDEX idx_multilink_items_order ON multilink_items(display_order);
CREATE INDEX idx_multilink_items_enabled ON multilink_items(is_enabled);
CREATE INDEX idx_link_templates_category ON link_templates(category_id);
CREATE INDEX idx_link_templates_slug ON link_templates(platform_slug);