-- HealthLink Database Migration
-- Add missing tables for Website Content Management and Support System

-- Website Content Management Table
CREATE TABLE IF NOT EXISTS website_content (
    id INT PRIMARY KEY AUTO_INCREMENT,
    homepage_title VARCHAR(255) NOT NULL DEFAULT 'HealthLink',
    homepage_description TEXT,
    about_us TEXT,
    contact_email VARCHAR(100),
    contact_phone VARCHAR(20),
    contact_address TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Support Tickets Table
CREATE TABLE IF NOT EXISTS support_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    category VARCHAR(50),
    status ENUM('open', 'in_progress', 'closed', 'resolved') DEFAULT 'open',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    resolution_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    assigned_to INT,
    INDEX idx_support_status (status),
    INDEX idx_support_email (email),
    INDEX idx_support_created (created_at)
);

-- Insert default website content if it doesn't exist
INSERT IGNORE INTO website_content (id, homepage_title, homepage_description, about_us, contact_email, contact_phone, contact_address) 
VALUES (1, 'HealthLink', 'Your Health, Our Priority', 'HealthLink is a comprehensive healthcare platform connecting patients with qualified doctors for online and physical consultations.', 'info@healthlink.com', '+92-300-1234567', 'Lahore, Pakistan');
