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

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(254) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  password_hash CHAR(128) NOT NULL,
  password_salt CHAR(32) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'employee',
  must_change_password TINYINT(1) NOT NULL DEFAULT 0,
  email_verified_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pending_registrations (
  email VARCHAR(254) PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  password_hash CHAR(128) NOT NULL,
  password_salt CHAR(32) NOT NULL,
  code_hash CHAR(64) NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS password_reset_requests (
  email VARCHAR(254) PRIMARY KEY,
  code_hash CHAR(64) NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS auth_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_auth_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_auth_sessions_expiry (expires_at)
);

CREATE TABLE IF NOT EXISTS auth_guard (
  id TINYINT UNSIGNED PRIMARY KEY
);

INSERT IGNORE INTO auth_guard (id) VALUES (1);

CREATE TABLE IF NOT EXISTS app_migrations (
  migration_key VARCHAR(100) PRIMARY KEY,
  applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  design_code VARCHAR(100) NOT NULL,
  design_type VARCHAR(150) NOT NULL,
  selling_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE suppliers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL UNIQUE,
  phone VARCHAR(50) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE store_receipts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  receipt_date DATE NOT NULL,
  invoice_number VARCHAR(100) NOT NULL,
  supplier_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE store_receipt_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  receipt_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  landing_price DECIMAL(12,2) NOT NULL,
  selling_price DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (receipt_id) REFERENCES store_receipts(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE dispatches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  dispatch_date DATE NOT NULL,
  source_shop VARCHAR(150) NOT NULL DEFAULT 'Delivarance Church Store',
  destination VARCHAR(150) NOT NULL,
  transfer_type ENUM('dispatch', 'return', 'transfer') NOT NULL DEFAULT 'dispatch',
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_dispatches_destination (destination),
  INDEX idx_dispatches_source (source_shop)
);

CREATE TABLE dispatch_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  dispatch_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  FOREIGN KEY (dispatch_id) REFERENCES dispatches(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE shop_stock (
  product_id BIGINT UNSIGNED NOT NULL,
  shop_name VARCHAR(150) NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id, shop_name),
  INDEX idx_shop_stock_name (shop_name),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE sales (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_date DATE NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  shop_name VARCHAR(150) NOT NULL DEFAULT 'Delivarance Church Store',
  payment_method ENUM('Cash','Paybill','Card','Other') NOT NULL DEFAULT 'Cash',
  customer_reference VARCHAR(150) NULL,
  CONSTRAINT fk_sales_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sale_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (sale_id) REFERENCES sales(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE INDEX idx_store_receipts_date ON store_receipts(receipt_date);
CREATE INDEX idx_dispatches_date ON dispatches(dispatch_date);
CREATE INDEX idx_sales_date ON sales(sale_date);
CREATE INDEX idx_sales_user_date ON sales(user_id, sale_date);
CREATE INDEX idx_sales_shop_date ON sales(shop_name, sale_date);
CREATE INDEX idx_products_code ON products(design_code);