We Fix It
Right. Guaranteed.

-- =============================================
-- REPAIR PLANET — MySQL Database Schema
-- repairplanet.ca
-- =============================================

CREATE DATABASE IF NOT EXISTS repairplanet
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE repairplanet;

-- Site Settings
CREATE TABLE 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
);

-- Contact Information
CREATE TABLE contact_info (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  business_name VARCHAR(150),
  phone      VARCHAR(50),
  email      VARCHAR(150),
  address    VARCHAR(255),
  city       VARCHAR(100),
  province   VARCHAR(50),
  postal_code VARCHAR(20),
  website    VARCHAR(255),
  hours_json TEXT COMMENT 'JSON: [{day, open, close}]',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Categories (shared by products & services)
CREATE TABLE categories (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  type       ENUM('product','service','both') DEFAULT 'both',
  icon       VARCHAR(10),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products
CREATE TABLE products (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  sku          VARCHAR(60) NOT NULL UNIQUE,
  name         VARCHAR(200) NOT NULL,
  description  TEXT,
  category_id  INT,
  cost_price   DECIMAL(10,2),
  sell_price   DECIMAL(10,2) NOT NULL,
  image_url    VARCHAR(500),
  weight_grams INT,
  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 categories(id) ON DELETE SET NULL
);

-- Inventory
CREATE TABLE inventory (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  product_id      INT NOT NULL UNIQUE,
  qty_on_hand     INT DEFAULT 0,
  qty_reserved    INT DEFAULT 0,
  reorder_point   INT DEFAULT 5,
  reorder_qty     INT DEFAULT 20,
  location        VARCHAR(100) COMMENT 'Shelf / bin location',
  last_restocked  DATE,
  updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Inventory Adjustments Log
CREATE TABLE inventory_log (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  product_id   INT NOT NULL,
  change_qty   INT NOT NULL COMMENT 'Positive = add, negative = remove',
  reason       VARCHAR(255),
  performed_by VARCHAR(100),
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Services
CREATE TABLE services (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(200) NOT NULL,
  description    TEXT,
  category_id    INT,
  duration_mins  INT COMMENT 'Estimated repair time',
  base_price     DECIMAL(10,2) NOT NULL,
  price_max      DECIMAL(10,2) COMMENT 'Upper range if variable',
  icon           VARCHAR(10),
  is_active      TINYINT(1) DEFAULT 1,
  sort_order     INT DEFAULT 0,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Pages (CMS)
CREATE TABLE pages (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  slug       VARCHAR(100) NOT NULL UNIQUE,
  title      VARCHAR(200),
  content    LONGTEXT COMMENT 'JSON or HTML content blocks',
  meta_desc  VARCHAR(300),
  is_active  TINYINT(1) DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Media / Uploads
CREATE TABLE media (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  filename    VARCHAR(255),
  file_path   VARCHAR(500),
  file_type   VARCHAR(50),
  file_size   INT,
  alt_text    VARCHAR(255),
  uploaded_by VARCHAR(100),
  is_logo     TINYINT(1) DEFAULT 0,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Admin Users
CREATE TABLE admin_users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(60) NOT NULL UNIQUE,
  email         VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL COMMENT 'bcrypt hash',
  full_name     VARCHAR(150),
  role          ENUM('super_admin','admin','editor') DEFAULT 'editor',
  last_login    TIMESTAMP,
  is_active     TINYINT(1) DEFAULT 1,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- SEED DATA
-- =============================================
INSERT INTO categories (name, type, icon) VALUES
  ('Smartphones', 'both', '📱'),
  ('Laptops', 'both', '💻'),
  ('Tablets', 'both', '📟'),
  ('Accessories', 'product', '🎧'),
  ('Screen Repair', 'service', '🔧'),
  ('Battery Service', 'service', '🔋');

INSERT INTO contact_info
  (business_name, phone, email, address, city, province, postal_code, website)
VALUES
  ('Repair Planet','(403) 555-0147','[email protected]',
   '123 Repair St NE','Calgary','AB','T2E 0B3','https://repairplanet.ca');

INSERT INTO admin_users (username, email, password_hash, full_name, role)
VALUES ('admin','[email protected]',
  '$2b$12$examplehashgoeshere','Site Administrator','super_admin');

-- Indexes
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_inventory_product ON inventory(product_id);
CREATE INDEX idx_services_category ON services(category_id);