-- Nirviq IT Ltd — improved schema (import into your MySQL database)
-- Create/select database first, e.g. motiurc3_nirviq26

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  category VARCHAR(120) NOT NULL DEFAULT 'General',
  brand VARCHAR(120) NOT NULL DEFAULT '',
  model VARCHAR(120) NOT NULL DEFAULT '',
  short_description TEXT NOT NULL,
  price_type VARCHAR(80) NOT NULL DEFAULT 'Call for Price',
  availability VARCHAR(80) NOT NULL DEFAULT 'Available on Request',
  image_url VARCHAR(500) NOT NULL DEFAULT '',
  specifications TEXT NULL,
  featured TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('draft','published') NOT NULL DEFAULT 'published',
  is_demo TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_products_category_status (category, status),
  FULLTEXT KEY ft_products_search (name, model, category, short_description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leads (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  company VARCHAR(180) NOT NULL DEFAULT '',
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(60) NOT NULL DEFAULT '',
  country VARCHAR(100) NOT NULL DEFAULT '',
  project_name VARCHAR(180) NOT NULL DEFAULT '',
  project_location VARCHAR(180) NOT NULL DEFAULT '',
  product_id BIGINT UNSIGNED NULL,
  quantity INT UNSIGNED NULL,
  project_stage VARCHAR(80) NOT NULL DEFAULT '',
  source VARCHAR(80) NOT NULL DEFAULT 'Website',
  message TEXT NOT NULL,
  notes TEXT NULL,
  status ENUM('New','Contacted','Qualified','Quotation','Negotiation','Won','Lost','Follow-up') NOT NULL DEFAULT 'New',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_leads_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
  INDEX idx_leads_status_created (status, created_at),
  INDEX idx_leads_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo products (mark is_demo=0 only after you verify real data)
INSERT IGNORE INTO products (name, slug, category, brand, model, short_description, price_type, availability, image_url, specifications, featured, is_demo) VALUES
('Standby Generator', 'demo-generator', 'Generator', '', 'Demo model', 'Demonstration listing for backup power equipment. Confirm specifications with the manufacturer.', 'Call for Price', 'Available on Request', 'assets/demo-generator.jpg', 'Specification available on request.', 1, 1),
('Modular UPS 200 kVA', 'modular-ups-200kva', 'UPS & Batteries', '', 'Demo model', 'Demonstration listing for modular uninterruptible power systems.', 'Call for Price', 'Available on Request', 'assets/demo-ups.jpg', 'Specification available on request.', 1, 1),
('Precision Cooling Unit', 'precision-cooling-unit', 'Precision Cooling', '', 'Demo model', 'Demonstration listing for precision air conditioning for IT environments.', 'Call for Price', 'Available on Request', 'assets/demo-pac.jpg', 'Specification available on request.', 1, 1),
('Comfort Air Conditioning Unit', 'cac-cooling-unit', 'PAC', '', 'Demo model', 'Demonstration listing for comfort cooling equipment.', 'Call for Price', 'Available on Request', 'assets/demo-cac.jpg', 'Specification available on request.', 0, 1),
('Addressable Fire Alarm Panel', 'addressable-fire-alarm-panel', 'Fire Alarm', '', 'Demo model', 'Demonstration listing for addressable fire detection systems.', 'Call for Price', 'Available on Request', 'assets/demo-fire.jpg', 'Specification available on request.', 1, 1),
('Aspirating Smoke Detection', 'aspirating-smoke-detection', 'VESDA', '', 'Demo model', 'Demonstration listing for early warning smoke detection.', 'Call for Price', 'Available on Request', 'assets/demo-vesda.jpg', 'Specification available on request.', 0, 1),
('Data Center Infrastructure Management System', 'dcim-system', 'DCIM / BMS / EMS', '', 'Demo model', 'Demonstration listing for infrastructure monitoring and management.', 'Project Based', 'Available on Request', 'assets/demo-dcim.jpg', 'Specification available on request.', 1, 1),
('Energy Management System', 'energy-management-system', 'EMS', '', 'Demo model', 'Demonstration listing for energy management software.', 'Project Based', 'Available on Request', 'assets/demo-ems.jpg', 'Specification available on request.', 0, 1),
('Raised Floor System', 'raised-floor-system', 'Raised Floor', '', 'Demo model', 'Demonstration listing for raised floor infrastructure.', 'Estimated Range', 'Available on Request', 'assets/demo-raised-floor.jpg', 'Specification available on request.', 1, 1),
('Structured Cabling & Connectivity', 'structured-cabling', 'Structured Cabling', '', 'Demo model', 'Demonstration listing for structured cabling and connectivity products.', 'Call for Price', 'Available on Request', 'assets/demo-cabling.jpg', 'Specification available on request.', 0, 1),
('42U Server Rack', '42u-server-rack', 'Racks & Cabinets', '', 'Demo model', 'Demonstration listing for server and network rack infrastructure.', 'Estimated Range', 'Available on Request', '', 'Specification available on request.', 0, 1),
('Smart Rack PDU', 'smart-rack-pdu', 'Electrical Power', '', 'Demo model', 'Demonstration listing for rack power distribution.', 'Estimated Range', 'Available on Request', '', 'Specification available on request.', 0, 1);

UPDATE products SET image_url = 'assets/demo-generator.jpg' WHERE slug = 'demo-generator';
UPDATE products SET image_url = 'assets/demo-ups.jpg' WHERE slug = 'modular-ups-200kva';
UPDATE products SET image_url = 'assets/demo-pac.jpg' WHERE slug = 'precision-cooling-unit';
UPDATE products SET image_url = 'assets/demo-cac.jpg' WHERE slug = 'cac-cooling-unit';
UPDATE products SET image_url = 'assets/demo-fire.jpg' WHERE slug = 'addressable-fire-alarm-panel';
UPDATE products SET image_url = 'assets/demo-vesda.jpg' WHERE slug = 'aspirating-smoke-detection';
UPDATE products SET image_url = 'assets/demo-dcim.jpg' WHERE slug = 'dcim-system';
UPDATE products SET image_url = 'assets/demo-ems.jpg' WHERE slug = 'energy-management-system';
UPDATE products SET image_url = 'assets/demo-raised-floor.jpg' WHERE slug = 'raised-floor-system';
UPDATE products SET image_url = 'assets/demo-cabling.jpg' WHERE slug = 'structured-cabling';