CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  description TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  grade VARCHAR(60) NULL,
  short_desc VARCHAR(255) NULL,
  description TEXT NULL,
  pack_sizes VARCHAR(255) NULL,
  image VARCHAR(80) NULL,
  spec_sheet VARCHAR(80) NULL,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_specs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  label VARCHAR(100) NOT NULL,
  value VARCHAR(255) NOT NULL,
  sort INT NOT NULL DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE enquiries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NULL,
  name VARCHAR(120) NOT NULL,
  company VARCHAR(160) NULL,
  email VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  message TEXT NOT NULL,
  status ENUM('new','handled') NOT NULL DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO categories (name, slug, description, sort_order) VALUES
('Engine oils','engine-oils','Petrol, diesel and heavy-duty engine oils for vehicles and machinery.',1),
('Industrial lubricants','industrial-lubricants','Hydraulic, gear and compressor oils for plant and equipment.',2),
('Greases','greases','Multi-purpose and specialty greases for bearings, joints and chassis.',3),
('Coolants','coolants','Long-life coolants and antifreeze for engines and industrial systems.',4);