-- Bhatt Enterprises — import into MySQL (phpMyAdmin or mysql CLI)
-- CREATE DATABASE IF NOT EXISTS bhatt_enterprise CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE bhatt_enterprise;

SET NAMES utf8mb4;

DROP TABLE IF EXISTS enquiries;
DROP TABLE IF EXISTS pricing_features;
DROP TABLE IF EXISTS pricing_plans;
DROP TABLE IF EXISTS job_openings;
DROP TABLE IF EXISTS faqs;
DROP TABLE IF EXISTS testimonials;
DROP TABLE IF EXISTS team_members;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS services;
DROP TABLE IF EXISTS banners;
DROP TABLE IF EXISTS static_pages;
DROP TABLE IF EXISTS site_settings;
DROP TABLE IF EXISTS admins;

CREATE TABLE admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE site_settings (
  setting_key VARCHAR(100) NOT NULL PRIMARY KEY,
  setting_value MEDIUMTEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE banners (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(500) NOT NULL,
  subtitle TEXT NULL,
  highlight_html VARCHAR(300) NULL,
  image_path VARCHAR(500) NULL,
  is_video TINYINT(1) NOT NULL DEFAULT 0,
  video_path VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE services (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  short_description TEXT NULL,
  full_description MEDIUMTEXT NULL,
  icon_class VARCHAR(120) NOT NULL DEFAULT 'flaticon-solar-panel',
  image_path VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE projects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT NULL,
  image_path VARCHAR(500) NULL,
  category VARCHAR(120) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  logo_path VARCHAR(500) NULL,
  website_url VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE team_members (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  designation VARCHAR(255) NULL,
  bio TEXT NULL,
  image_path VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE testimonials (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_name VARCHAR(255) NOT NULL,
  content TEXT NULL,
  rating TINYINT UNSIGNED NOT NULL DEFAULT 5,
  image_path VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE faqs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question TEXT NOT NULL,
  answer TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE enquiries (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(64) NULL,
  subject VARCHAR(255) NULL,
  message TEXT NULL,
  status ENUM('new','read','closed') NOT NULL DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE static_pages (
  slug VARCHAR(64) NOT NULL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_openings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  location VARCHAR(255) NULL,
  employment_type VARCHAR(120) NULL,
  short_description TEXT NULL,
  description MEDIUMTEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pricing_plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  card_title VARCHAR(255) NOT NULL DEFAULT 'Solar Service Price',
  price_display VARCHAR(120) NOT NULL,
  plan_name VARCHAR(255) NOT NULL,
  cta_url VARCHAR(500) NOT NULL DEFAULT 'contact.php',
  cta_label VARCHAR(120) NOT NULL DEFAULT 'Choose Packages',
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  cta_yellow TINYINT(1) NOT NULL DEFAULT 0,
  cta_in_wrapper TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pricing_features (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_id INT UNSIGNED NOT NULL,
  label TEXT NOT NULL,
  icon_style ENUM('check', 'mark') NOT NULL DEFAULT 'check',
  sort_order INT NOT NULL DEFAULT 0,
  KEY idx_plan (plan_id),
  KEY idx_sort (plan_id, sort_order),
  CONSTRAINT fk_pricing_features_plan
    FOREIGN KEY (plan_id) REFERENCES pricing_plans (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default admin login: username admin / password password  (change immediately in production)
INSERT INTO admins (username, password_hash) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

INSERT INTO site_settings (setting_key, setting_value) VALUES
('company_name', 'Bhatt Enterprises'),
('company_tagline', 'Trusted solar solutions in Bilaspur — Authorized Tata Power Solar Partner'),
('phone', '8085548615'),
('phone_display', '+91 80855 48615'),
('email', 'info@bhattenterprise.in'),
('address', 'Shop No 03, Babji Park, Infront Of Haribhumi Press, Ring Road 2, Bilaspur-Chhattisgarh - 495001'),
('hours', 'Open from 10:00 AM (Mon–Sat)'),
('map_embed', 'https://www.google.com/maps/embed?pb=!1m18!1m12!1m3!1d3715.5!2d82.15!3d22.09!2m3!1f0!2f0!3f0!3m2!1i1024!2i768!4f13.1!3m3!1m2!1s0x0%3A0x0!2zMjLCsDA1JzI0LjAiTiA4MsKwMDknMDAuMCJF!5e0!3m2!1sen!2sin!4v1'),
('home_about_title', 'Empowering Bilaspur with Clean Solar Energy'),
('home_about_text', 'We design and install high-efficiency solar rooftop systems for homes and businesses, with reliable mounting solutions and end-to-end support. Reduce your electricity bills with sustainable power from the sun.'),
('home_highlight_1', 'Tata Power Solar authorized partner'),
('home_highlight_2', 'Residential & commercial rooftop installations'),
('home_highlight_3', 'Quality panels, inverters & mounting systems'),
('contact_intro', 'Have a solar project in mind? Share your details and our team will get back to you shortly.'),
('meta_description', 'Bhatt Enterprises — Solar panels, inverters & rooftop systems in Bilaspur, Chhattisgarh. Tata Power Solar partner. Call 8085548615.'),
('home_pricing_subtitle', 'Pricing Plan'),
('home_pricing_title', 'Our Pricing Plan For you');

INSERT INTO banners (title, subtitle, highlight_html, image_path, is_video, video_path, sort_order, is_active) VALUES
('The Best Solar Solutions for', 'Residential and commercial rooftop solar backed by Tata Power Solar. Cut electricity costs with reliable, sustainable energy.', '<span class="theme-text1">Homes & Businesses</span>', 'assets/img/bg/cta-bg-1-1.jpg', 0, NULL, 1, 1),
('Trusted Solar Partner in', 'High-efficiency panels, inverters, and mounting solutions from Ring Road 2, Bilaspur.', '<span class="theme-text1">Bilaspur</span>', 'assets/img/bg/footer-bg-3-1.jpg', 0, NULL, 2, 1),
('Authorized', 'Quality installations and after-sales support for long-term peace of mind.', '<span class="theme-text1">Tata Power Solar</span>', 'assets/img/bg/choose-bg-2-1.jpg', 0, NULL, 3, 1);

INSERT INTO services (title, slug, short_description, full_description, icon_class, image_path, sort_order) VALUES
('Solar Rooftop Installation', 'solar-rooftop-installation', 'End-to-end rooftop solar for homes and commercial buildings with optimized design.', 'We assess your roof, energy needs, and grid connection to deliver a turnkey solar rooftop system including high-efficiency modules, inverter, and safe mounting structures.', 'flaticon-solar-panel', 'assets/img/about/ab-img-3-1.jpg', 1),
('Solar Inverters & Power Conditioning', 'solar-inverters', 'Reliable inverters for maximum energy yield and grid safety.', 'Selection and installation of string and hybrid inverters suited to your load profile and net metering requirements.', 'flaticon-thunderbolt', 'assets/img/about/ab-img-3-2.jpg', 2),
('Solar Panels & Mounting Systems', 'solar-panels-mounting', 'Durable mounting and premium panels for harsh weather.', 'Quality PV modules with engineered mounting solutions for tiled, sheet, or RCC roofs.', 'flaticon-solar-system', 'assets/img/about/ab-v-img.jpg', 3);

INSERT INTO projects (title, slug, description, image_path, category, sort_order) VALUES
('Residential Rooftop — Bilaspur', 'residential-rooftop-bilaspur', '5 kW grid-tied rooftop system with net metering support.', 'assets/img/bg/copyright-bg-3-1.jpg', 'Residential', 1),
('Commercial Solar — Local Business', 'commercial-solar-business', 'Reduced operating costs with a tailored commercial PV solution.', 'assets/img/bg/case-study-bg.png', 'Commercial', 2),
('Industrial Shed Mount', 'industrial-shed-mount', 'High-capacity installation with robust structural mounting.', 'assets/img/bg/about-bg-3-1.png', 'Industrial', 3);

INSERT INTO clients (name, logo_path, website_url, sort_order) VALUES
('Tata Power Solar', '', 'https://www.tatapowersolar.com/', 1),
('Residential Customers', 'assets/img/about/ab-img-3-1.jpg', '', 2),
('Commercial Partners', 'assets/img/about/ab-img-3-2.jpg', '', 3);

INSERT INTO team_members (name, designation, bio, image_path, sort_order) VALUES
('Lead Engineer', 'Solar Design & Projects', 'System design, site surveys, and quality execution for every installation.', 'assets/img/about/ab-user-1-1.png', 1),
('Operations', 'Installation & Service', 'On-site installation, commissioning, and customer support.', 'assets/img/about/ab-user-1-1.png', 2);

INSERT INTO testimonials (client_name, content, rating, sort_order) VALUES
('Residential Customer, Bilaspur', 'Professional team and clear explanation of savings. Our bills dropped significantly after going solar.', 5, 1),
('Business Owner', 'Bhatt Enterprises handled our commercial rooftop project end to end. Reliable workmanship.', 5, 2);

INSERT INTO faqs (question, answer, sort_order) VALUES
('How much can I save with rooftop solar?', 'Savings depend on your consumption, system size, and tariff. Most homes see a strong reduction in electricity bills within the first billing cycle after net metering.', 1),
('Are you authorized for Tata Power Solar products?', 'Yes. Bhatt Enterprises is an authorized partner for Tata Power Solar, offering genuine products and supported installations.', 2),
('Do solar panels work on cloudy days?', 'Yes, but at lower output than bright sunny days. Your annual generation estimate already accounts for local weather patterns.', 3),
('What is the typical installation time?', 'Residential projects often complete within a few days after approvals and material readiness. Commercial timelines vary by size.', 4),
('Do you provide mounting structures?', 'Yes. We supply reliable mounting solutions suited to your roof type for long-term safety and warranty compliance.', 5);

INSERT INTO static_pages (slug, title, body) VALUES
('terms', 'Terms & Conditions', '<p>These terms govern use of this website and engagement of Bhatt Enterprises for solar products and services. Project-specific agreements will be signed before execution.</p><p><strong>Quotations</strong> — Valid for the period stated on the quote. Prices may change with component or policy changes.</p><p><strong>Warranties</strong> — Product warranties are as per manufacturer. Installation workmanship terms are covered in your project contract.</p>'),
('privacy', 'Privacy Policy', '<p>Bhatt Enterprises respects your privacy. Information submitted via contact forms is used only to respond to enquiries and provide requested services.</p><p>We do not sell personal data. Technical logs may be collected for security and analytics as per hosting provider defaults.</p>');

INSERT INTO job_openings (title, slug, location, employment_type, short_description, description, sort_order, is_active) VALUES
('Solar Installation Engineer', 'solar-installation-engineer', 'Bilaspur, Chhattisgarh', 'Full-time', 'Hands-on rooftop solar installation, commissioning, and site coordination.', 'We are looking for an experienced solar installation professional familiar with grid-tied rooftop systems, safety practices, and customer communication. Responsibilities include site execution, quality checks, and coordination with the design team. Apply with your experience and availability.', 1, 1);

INSERT INTO pricing_plans (card_title, price_display, plan_name, cta_url, cta_label, is_featured, cta_yellow, cta_in_wrapper, sort_order, is_active) VALUES
('Solar Service Price', '$80', 'Repairing Plan', 'contact.php', 'Choose Packages', 0, 0, 0, 1, 1),
('Solar Service Price', '$110', 'Emergency Plan', 'contact.php', 'Choose Packages', 1, 1, 1, 2, 1),
('Solar Service Price', '$180', 'Replacement Plan', 'contact.php', 'Choose Packages', 0, 0, 1, 3, 1);

INSERT INTO pricing_features (plan_id, label, icon_style, sort_order) VALUES
(1, 'Maintained Equipment', 'check', 1), (1, '10% Discount on Repairing', 'check', 2), (1, 'Inspection 2 Times Monthly', 'check', 3), (1, 'Unlimited Call In Free', 'check', 4), (1, 'No Charges for checking', 'mark', 5), (1, 'Offer New Coupons', 'mark', 6),
(2, 'Maintained Equipment', 'check', 1), (2, '10% Discount on Repairing', 'check', 2), (2, 'Inspection 2 Times Monthly', 'check', 3), (2, 'Unlimited Call In Free', 'check', 4), (2, 'No Charges for checking', 'mark', 5), (2, 'Offer New Coupons', 'mark', 6),
(3, 'Maintained Equipment', 'check', 1), (3, '10% Discount on Repairing', 'check', 2), (3, 'Inspection 2 Times Monthly', 'check', 3), (3, 'Unlimited Call In Free', 'check', 4), (3, 'No Charges for checking', 'mark', 5), (3, 'Offer New Coupons', 'mark', 6);
