-- Create database
CREATE DATABASE IF NOT EXISTS panorama_cms;
USE panorama_cms;

-- Pages table
CREATE TABLE IF NOT EXISTS pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    content TEXT,
    meta_description VARCHAR(255),
    meta_keywords VARCHAR(255),
    status ENUM('published', 'draft') DEFAULT 'draft',
    menu_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE SET NULL ON UPDATE CASCADE 
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Posts table
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    content TEXT,
    excerpt TEXT,
    featured_image VARCHAR(255),
    category_id INT,
    author_id INT,
    status ENUM('published', 'draft') DEFAULT 'draft',
    pages_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (pages_id) REFERENCES pages(id) ON DELETE SET NULL ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES 
('site_name', 'Panorama Infosys Limited'),
('site_description', 'Welcome to Panorama Infosys Limited'),
('site_keywords', 'panorama, infosys, technology, services'),
('site_logo', ''),
('site_favicon', ''),
('admin_email', 'admin@panoramainfosys.com'),
('facebook_url', 'https://www.facebook.com/panoramainfosysltd/'),
('twitter_url', ''),
('linkedin_url', 'https://www.linkedin.com/company/panorama-infosys-ltd/'),
('company_address', 'Dhaka Trade Center (14th Floor), 99, Kazi Nazrul Islam Avenue Kawran Bazar, Dhaka-1215. Bangladesh.'),
('company_phone', '+88 02 55012248, 55012254'),
('company_email', 'hello@panoramainfosys.com'),
('header_bg_color', '#e6e6e6'),
('middle_bg_color', '#e6e6e6'),
('footer_bg_color', '#e6e6e6'),
('footer_font_color', '#000000'),
('footer_font_hover_color', '#ffffff'),
('footer_text', 'We deliver innovative digital solutions — from web apps to enterprise systems. Our mission is excellence, performance, and customer sat')
;

-- Hero Section table
CREATE TABLE IF NOT EXISTS hero_section (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    subtitle TEXT,
    image_path VARCHAR(255),
    button_text VARCHAR(50),
    button_link VARCHAR(255),
    active BOOLEAN DEFAULT true,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    carosol_badge_back_color VARCHAR(50),
    carosol_badge_font_color VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS footer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    footer_header VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS footer_text (
    id INT AUTO_INCREMENT PRIMARY KEY,
    footer_text VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS footer_link (
    id INT AUTO_INCREMENT PRIMARY KEY,
    header_id INT NOT NULL UNIQUE,
    link_name VARCHAR(255),
    linked_page VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS submenu_two_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content varchar(255) NOT NULL,
    image_path VARCHAR(255),
    submenutwoid INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add foreign key constraint for pages.menu_id -> menus.id
-- Use ALTER TABLE here because `pages` is created earlier in this file.
-- This will ensure referential integrity; on delete of a menu we set page.menu_id to NULL.
ALTER TABLE pages
    ADD CONSTRAINT fk_pages_menu
    FOREIGN KEY (menu_id) REFERENCES menus(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS aboutus (
    id INT AUTO_INCREMENT PRIMARY KEY,
    header_image VARCHAR(255) NOT NULL,
    content TEXT,
    side_image VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;