-- ============================================================
-- IHMS Website CMS - Database Migration Script
-- Database: ihms_cms
-- ============================================================

CREATE DATABASE IF NOT EXISTS ihms_cms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ihms_cms;

-- -----------------------------------------------------------
-- 1. ADMINS TABLE
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'editor') NOT NULL DEFAULT 'editor',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- 2. BLOG POSTS TABLE
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS blog_posts (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    cover_image_url VARCHAR(500),
    content_blocks JSON DEFAULT NULL,
    status ENUM('active', 'inactive', 'draft') NOT NULL DEFAULT 'draft',
    seo_title VARCHAR(255) DEFAULT NULL,
    seo_description TEXT DEFAULT NULL,
    seo_keywords VARCHAR(500) DEFAULT NULL,
    author_id CHAR(36) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- 3. NEWS POSTS TABLE
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS news_posts (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    content TEXT,
    cover_image_url VARCHAR(500),
    category VARCHAR(100) DEFAULT NULL,  -- References tags via tag_id
    tag_id CHAR(36) DEFAULT NULL,
    socials JSON DEFAULT NULL,
    source_link VARCHAR(500) DEFAULT NULL,
    cta_plan VARCHAR(255) DEFAULT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    author_id CHAR(36) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- 4. CSR CAMPAIGNS TABLE
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS csr_campaigns (
    id CHAR(36) PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    cover_image_url VARCHAR(500),
    short_desc_one TEXT,
    short_desc_two TEXT,
    content_blocks JSON DEFAULT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    seo_title VARCHAR(255) DEFAULT NULL,
    seo_description TEXT DEFAULT NULL,
    seo_keywords VARCHAR(500) DEFAULT NULL,
    author_id CHAR(36) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- 5. FAQS TABLE
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS faqs (
    id CHAR(36) PRIMARY KEY,
    category VARCHAR(100) NOT NULL,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- 6. PARTNERS TABLE
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS partners (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    logo_url VARCHAR(500) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- 7. TAGS TABLE (for news categories)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS tags (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- 8. MEDIA FILES TABLE
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS media_files (
    id CHAR(36) PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size INT UNSIGNED NOT NULL,
    url VARCHAR(500) NOT NULL,
    uploaded_by CHAR(36) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- INDEXES
-- -----------------------------------------------------------
CREATE INDEX idx_blog_status ON blog_posts(status);
CREATE INDEX idx_news_status ON news_posts(status);
CREATE INDEX idx_news_tag ON news_posts(tag_id);
CREATE INDEX idx_csr_status ON csr_campaigns(status);
CREATE INDEX idx_faq_category ON faqs(category);
CREATE INDEX idx_partner_status ON partners(status);

-- -----------------------------------------------------------
-- DEFAULT SEED DATA
-- -----------------------------------------------------------
-- Password: admin123 (bcrypt hash)
INSERT INTO admins (id, name, email, password, role) VALUES
(UUID(), 'Super Admin', 'admin@ihms.com', '$2y$10$DByiintdwDHfahQAJ0fDtOfmAAvPFBjDveoxFcu5Yq1LUcxL3elAG', 'super_admin'),
(UUID(), 'Editor', 'editor@ihms.com', '$2y$10$DByiintdwDHfahQAJ0fDtOfmAAvPFBjDveoxFcu5Yq1LUcxL3elAG', 'editor');

INSERT INTO tags (id, name) VALUES
(UUID(), 'Health Tips'),
(UUID(), 'Company News'),
(UUID(), 'Wellness'),
(UUID(), 'Announcements');