-- Fizasite / CRIMSON - Dynamic Website Database
-- Database: fizasie

CREATE DATABASE IF NOT EXISTS fizasie;
USE fizasie;

-- Admin users
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO admin_users (username, password, email) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@fizasite.com');
-- Default password: password

-- Site settings (logo text, tagline, etc.)
CREATE TABLE IF NOT EXISTS site_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO site_settings (setting_key, setting_value) VALUES 
('site_name', 'CRIMSON'),
('tagline', 'Manufacturer of Corrugated Boxes'),
('contact_email', 'info@crimson.com'),
('contact_phone', ''),
('contact_address', '');

-- Page content (About, Technology & Quality, etc.)
CREATE TABLE IF NOT EXISTS pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    content LONGTEXT,
    meta_description VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO pages (slug, title, content, meta_description) VALUES 
('about', 'About Us', '<p>We are a leading manufacturer of corrugated boxes...</p>', 'About CRIMSON - Manufacturer of Corrugated Boxes'),
('technology-quality', 'Technology & Quality', '<p>Our commitment to quality and technology...</p>', 'Technology and Quality at CRIMSON'),
('home', 'Home', '<p>Welcome to CRIMSON - Manufacturer of Corrugated Boxes.</p>', 'CRIMSON - Manufacturer of Corrugated Boxes');

-- Product categories
CREATE TABLE IF NOT EXISTS product_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO product_categories (name, slug, description, sort_order) VALUES 
('Corrugated Boxes', 'corrugated-boxes', 'Standard corrugated packaging', 1),
('Custom Boxes', 'custom-boxes', 'Custom sized boxes', 2);

-- Products
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL,
    description TEXT,
    specifications TEXT,
    image VARCHAR(255),
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL
);

-- Photo gallery
CREATE TABLE IF NOT EXISTS gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    image VARCHAR(255) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Video gallery
CREATE TABLE IF NOT EXISTS videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    video_url VARCHAR(500) NOT NULL,
    thumbnail VARCHAR(255),
    description TEXT,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Enquiries
CREATE TABLE IF NOT EXISTS enquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    subject VARCHAR(200),
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Contact form submissions (optional, can use enquiries)
CREATE TABLE IF NOT EXISTS contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Navigation menu (optional - for dynamic menu)
CREATE TABLE IF NOT EXISTS menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    url VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
);

INSERT INTO menu_items (label, url, sort_order) VALUES 
('Home', 'index.php', 1),
('About us', 'about.php', 2),
('Products', 'products.php', 3),
('Technology & Quality', 'technology-quality.php', 4),
('Enquiry', 'enquiry.php', 5),
('PhotoGallery', 'gallery.php', 6),
('video gallery', 'video-gallery.php', 7),
('Contact us', 'contact.php', 8);

-- Add sample products
INSERT INTO products (category_id, name, slug, description, specifications, image, sort_order) VALUES 
(1, 'Single Wall Corrugated Box', 'single-wall-corrugated-box', 'Strong single wall corrugated boxes for general packaging.', 'Material: Kraft | Flute: B | Custom sizes available', NULL, 1),
(2, 'Double Wall Shipping Box', 'double-wall-shipping-box', 'Heavy duty double wall boxes for secure shipping.', 'Material: Kraft | Flute: BC | High strength', NULL, 2);

-- Stats counters (home client section) — admin: Stats
CREATE TABLE IF NOT EXISTS stats_counters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(50) NOT NULL,
    label VARCHAR(200) NOT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO stats_counters (value, label, sort_order) VALUES
('6+', 'Year of Excellence', 1), ('50+', 'Employee', 2), ('1000+', 'Projects Done', 3), ('30+', 'Happy Customers', 4);

-- Footer columns — admin: Footer
CREATE TABLE IF NOT EXISTS footer_columns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body TEXT,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO footer_columns (title, body, sort_order) VALUES
('Quick links', 'Home|index.php\nAbout us|about.php\nProducts|products.php\nContact us|contact.php', 1),
('Contact', 'Crimson Uni Quepack Pvt. Ltd.\nPlot No: 117, Sector 6A, SIDCUL\nHaridwar- 249403\n\ncrimsonhwr@gmail.com', 2),
('Hours', 'Mon – Sat: 9:00 AM – 6:00 PM\nSunday: Closed', 3);

INSERT INTO site_settings (setting_key, setting_value) VALUES
('client_section_headline', 'A ONE STOP SOLUTION FOR WORLD CLASS QUALITY PRINTED PACKAGING PRODUCTS'),
('client_section_subtext', 'We create value with our clients, deliver consistently with the single aim to reach higher by making smart investments in technology for smart innovations that push the envelope.')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Our clients (home logo strip) — admin: Our clients
CREATE TABLE IF NOT EXISTS our_clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    logo VARCHAR(255) NOT NULL,
    website_url VARCHAR(500) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO site_settings (setting_key, setting_value) VALUES
('our_clients_title', 'Our clients'),
('our_clients_subtext', 'Trusted by brands across industries')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Section images (multi-image strips for pages/sections) — admin: Page edit + Home sections
CREATE TABLE IF NOT EXISTS section_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_key VARCHAR(100) NOT NULL,
    image VARCHAR(255) NOT NULL,
    caption VARCHAR(255) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_section_key (section_key),
    KEY idx_active (is_active)
);

-- Home "Why businesses choose us" feature cards — admin: Home blocks
CREATE TABLE IF NOT EXISTS home_features (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    icon_type VARCHAR(20) DEFAULT 'emoji',
    icon_value VARCHAR(255) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    color_class VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO home_features (title, description, icon_type, icon_value, sort_order, color_class) VALUES
('Quality Packaging', 'Single-wall & double-wall boxes that survive transit. ISO-aligned processes and consistent board grades.', 'emoji', '📦', 1, 'teal'),
('Custom Sizes', 'Standard cartons to made-to-measure specs—die-cut flaps, printed sleeves, and bulk orders on schedule.', 'emoji', '⚙️', 2, 'amber'),
('Sustainable', 'Recyclable kraft and responsible sourcing. Right-sized boxes mean less waste and lower freight.', 'emoji', '🌱', 3, 'violet');

-- "Why choose us?" section uses site_settings (title, lead, list, button, image)
INSERT INTO site_settings (setting_key, setting_value) VALUES
('features_section_title', 'Why businesses choose us'),
('features_section_sub', 'Quality corrugated solutions from one reliable partner.'),
('why_choose_title', 'Why choose us?'),
('why_choose_lead', 'Modern corrugators and finishing lines—straight, strong boxes every time.'),
('why_choose_list', "On-time delivery for bulk & recurring orders\nTechnology & quality checks at every stage\nDedicated enquiry & support team"),
('why_choose_btn_text', 'About us'),
('why_choose_btn_url', 'about.php')
ON DUPLICATE KEY UPDATE setting_key = setting_key;
