/* ============================================================
   Bicycle Rental + Sales + Maintenance POS (Cloud, Multi-Branch)
   MySQL 8+ | InnoDB | UTF8MB4 | Bilingual fields | RBAC
   Notes:
   - Use app-level transactions when creating invoices/rentals/maintenance.
   - Keep bicycle assets tracked per unit (serial/barcode/status).
   - Inventory is ledger-based (stock_movements) for accuracy & audits.
   ============================================================ */

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

SET sql_safe_updates = 0;

-- =========================
-- 0) Shared / Lookup
-- =========================
CREATE TABLE branches (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(30) NOT NULL UNIQUE,
  name_en VARCHAR(150) NOT NULL,
  name_ar VARCHAR(150) NOT NULL,
  phone VARCHAR(30) NULL,
  address_en VARCHAR(255) NULL,
  address_ar VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE currencies (
  code CHAR(3) PRIMARY KEY,              -- e.g. AED, SAR, EGP
  name_en VARCHAR(50) NOT NULL,
  name_ar VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO currencies(code, name_en, name_ar)
VALUES ('AED','UAE Dirham','درهم إماراتي'),('SAR','Saudi Riyal','ريال سعودي'),('EGP','Egyptian Pound','جنيه مصري');

-- =========================
-- 1) Users / RBAC
-- =========================
CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NULL,
  full_name VARCHAR(150) NOT NULL,
  username VARCHAR(80) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL,
  email VARCHAR(120) NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_branch
    FOREIGN KEY (branch_id) REFERENCES branches(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE roles (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL UNIQUE, -- Admin, Cashier, Workshop, Manager...
  description VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(100) NOT NULL UNIQUE, -- e.g. invoice.create, rental.close
  description VARCHAR(255) NULL
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- 2) Customers + Loyalty
-- =========================
CREATE TABLE customers (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(120) NULL,
  national_id VARCHAR(50) NULL,
  address_en VARCHAR(255) NULL,
  address_ar VARCHAR(255) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE loyalty_rules (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  source ENUM('SALE','RENTAL','MAINTENANCE') NOT NULL,
  points_per_currency DECIMAL(10,4) NOT NULL DEFAULT 0.0000, -- points per 1 currency unit
  fixed_points_per_invoice INT NOT NULL DEFAULT 0,
  min_invoice_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  expiry_days INT NULL, -- NULL => never expires
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE loyalty_wallet (
  customer_id BIGINT UNSIGNED PRIMARY KEY,
  balance_points INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE loyalty_transactions (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  branch_id BIGINT UNSIGNED NOT NULL,
  source ENUM('SALE','RENTAL','MAINTENANCE','ADJUSTMENT','REDEMPTION') NOT NULL,
  ref_table VARCHAR(50) NULL,
  ref_id BIGINT UNSIGNED NULL,
  points INT NOT NULL, -- positive earn, negative redeem
  expiry_date DATE NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  INDEX idx_loy_customer_created (customer_id, created_at),
  INDEX idx_loy_expiry (expiry_date)
) ENGINE=InnoDB;

-- =========================
-- 3) Products / Inventory
-- =========================
CREATE TABLE product_categories (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  parent_id BIGINT UNSIGNED NULL,
  name_en VARCHAR(120) NOT NULL,
  name_ar VARCHAR(120) NOT NULL,
  description TEXT NULL,
  FOREIGN KEY (parent_id) REFERENCES product_categories(id) ON DELETE SET NULL,
  INDEX idx_cat_parent (parent_id)
) ENGINE=InnoDB;

CREATE TABLE brands (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE products (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  type ENUM('SPARE_PART','ACCESSORY','BICYCLE_MODEL','SERVICE') NOT NULL,
  sku VARCHAR(60) NOT NULL UNIQUE,
  barcode VARCHAR(80) NULL,
  name_en VARCHAR(200) NOT NULL,
  name_ar VARCHAR(200) NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  brand_id BIGINT UNSIGNED NULL,
  description TEXT NULL,
  unit_name_en VARCHAR(50) NULL, -- pcs, hour, etc
  unit_name_ar VARCHAR(50) NULL,
  cost_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  sale_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 5.00,
  is_track_stock TINYINT(1) NOT NULL DEFAULT 1, -- services usually 0
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL,
  INDEX idx_products_type (type),
  INDEX idx_products_barcode (barcode),
  INDEX idx_products_category (category_id)
) ENGINE=InnoDB;

CREATE TABLE product_tags (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE product_tag_map (
  product_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, tag_id),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES product_tags(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE stock_balance_cache (
  branch_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty_on_hand DECIMAL(14,3) NOT NULL DEFAULT 0.000,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (branch_id, product_id),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE stock_movements (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  movement_type ENUM('PURCHASE_IN','ADJUSTMENT_IN','ADJUSTMENT_OUT','SALE_OUT','RETURN_IN','MAINTENANCE_OUT','TRANSFER_IN','TRANSFER_OUT') NOT NULL,
  ref_table VARCHAR(50) NULL,
  ref_id BIGINT UNSIGNED NULL,
  qty DECIMAL(14,3) NOT NULL, -- positive for IN, negative for OUT (recommended)
  unit_cost DECIMAL(12,2) NULL,
  note VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_stock_branch_product (branch_id, product_id, created_at),
  INDEX idx_stock_ref (ref_table, ref_id)
) ENGINE=InnoDB;

-- =========================
-- 4) Bicycle Assets (Per-unit tracking)
-- =========================
CREATE TABLE bicycle_models (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL, -- link to products(type=BICYCLE_MODEL)
  frame_size VARCHAR(50) NULL,
  color VARCHAR(50) NULL,
  wheel_size VARCHAR(50) NULL,
  notes TEXT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  UNIQUE KEY uq_bike_model_product (product_id)
) ENGINE=InnoDB;

CREATE TABLE bicycles (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  model_id BIGINT UNSIGNED NOT NULL,
  asset_code VARCHAR(60) NOT NULL UNIQUE,
  serial_number VARCHAR(100) NULL UNIQUE,
  barcode VARCHAR(80) NULL UNIQUE,
  purchase_date DATE NULL,
  purchase_cost DECIMAL(12,2) NULL,
  status ENUM('AVAILABLE','RENTED','UNDER_MAINTENANCE','OUT_OF_SERVICE') NOT NULL DEFAULT 'AVAILABLE',
  odometer_km DECIMAL(12,2) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (model_id) REFERENCES bicycle_models(id) ON DELETE RESTRICT,
  INDEX idx_bike_branch_status (branch_id, status),
  INDEX idx_bike_model (model_id)
) ENGINE=InnoDB;

-- =========================
-- 5) Pricing for Rentals (hour/day/package)
-- =========================
CREATE TABLE rental_packages (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name_en VARCHAR(150) NOT NULL,
  name_ar VARCHAR(150) NOT NULL,
  duration_unit ENUM('HOUR','DAY') NOT NULL,
  duration_value INT NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  late_fee_per_unit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE rental_rates (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  model_id BIGINT UNSIGNED NOT NULL,
  rate_type ENUM('HOURLY','DAILY') NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  late_fee_per_unit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (model_id) REFERENCES bicycle_models(id) ON DELETE CASCADE,
  UNIQUE KEY uq_rate (model_id, rate_type)
) ENGINE=InnoDB;

-- =========================
-- 6) Invoicing (Sales/Rental/Maintenance)
-- =========================
CREATE TABLE invoices (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  invoice_no VARCHAR(40) NOT NULL,
  invoice_type ENUM('SALE','RENTAL','MAINTENANCE') NOT NULL,
  status ENUM('DRAFT','UNPAID','PAID','VOID','REFUNDED','PARTIALLY_REFUNDED') NOT NULL DEFAULT 'UNPAID',
  currency_code CHAR(3) NOT NULL DEFAULT 'AED',
  customer_id BIGINT UNSIGNED NULL,
  cashier_id BIGINT UNSIGNED NULL,
  issue_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note VARCHAR(255) NULL,
  sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  discount_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  vat_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  grand_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_invoice_branch_no (branch_id, invoice_no),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (currency_code) REFERENCES currencies(code) ON DELETE RESTRICT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  FOREIGN KEY (cashier_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_invoice_branch_type_date (branch_id, invoice_type, issue_datetime),
  INDEX idx_invoice_status (status)
) ENGINE=InnoDB;

CREATE TABLE invoice_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  invoice_id BIGINT UNSIGNED NOT NULL,
  line_no INT NOT NULL,
  item_type ENUM('PRODUCT','SERVICE','RENTAL_CHARGE') NOT NULL DEFAULT 'PRODUCT',
  product_id BIGINT UNSIGNED NULL,
  bicycle_id BIGINT UNSIGNED NULL,
  description VARCHAR(255) NULL,
  qty DECIMAL(14,3) NOT NULL DEFAULT 1.000,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 5.00,
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
  FOREIGN KEY (bicycle_id) REFERENCES bicycles(id) ON DELETE SET NULL,
  UNIQUE KEY uq_invoice_line (invoice_id, line_no),
  INDEX idx_inv_items_invoice (invoice_id),
  INDEX idx_inv_items_product (product_id)
) ENGINE=InnoDB;

CREATE TABLE refunds (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  original_invoice_id BIGINT UNSIGNED NOT NULL,
  refund_no VARCHAR(40) NOT NULL,
  status ENUM('DRAFT','APPROVED','PAID','VOID') NOT NULL DEFAULT 'PAID',
  refunded_by BIGINT UNSIGNED NULL,
  refund_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reason VARCHAR(255) NULL,
  refund_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  UNIQUE KEY uq_refund_branch_no (branch_id, refund_no),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (original_invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT,
  FOREIGN KEY (refunded_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_refund_original (original_invoice_id)
) ENGINE=InnoDB;

CREATE TABLE refund_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  refund_id BIGINT UNSIGNED NOT NULL,
  invoice_item_id BIGINT UNSIGNED NOT NULL,
  qty DECIMAL(14,3) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (refund_id) REFERENCES refunds(id) ON DELETE CASCADE,
  FOREIGN KEY (invoice_item_id) REFERENCES invoice_items(id) ON DELETE RESTRICT,
  INDEX idx_refund_items_refund (refund_id)
) ENGINE=InnoDB;

-- =========================
-- 7) Payments (Cash / POS Terminal manual)
-- =========================
CREATE TABLE payments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  invoice_id BIGINT UNSIGNED NOT NULL,
  payment_method ENUM('CASH','POS_TERMINAL') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  pos_reference VARCHAR(80) NULL,
  confirmed_by BIGINT UNSIGNED NULL,
  paid_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_payments_invoice (invoice_id),
  INDEX idx_payments_branch_date (branch_id, paid_datetime)
) ENGINE=InnoDB;

-- =========================
-- 8) Rentals (Contract + Signature)
-- =========================
CREATE TABLE rentals (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  rental_no VARCHAR(40) NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  bicycle_id BIGINT UNSIGNED NOT NULL,
  pricing_mode ENUM('HOURLY','DAILY','PACKAGE') NOT NULL,
  package_id BIGINT UNSIGNED NULL,
  rate_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  late_fee_per_unit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  deposit_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  start_datetime DATETIME NOT NULL,
  expected_return_datetime DATETIME NOT NULL,
  actual_return_datetime DATETIME NULL,
  status ENUM('ACTIVE','RETURNED','LATE','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
  terms_accepted TINYINT(1) NOT NULL DEFAULT 0,
  signature_image_url VARCHAR(255) NULL,
  notes TEXT NULL,
  linked_invoice_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rental_branch_no (branch_id, rental_no),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (bicycle_id) REFERENCES bicycles(id) ON DELETE RESTRICT,
  FOREIGN KEY (package_id) REFERENCES rental_packages(id) ON DELETE SET NULL,
  FOREIGN KEY (linked_invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  INDEX idx_rentals_bike_status (bicycle_id, status),
  INDEX idx_rentals_customer_date (customer_id, start_datetime)
) ENGINE=InnoDB;

-- =========================
-- 9) Maintenance / Workshop
-- =========================
CREATE TABLE maintenance_jobs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  job_no VARCHAR(40) NOT NULL,
  bicycle_id BIGINT UNSIGNED NOT NULL,
  opened_by BIGINT UNSIGNED NULL,
  assigned_to BIGINT UNSIGNED NULL,
  status ENUM('OPEN','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'OPEN',
  problem_description TEXT NULL,
  diagnosis TEXT NULL,
  work_done TEXT NULL,
  labor_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  other_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  linked_invoice_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_job_branch_no (branch_id, job_no),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (bicycle_id) REFERENCES bicycles(id) ON DELETE RESTRICT,
  FOREIGN KEY (opened_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (linked_invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  INDEX idx_jobs_bike_status (bicycle_id, status),
  INDEX idx_jobs_branch_date (branch_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE maintenance_parts (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty DECIMAL(14,3) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  FOREIGN KEY (job_id) REFERENCES maintenance_jobs(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  INDEX idx_job_parts_job (job_id)
) ENGINE=InnoDB;

-- =========================
-- 10) Expenses (for basic finance)
-- =========================
CREATE TABLE expense_categories (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name_en VARCHAR(120) NOT NULL,
  name_ar VARCHAR(120) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE expenses (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  title VARCHAR(150) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  expense_date DATE NOT NULL,
  paid_by_user_id BIGINT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (category_id) REFERENCES expense_categories(id) ON DELETE SET NULL,
  FOREIGN KEY (paid_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_exp_branch_date (branch_id, expense_date)
) ENGINE=InnoDB;

-- =========================
-- 11) Staff sales attribution (for contribution/bonus later)
-- =========================
CREATE TABLE staff_monthly_contribution (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  year SMALLINT NOT NULL,
  month TINYINT NOT NULL,
  sales_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  contribution_percent DECIMAL(6,3) NOT NULL DEFAULT 0.000,
  bonus_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  UNIQUE KEY uq_staff_month (branch_id, user_id, year, month),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- 12) Audit logs (critical actions)
-- =========================
CREATE TABLE audit_logs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(80) NULL,
  entity_id BIGINT UNSIGNED NULL,
  before_json JSON NULL,
  after_json JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_entity (entity, entity_id),
  INDEX idx_audit_user_date (user_id, created_at)
) ENGINE=InnoDB;

-- =========================
-- 13) Optional Accounting (V1 - ready to expand to full double-entry)
-- =========================
CREATE TABLE accounts (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(30) NOT NULL UNIQUE,
  name_en VARCHAR(150) NOT NULL,
  name_ar VARCHAR(150) NOT NULL,
  type ENUM('ASSET','LIABILITY','EQUITY','REVENUE','EXPENSE') NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE journal_entries (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT UNSIGNED NOT NULL,
  entry_no VARCHAR(40) NOT NULL,
  entry_date DATE NOT NULL,
  memo VARCHAR(255) NULL,
  ref_table VARCHAR(50) NULL,
  ref_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_journal_branch_no (branch_id, entry_no),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_journal_ref (ref_table, ref_id)
) ENGINE=InnoDB;

CREATE TABLE journal_lines (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  entry_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NOT NULL,
  line_no INT NOT NULL,
  debit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  credit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  description VARCHAR(255) NULL,
  FOREIGN KEY (entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE RESTRICT,
  UNIQUE KEY uq_journal_line (entry_id, line_no),
  INDEX idx_journal_account (account_id)
) ENGINE=InnoDB;