-- Caliphate Bites - MySQL Schema
-- Engine: InnoDB, Charset: utf8mb4

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

-- ===== USERS / AUTH =====
CREATE TABLE users (
  id CHAR(36) PRIMARY KEY,
  email VARCHAR(190) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(190),
  phone VARCHAR(40),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE user_roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  role ENUM('admin','cashier','customer') NOT NULL,
  UNIQUE KEY uniq_user_role (user_id, role),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE customers (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NULL,
  email VARCHAR(190),
  full_name VARCHAR(190),
  phone VARCHAR(40),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE customer_addresses (
  id CHAR(36) PRIMARY KEY,
  customer_id CHAR(36) NOT NULL,
  label VARCHAR(80),
  address_line TEXT NOT NULL,
  city VARCHAR(80),
  state VARCHAR(80),
  is_default BOOLEAN DEFAULT FALSE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===== CATALOG =====
CREATE TABLE categories (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  icon VARCHAR(80),
  display_order INT DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE products (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  description TEXT,
  price DECIMAL(12,2) NOT NULL,
  cost DECIMAL(12,2) DEFAULT 0,
  quantity INT DEFAULT 0,
  low_stock_threshold INT DEFAULT 5,
  category_id CHAR(36),
  image_url VARCHAR(500),
  is_active BOOLEAN DEFAULT TRUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===== POS SALES =====
CREATE TABLE pos_sales (
  id CHAR(36) PRIMARY KEY,
  receipt_number VARCHAR(60) UNIQUE NOT NULL,
  cashier_id CHAR(36),
  subtotal DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) NOT NULL,
  payment_method ENUM('cash','transfer','card','pos') DEFAULT 'cash',
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cashier_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE pos_sale_items (
  id CHAR(36) PRIMARY KEY,
  sale_id CHAR(36) NOT NULL,
  product_id CHAR(36),
  product_name VARCHAR(190) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  total_price DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (sale_id) REFERENCES pos_sales(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===== ONLINE ORDERS =====
CREATE TABLE online_orders (
  id CHAR(36) PRIMARY KEY,
  order_number VARCHAR(60) UNIQUE NOT NULL,
  customer_id CHAR(36) NULL,
  guest_email VARCHAR(190),
  guest_name VARCHAR(190),
  guest_phone VARCHAR(40),
  delivery_address TEXT NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL,
  discount_amount DECIMAL(12,2) DEFAULT 0,
  delivery_fee DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) NOT NULL,
  payment_method ENUM('cash_on_delivery','bank_transfer') DEFAULT 'cash_on_delivery',
  status ENUM('pending','confirmed','preparing','out_for_delivery','delivered','cancelled') DEFAULT 'pending',
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE online_order_items (
  id CHAR(36) PRIMARY KEY,
  order_id CHAR(36) NOT NULL,
  product_id CHAR(36),
  product_name VARCHAR(190) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  total_price DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES online_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE order_status_history (
  id CHAR(36) PRIMARY KEY,
  order_id CHAR(36) NOT NULL,
  status VARCHAR(40) NOT NULL,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES online_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===== PROMO =====
CREATE TABLE promo_codes (
  id CHAR(36) PRIMARY KEY,
  code VARCHAR(60) UNIQUE NOT NULL,
  discount_type ENUM('percentage','fixed') DEFAULT 'percentage',
  discount_value DECIMAL(12,2) NOT NULL,
  min_order_amount DECIMAL(12,2) DEFAULT 0,
  max_uses INT NULL,
  used_count INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  starts_at DATETIME NULL,
  expires_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE promo_banners (
  id CHAR(36) PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  subtitle VARCHAR(255),
  image_url VARCHAR(500),
  link_url VARCHAR(500),
  is_active BOOLEAN DEFAULT TRUE,
  display_order INT DEFAULT 0,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ===== REVIEWS =====
CREATE TABLE product_reviews (
  id CHAR(36) PRIMARY KEY,
  product_id CHAR(36) NOT NULL,
  customer_id CHAR(36),
  customer_name VARCHAR(190),
  rating TINYINT NOT NULL,
  comment TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===== EXPENSES =====
CREATE TABLE expenses (
  id CHAR(36) PRIMARY KEY,
  description VARCHAR(255) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  category VARCHAR(80),
  created_by CHAR(36),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===== LIVE CHAT =====
CREATE TABLE chat_sessions (
  id CHAR(36) PRIMARY KEY,
  customer_id CHAR(36),
  guest_name VARCHAR(190),
  device_id VARCHAR(120),
  status ENUM('open','closed') DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE chat_messages (
  id CHAR(36) PRIMARY KEY,
  session_id CHAR(36) NOT NULL,
  sender ENUM('customer','admin') NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===== NOTIFICATIONS =====
CREATE TABLE customer_notifications (
  id CHAR(36) PRIMARY KEY,
  customer_id CHAR(36),
  order_id CHAR(36),
  title VARCHAR(190) NOT NULL,
  message TEXT,
  is_read BOOLEAN DEFAULT FALSE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ===== SETTINGS =====
CREATE TABLE settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  setting_value TEXT
) ENGINE=InnoDB;

-- ===== SEED =====
INSERT INTO settings (setting_key, setting_value) VALUES
  ('business_name','Caliphate Bites'),
  ('currency','NGN'),
  ('currency_symbol','₦'),
  ('phone','+234 000 000 0000'),
  ('whatsapp','+234 000 000 0000'),
  ('address','Sokoto, Nigeria'),
  ('delivery_fee','0');

INSERT INTO categories (id, name, icon, display_order) VALUES
  (UUID(),'Shawarma','fa-bread-slice',1),
  (UUID(),'Grills','fa-drumstick-bite',2),
  (UUID(),'Small Chops','fa-pizza-slice',3),
  (UUID(),'Drinks','fa-mug-hot',4);

-- Default admin: email=admin@caliphate.com password=admin123
INSERT INTO users (id, email, password_hash, full_name)
  VALUES (UUID(), 'admin@caliphate.com', '$2y$10$OPlLUBuh6bbnwY1z1Zf7lebr2P2TksqQlyoRSAFOJDx7sHF0Zb1KO', 'Administrator');
INSERT INTO user_roles (user_id, role) SELECT id, 'admin' FROM users WHERE email='admin@caliphate.com';