-- schema trimmed (created earlier in previous cell)
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('owner','manager','cashier') NOT NULL DEFAULT 'owner',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  id TINYINT PRIMARY KEY,
  site_title VARCHAR(200) NOT NULL DEFAULT 'PrediHub Merchant',
  vat_enabled TINYINT(1) NOT NULL DEFAULT 1,
  vat_percent DECIMAL(5,2) NOT NULL DEFAULT 15.00,
  always_show_inc_vat TINYINT(1) NOT NULL DEFAULT 1,
  logo_path VARCHAR(255) NULL,
  favicon_path VARCHAR(255) NULL,
  features_json JSON NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO settings (id) VALUES (1);

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(100) NULL,
  name VARCHAR(200) NOT NULL,
  category VARCHAR(120) NULL,
  default_vat_percent DECIMAL(5,2) NOT NULL DEFAULT 15.00,
  default_fees_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  desired_profit_percent DECIMAL(6,2) NOT NULL DEFAULT 20.00,
  min_profit_per_unit DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  low_stock_threshold INT NOT NULL DEFAULT 0,
  always_show_inc_vat TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  stock_qty INT NOT NULL DEFAULT 0,
  stock_avg_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchases (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty INT NOT NULL,
  unit_cost DECIMAL(14,4) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  total_ex DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  vat_amount DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  total_inc DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  fees_amount DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  discount_amount DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sale_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty INT NOT NULL,
  unit_price_ex DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  unit_price_inc DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  cost_unit DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  profit_unit DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(sale_id), INDEX(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;