-- Websites table
CREATE TABLE IF NOT EXISTS websites (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    domain VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Blogs table with website support
CREATE TABLE IF NOT EXISTS blogs (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    website_id CHAR(36) NOT NULL,
    title VARCHAR(500) NOT NULL,
    category VARCHAR(100) NOT NULL,
    author VARCHAR(255) NOT NULL,
    image TEXT,
    hero_image TEXT,
    slideshow_images JSON,
    excerpt TEXT NOT NULL,
    content LONGTEXT NOT NULL,
    featured BOOLEAN DEFAULT FALSE,
    visible BOOLEAN DEFAULT TRUE,
    tags JSON,
    hero_data JSON,
    read_time VARCHAR(50) DEFAULT '5 min read',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE,
    INDEX idx_website_id (website_id),
    INDEX idx_category (category),
    INDEX idx_featured (featured),
    INDEX idx_visible (visible),
    INDEX idx_created_at (created_at),
    FULLTEXT idx_search (title, excerpt, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comments table with UUIDs
CREATE TABLE IF NOT EXISTS comments (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    blog_id CHAR(36) NOT NULL,
    user_id CHAR(36) NULL,
    name VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    content TEXT NOT NULL,
    parent_id CHAR(36) NULL,
    is_admin_reply BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
    INDEX idx_blog_id (blog_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reactions table with UUIDs
CREATE TABLE IF NOT EXISTS reactions (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    blog_id CHAR(36) NOT NULL,
    type ENUM('like', 'love', 'insightful', 'celebrate') NOT NULL,
    identifier VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE,
    UNIQUE KEY unique_reaction (blog_id, identifier),
    INDEX idx_blog_id (blog_id),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Blog views table with UUIDs
CREATE TABLE IF NOT EXISTS blog_views (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    blog_id CHAR(36) NOT NULL,
    identifier VARCHAR(255) NOT NULL,
    duration INT DEFAULT 0,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE,
    INDEX idx_blog_id (blog_id),
    INDEX idx_viewed_at (viewed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default websites
INSERT INTO websites (id, domain, name) VALUES 
(UUID(), 'tekjuice.co.uk', 'TekJuice Main'),
(UUID(), 'staging.tekjuice.co.uk', 'TekJuice Staging');