-- MTS Trader Database Schema
-- Run this file to create all tables

CREATE DATABASE IF NOT EXISTS umairtra_mtstraderw;
USE umairtra_mtstraderw;

CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS slides (
  id INT AUTO_INCREMENT PRIMARY KEY,
  media_type ENUM('image','video') DEFAULT 'image',
  media_url VARCHAR(500),
  heading VARCHAR(255),
  subheading TEXT,
  btn1_text VARCHAR(100),
  btn1_link VARCHAR(255),
  btn2_text VARCHAR(100),
  btn2_link VARCHAR(255),
  sort_order INT DEFAULT 0,
  is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS stats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(50),
  label VARCHAR(100),
  icon VARCHAR(100),
  sort_order INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS brands (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  logo_url VARCHAR(500),
  sort_order INT DEFAULT 0,
  is_active TINYINT DEFAULT 1
);

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  category_id INT,
  company_id INT,
  description TEXT,
  image_url VARCHAR(500),
  price VARCHAR(100),
  is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  location VARCHAR(255),
  description TEXT,
  image_url VARCHAR(500),
  project_date DATE,
  is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS testimonials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(150),
  photo_url VARCHAR(500),
  feedback TEXT,
  stars TINYINT DEFAULT 5,
  is_active TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS team_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150),
  designation VARCHAR(150),
  description TEXT,
  photo_url VARCHAR(500),
  sort_order INT DEFAULT 0,
  is_active TINYINT DEFAULT 1
);

CREATE TABLE IF NOT EXISTS faqs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question TEXT,
  answer TEXT,
  category VARCHAR(100),
  sort_order INT DEFAULT 0,
  is_active TINYINT DEFAULT 1
);

CREATE TABLE IF NOT EXISTS blog_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  slug VARCHAR(255) UNIQUE,
  content LONGTEXT,
  featured_image VARCHAR(500),
  tags VARCHAR(255),
  is_published TINYINT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150),
  email VARCHAR(150),
  phone VARCHAR(50),
  subject VARCHAR(255),
  message TEXT,
  is_read TINYINT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(150) UNIQUE,
  subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS site_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) UNIQUE,
  setting_value TEXT
);

CREATE TABLE IF NOT EXISTS about_content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tab_key VARCHAR(50) UNIQUE,
  title VARCHAR(255),
  content TEXT
);

CREATE TABLE IF NOT EXISTS services_content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  description TEXT,
  icon VARCHAR(100),
  sort_order INT DEFAULT 0,
  is_active TINYINT DEFAULT 1
);

-- Default site settings
INSERT IGNORE INTO site_settings (setting_key, setting_value) VALUES
('site_title', 'MTS Trader'),
('tagline', 'Pakistan''s Leading Solar Solutions Provider'),
('phone', '03004152648'),
('email', 'info@mtstrader.com'),
('address', 'LW-22 Ajwa Jawad Centre, The Mall Road, Lahore'),
('facebook', ''),
('instagram', ''),
('youtube', ''),
('linkedin', ''),
('whatsapp', '923004152648'),
('logo_url', '/uploads/logo.png'),
('working_hours', 'Mon-Sat: 9:00 AM - 6:00 PM');

-- Default stats
INSERT IGNORE INTO stats (value, label, icon, sort_order) VALUES
('8', 'Years of Limitless Power', 'fa-bolt', 1),
('380', 'MW Annual Production', 'fa-solar-panel', 2),
('30', 'Years Solar Panel Warranty', 'fa-shield-alt', 3),
('1000', 'Sales Monthly', 'fa-chart-line', 4),
('4831', 'KTonnes Carbon Reduction', 'fa-leaf', 5),
('1000', 'Sites Covered', 'fa-map-marker-alt', 6),
('100', 'Happy Clients', 'fa-smile', 7),
('571', 'Million Customer Savings', 'fa-piggy-bank', 8);

-- Default about content
INSERT IGNORE INTO about_content (tab_key, title, content) VALUES
('who_we_are', 'Who We Are', 'At MTS Trader, we focus on trading premium solar products, including Tier-1 panels, inverters, batteries, and accessories. We also provide professional solar installation for homes, businesses, and industries, delivering efficient, reliable, and cost-saving energy solutions with long-term performance.'),
('our_mission', 'Our Mission', 'Our mission is to make clean, renewable solar energy accessible and affordable for every household and business in Pakistan. We are committed to delivering the highest quality solar solutions that reduce energy costs while contributing to a greener future.'),
('our_goal', 'Our Goal', 'Our goal is to become Pakistan''s most trusted solar energy partner by providing top-tier products, exceptional installation services, and unmatched after-sale support. We aim to power over 10,000 homes and businesses with clean energy by 2030.');

-- Default services
INSERT IGNORE INTO services_content (title, description, icon, sort_order) VALUES
('Solar Equipment Trading', 'Supplying high quality solar panels, inverters, cables, VFDs, batteries, and breakers from top global brands.', 'fa-exchange-alt', 1),
('Elevated Structures', 'Strong painted girder MS iron structures, built for durability and maximizing rooftop solar space.', 'fa-building', 2),
('Net Billing Solutions', 'WAPDA-approved net billing services to help reduce your electricity costs and earn from excess generation.', 'fa-file-invoice-dollar', 3),
('Solar Financing / Installment Plans', 'We are partnered with all authorized banks, offering easy installments through secure banking channels.', 'fa-hand-holding-usd', 4);

-- Default slides
INSERT IGNORE INTO slides (media_type, media_url, heading, subheading, btn1_text, btn1_link, btn2_text, btn2_link, sort_order, is_active) VALUES
('image', '/uploads/slides/slide1.jpg', 'Powering Pakistan with Clean Energy', 'Premium solar solutions for homes, businesses, and industries', 'Get Quote', '/calculator', 'Our Products', '/products', 1, 1),
('image', '/uploads/slides/slide2.jpg', 'Professional Solar Installation', 'Expert installation services with 30-year warranty support', 'Contact Us', '/contact', 'View Projects', '/projects', 2, 1),
('image', '/uploads/slides/slide3.jpg', 'Premium Solar Equipment Trading', 'Tier-1 panels, inverters, batteries and accessories at best prices', 'Browse Products', '/products', 'Call Now', 'tel:+923004152648', 3, 1);