-- Import this in phpMyAdmin while the cPanel database is selected.
-- RimBoo inventory schema for local XAMPP MySQL.
-- Database name matches MYSQL_DATABASE in .env.local.

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  user_level VARCHAR(32) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stores (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  location VARCHAR(255) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id INT UNSIGNED NOT NULL,
  name VARCHAR(200) NOT NULL,
  category VARCHAR(100) NOT NULL,
  qty INT NOT NULL DEFAULT 0,
  cost_price DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  selling_price DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  reorder_level INT NOT NULL DEFAULT 0,
  barcode VARCHAR(64) NULL,
  last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_products_store_id (store_id),
  KEY idx_products_barcode (barcode),
  CONSTRAINT fk_products_store
    FOREIGN KEY (store_id) REFERENCES stores (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  address VARCHAR(255) NULL,
  phone VARCHAR(32) NULL,
  initial_balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  wallet_balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  debt_balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  credit_limit DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  narration TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_customers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS suppliers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(32) NULL,
  address VARCHAR(255) NULL,
  wallet_balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  opening_balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  debt_balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_suppliers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_no VARCHAR(32) NOT NULL,
  customer_id INT UNSIGNED NULL,
  store_id INT UNSIGNED NULL,
  cashier_name VARCHAR(150) NOT NULL,
  total_amount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  paid_amount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  cash_amount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  bank_amount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  payment_type VARCHAR(32) NOT NULL,
  bank_name VARCHAR(100) NULL,
  status VARCHAR(32) NOT NULL,
  waybill_no VARCHAR(64) NULL,
  truck_plate VARCHAR(32) NULL,
  driver_name VARCHAR(150) NULL,
  driver_phone VARCHAR(32) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sales_invoices_invoice_no (invoice_no),
  KEY idx_sales_invoices_customer_id (customer_id),
  KEY idx_sales_invoices_store_id (store_id),
  KEY idx_sales_invoices_created_at (created_at),
  CONSTRAINT fk_sales_invoices_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty INT NOT NULL,
  price DECIMAL(14, 2) NOT NULL,
  discount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  amount DECIMAL(14, 2) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_sales_items_invoice_id (invoice_id),
  KEY idx_sales_items_product_id (product_id),
  CONSTRAINT fk_sales_items_invoice
    FOREIGN KEY (invoice_id) REFERENCES sales_invoices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_sales_items_product
    FOREIGN KEY (product_id) REFERENCES products (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchases (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  supplier_id INT UNSIGNED NOT NULL,
  cashier_name VARCHAR(150) NULL,
  total_amount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  paid_amount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  payment_type VARCHAR(32) NOT NULL,
  bank_name VARCHAR(100) NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'Received',
  waybill_no VARCHAR(64) NULL,
  driver_name VARCHAR(150) NULL,
  driver_phone VARCHAR(32) NULL,
  truck_plate VARCHAR(32) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_purchases_supplier_id (supplier_id),
  KEY idx_purchases_created_at (created_at),
  CONSTRAINT fk_purchases_supplier
    FOREIGN KEY (supplier_id) REFERENCES suppliers (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchase_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty INT NOT NULL,
  cost_price DECIMAL(14, 2) NOT NULL,
  amount DECIMAL(14, 2) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_purchase_items_purchase_id (purchase_id),
  KEY idx_purchase_items_product_id (product_id),
  CONSTRAINT fk_purchase_items_purchase
    FOREIGN KEY (purchase_id) REFERENCES purchases (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_purchase_items_product
    FOREIGN KEY (product_id) REFERENCES products (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id INT UNSIGNED NULL,
  `date` DATE NOT NULL,
  collector VARCHAR(150) NOT NULL,
  details TEXT NULL,
  amount DECIMAL(14, 2) NOT NULL,
  cashier VARCHAR(150) NOT NULL,
  approver VARCHAR(150) NULL,
  payment_type VARCHAR(32) NOT NULL,
  banker VARCHAR(150) NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'Active',
  revoked_by VARCHAR(150) NULL,
  PRIMARY KEY (id),
  KEY idx_expenses_store_id (store_id),
  KEY idx_expenses_date (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS register_closes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  business_date DATE NOT NULL,
  store_id INT UNSIGNED NULL,
  closed_by VARCHAR(150) NOT NULL,
  total_sales DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  previous_cash_balance DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  cash_received DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  bank_received DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  cash_expenses DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  bank_expenses DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  debts_allowed DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  cash_with_cashier DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
  closed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_register_closes_date_store (business_date, store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deposits (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id INT UNSIGNED NULL,
  entity_type ENUM('customer', 'supplier') NOT NULL,
  entity_id INT UNSIGNED NOT NULL,
  movement ENUM('deposit', 'refund', 'settlement') NOT NULL DEFAULT 'deposit',
  amount DECIMAL(14, 2) NOT NULL,
  payment_type VARCHAR(32) NOT NULL,
  bank VARCHAR(100) NULL,
  cashier VARCHAR(150) NOT NULL,
  approver VARCHAR(150) NULL,
  details TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_deposits_store_id (store_id),
  KEY idx_deposits_entity (entity_type, entity_id),
  KEY idx_deposits_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_logs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id INT UNSIGNED NOT NULL,
  purchase_id INT UNSIGNED NULL,
  added_qty INT NOT NULL,
  waybill_no VARCHAR(64) NOT NULL,
  driver_name VARCHAR(150) NOT NULL,
  driver_phone VARCHAR(32) NOT NULL,
  truck_plate VARCHAR(32) NOT NULL,
  logged_on DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_stock_logs_product_id (product_id),
  KEY idx_stock_logs_logged_on (logged_on),
  KEY idx_stock_logs_purchase_id (purchase_id),
  CONSTRAINT fk_stock_logs_product
    FOREIGN KEY (product_id) REFERENCES products (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_stock_logs_purchase
    FOREIGN KEY (purchase_id) REFERENCES purchases (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_audits (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id INT UNSIGNED NOT NULL,
  store_id INT UNSIGNED NOT NULL,
  book_qty INT NOT NULL,
  counted_qty INT NOT NULL,
  cashier_name VARCHAR(150) NOT NULL,
  note VARCHAR(255) NULL,
  audited_on DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_stock_audits_store_id (store_id),
  KEY idx_stock_audits_product_id (product_id),
  CONSTRAINT fk_stock_audits_product
    FOREIGN KEY (product_id) REFERENCES products (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_settings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  key_name VARCHAR(100) NOT NULL,
  `value` TEXT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_system_settings_key_name (key_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;