-- POS26 schema — utf8mb4 / InnoDB
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS register_shift_handovers;
DROP TABLE IF EXISTS register_shift_balances;
DROP TABLE IF EXISTS register_shifts;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS sale_return_items;
DROP TABLE IF EXISTS sale_returns;
DROP TABLE IF EXISTS sale_payments;
DROP TABLE IF EXISTS sale_items;
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS purchase_payments;
DROP TABLE IF EXISTS purchase_items;
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS product_serials;
DROP TABLE IF EXISTS product_branch_stock;
DROP TABLE IF EXISTS product_variations;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS expense_entries;
DROP TABLE IF EXISTS expense_categories;
DROP TABLE IF EXISTS stock_adjustment_lines;
DROP TABLE IF EXISTS stock_adjustments;
DROP TABLE IF EXISTS stock_transfer_lines;
DROP TABLE IF EXISTS stock_transfers;
DROP TABLE IF EXISTS reward_transactions;
DROP TABLE IF EXISTS customer_reward_balances;
DROP TABLE IF EXISTS reward_rules;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS customer_groups;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS payment_methods;
DROP TABLE IF EXISTS brands;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS variation_values;
DROP TABLE IF EXISTS variation_types;
DROP TABLE IF EXISTS units;
DROP TABLE IF EXISTS user_branches;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS shop_types;
DROP TABLE IF EXISTS branches;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS restaurant_tables;
DROP TABLE IF EXISTS kitchen_orders;

CREATE TABLE settings (
  `key` VARCHAR(120) NOT NULL PRIMARY KEY,
  `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_renewals (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  previous_expires_at DATE NULL,
  new_expires_at DATE NOT NULL,
  renewed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  notes VARCHAR(255) NULL,
  PRIMARY KEY (id),
  KEY idx_renewed_at (renewed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE shop_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(60) NOT NULL UNIQUE,
  name_en VARCHAR(120) NOT NULL,
  name_bn VARCHAR(120) NOT NULL,
  feature_flags JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branches (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  code VARCHAR(40) NOT NULL DEFAULT '',
  phone VARCHAR(60) NULL,
  address TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_branches_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  slug VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  module VARCHAR(80) NOT NULL,
  action VARCHAR(40) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  KEY idx_perm_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  default_branch_id INT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  phone VARCHAR(40) NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_system_admin TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  language CHAR(2) NOT NULL DEFAULT 'en',
  theme VARCHAR(16) NOT NULL DEFAULT 'light',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id),
  CONSTRAINT fk_users_def_branch FOREIGN KEY (default_branch_id) REFERENCES branches(id) ON DELETE SET NULL,
  KEY idx_users_active (is_active),
  KEY idx_users_system (is_system_admin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_branches (
  user_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, branch_id),
  CONSTRAINT fk_ub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ub_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE units (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  short_name VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  parent_id INT UNSIGNED NULL,
  KEY idx_cat_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE brands (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE variation_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  display_order INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE variation_values (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  type_id INT UNSIGNED NOT NULL,
  value VARCHAR(80) NOT NULL,
  display_order INT UNSIGNED NOT NULL DEFAULT 0,
  KEY idx_vv_type (type_id),
  CONSTRAINT fk_vv_type FOREIGN KEY (type_id) REFERENCES variation_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_methods (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  code VARCHAR(40) NOT NULL DEFAULT '',
  type ENUM('cash','bank','mobile','card','other') NOT NULL DEFAULT 'cash',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE register_shifts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  terminal_name VARCHAR(80) NULL,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at DATETIME NULL,
  opening_note VARCHAR(500) NULL,
  closing_note VARCHAR(500) NULL,
  opened_by INT UNSIGNED NOT NULL,
  closed_by INT UNSIGNED NULL,
  KEY idx_rs_branch_status (branch_id, status),
  KEY idx_rs_user_status (user_id, status),
  KEY idx_rs_opened (opened_at),
  CONSTRAINT fk_rs_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_rs_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_rs_opened_by FOREIGN KEY (opened_by) REFERENCES users(id),
  CONSTRAINT fk_rs_closed_by FOREIGN KEY (closed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE register_shift_balances (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shift_id INT UNSIGNED NOT NULL,
  payment_method_id INT UNSIGNED NOT NULL,
  opening_amount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  closing_amount DECIMAL(18,4) NULL,
  expected_amount DECIMAL(18,4) NULL,
  sales_amount DECIMAL(18,4) NULL,
  handover_in DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  handover_out DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  UNIQUE KEY uq_rsb_shift_pm (shift_id, payment_method_id),
  CONSTRAINT fk_rsb_shift FOREIGN KEY (shift_id) REFERENCES register_shifts(id) ON DELETE CASCADE,
  CONSTRAINT fk_rsb_pm FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE register_shift_handovers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  from_shift_id INT UNSIGNED NULL,
  to_shift_id INT UNSIGNED NULL,
  from_user_id INT UNSIGNED NOT NULL,
  to_user_id INT UNSIGNED NOT NULL,
  payment_method_id INT UNSIGNED NOT NULL,
  amount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  status ENUM('pending','accepted','cancelled') NOT NULL DEFAULT 'pending',
  note VARCHAR(500) NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  accepted_at DATETIME NULL,
  accepted_by INT UNSIGNED NULL,
  KEY idx_rsh_to_user (to_user_id, status),
  KEY idx_rsh_branch (branch_id, created_at),
  CONSTRAINT fk_rsh_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_rsh_from_shift FOREIGN KEY (from_shift_id) REFERENCES register_shifts(id) ON DELETE SET NULL,
  CONSTRAINT fk_rsh_to_shift FOREIGN KEY (to_shift_id) REFERENCES register_shifts(id) ON DELETE SET NULL,
  CONSTRAINT fk_rsh_from_user FOREIGN KEY (from_user_id) REFERENCES users(id),
  CONSTRAINT fk_rsh_to_user FOREIGN KEY (to_user_id) REFERENCES users(id),
  CONSTRAINT fk_rsh_pm FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id),
  CONSTRAINT fk_rsh_created FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_rsh_accepted FOREIGN KEY (accepted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_groups (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  discount_type ENUM('none','flat','percent') NOT NULL DEFAULT 'none',
  discount_value DECIMAL(18,4) NOT NULL DEFAULT 0.0000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  group_id INT UNSIGNED NULL,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(160) NULL,
  address TEXT NULL,
  opening_balance DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  current_balance DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cust_branch (branch_id),
  KEY idx_cust_phone (phone),
  CONSTRAINT fk_cust_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_cust_group FOREIGN KEY (group_id) REFERENCES customer_groups(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_reward_balances (
  customer_id INT UNSIGNED NOT NULL PRIMARY KEY,
  points INT NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_crb_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reward_rules (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  points_per_amount DECIMAL(18,4) NOT NULL DEFAULT 1.0000,
  amount_per_point DECIMAL(18,4) NOT NULL DEFAULT 10.0000,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rr_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE suppliers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(160) NULL,
  address TEXT NULL,
  opening_balance DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  current_balance DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sup_branch (branch_id),
  CONSTRAINT fk_sup_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(120) NOT NULL,
  barcode VARCHAR(120) NULL,
  name VARCHAR(220) NOT NULL,
  description TEXT NULL,
  category_id INT UNSIGNED NULL,
  brand_id INT UNSIGNED NULL,
  unit_id INT UNSIGNED NOT NULL,
  product_kind ENUM('simple','variable') NOT NULL DEFAULT 'simple',
  purchase_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  sale_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  min_stock_level DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  track_serial TINYINT(1) NOT NULL DEFAULT 0,
  track_warranty TINYINT(1) NOT NULL DEFAULT 0,
  is_weight_based TINYINT(1) NOT NULL DEFAULT 0,
  meta JSON NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_products_sku (sku),
  KEY idx_products_name (name),
  KEY idx_products_barcode (barcode),
  KEY idx_products_cat (category_id),
  CONSTRAINT fk_prod_unit FOREIGN KEY (unit_id) REFERENCES units(id),
  CONSTRAINT fk_prod_cat FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_prod_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_branch_stock (
  product_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NOT NULL,
  quantity DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id, branch_id),
  CONSTRAINT fk_pbs_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_pbs_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  KEY idx_pbs_branch_qty (branch_id, quantity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_variations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  sku VARCHAR(120) NOT NULL DEFAULT '',
  name VARCHAR(160) NOT NULL,
  size_label VARCHAR(80) NULL,
  color_label VARCHAR(80) NULL,
  fabric_label VARCHAR(80) NULL,
  sku_suffix VARCHAR(80) NOT NULL DEFAULT '',
  variation_value_ids JSON NULL,
  barcode VARCHAR(120) NULL,
  purchase_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  sale_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  KEY idx_pv_prod (product_id),
  CONSTRAINT fk_pv_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_serials (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  variation_id INT UNSIGNED NULL,
  branch_id INT UNSIGNED NOT NULL,
  imei VARCHAR(32) NULL,
  serial_no VARCHAR(80) NULL,
  battery_health VARCHAR(40) NULL,
  warranty_months INT UNSIGNED NULL,
  warranty_start DATE NULL,
  batch_no VARCHAR(80) NULL,
  expiry_date DATE NULL,
  author_name VARCHAR(160) NULL,
  publication_name VARCHAR(160) NULL,
  isbn VARCHAR(40) NULL,
  status ENUM('in_stock','sold','returned','transit') NOT NULL DEFAULT 'in_stock',
  purchase_item_id BIGINT UNSIGNED NULL,
  sale_item_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ps_prod_branch (product_id, branch_id, status),
  KEY idx_ps_imei (imei),
  CONSTRAINT fk_ps_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_ps_var FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL,
  CONSTRAINT fk_ps_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE purchases (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  supplier_id INT UNSIGNED NOT NULL,
  invoice_no VARCHAR(80) NOT NULL,
  purchase_date DATE NOT NULL,
  subtotal DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  discount_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  tax_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  grand_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  paid_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  due_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  status ENUM('draft','completed','cancelled') NOT NULL DEFAULT 'completed',
  note VARCHAR(500) NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_purchase_inv_branch (branch_id, invoice_no),
  KEY idx_purchase_date (purchase_date),
  KEY idx_purchase_supplier (supplier_id),
  CONSTRAINT fk_pur_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_pur_sup FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_pur_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE purchase_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  purchase_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  variation_id INT UNSIGNED NULL,
  quantity DECIMAL(18,4) NOT NULL DEFAULT 1.0000,
  unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  line_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  KEY idx_pi_purchase (purchase_id),
  CONSTRAINT fk_pi_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
  CONSTRAINT fk_pi_prod FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_pi_var FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE purchase_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  purchase_id INT UNSIGNED NOT NULL,
  payment_method_id INT UNSIGNED NOT NULL,
  amount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pp_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
  CONSTRAINT fk_pp_pm FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sales (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NULL,
  invoice_no VARCHAR(80) NOT NULL,
  sale_date DATE NOT NULL,
  subtotal DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  discount_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  membership_discount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  reward_points_used INT NOT NULL DEFAULT 0,
  tax_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  grand_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  paid_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  due_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  status ENUM('draft','completed','cancelled') NOT NULL DEFAULT 'completed',
  note VARCHAR(500) NULL,
  created_by INT UNSIGNED NOT NULL,
  shift_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sale_inv_branch (branch_id, invoice_no),
  KEY idx_sale_date (sale_date),
  KEY idx_sale_customer (customer_id),
  KEY idx_sale_shift (shift_id),
  CONSTRAINT fk_sale_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_sale_shift FOREIGN KEY (shift_id) REFERENCES register_shifts(id) ON DELETE SET NULL,
  CONSTRAINT fk_sale_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_sale_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sale_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sale_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  variation_id INT UNSIGNED NULL,
  quantity DECIMAL(18,4) NOT NULL DEFAULT 1.0000,
  unit_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  discount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  tax DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  line_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  KEY idx_si_sale (sale_id),
  CONSTRAINT fk_si_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
  CONSTRAINT fk_si_prod FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_si_var FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sale_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sale_id INT UNSIGNED NOT NULL,
  payment_method_id INT UNSIGNED NOT NULL,
  amount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sp_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
  CONSTRAINT fk_sp_pm FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sale_returns (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sale_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NULL,
  return_date DATE NOT NULL,
  subtotal DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  note TEXT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sr_sale (sale_id),
  KEY idx_sr_branch (branch_id),
  CONSTRAINT fk_sr_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
  CONSTRAINT fk_sr_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_sr_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_sr_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sale_return_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  return_id INT UNSIGNED NOT NULL,
  sale_item_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  variation_id INT UNSIGNED NULL,
  quantity DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  unit_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  line_total DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  KEY idx_sri_return (return_id),
  KEY idx_sri_saleitem (sale_item_id),
  CONSTRAINT fk_sri_return FOREIGN KEY (return_id) REFERENCES sale_returns(id) ON DELETE CASCADE,
  CONSTRAINT fk_sri_si FOREIGN KEY (sale_item_id) REFERENCES sale_items(id),
  CONSTRAINT fk_sri_prod FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_sri_var FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reward_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  sale_id INT UNSIGNED NULL,
  points INT NOT NULL,
  type ENUM('earn','redeem','adjust') NOT NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rt_cust (customer_id),
  CONSTRAINT fk_rt_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_rt_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_transfers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  from_branch_id INT UNSIGNED NOT NULL,
  to_branch_id INT UNSIGNED NOT NULL,
  transfer_date DATE NOT NULL,
  status ENUM('draft','completed','cancelled') NOT NULL DEFAULT 'draft',
  note VARCHAR(255) NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_st_from FOREIGN KEY (from_branch_id) REFERENCES branches(id),
  CONSTRAINT fk_st_to FOREIGN KEY (to_branch_id) REFERENCES branches(id),
  CONSTRAINT fk_st_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_transfer_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  transfer_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  variation_id INT UNSIGNED NULL,
  quantity DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  CONSTRAINT fk_stl_t FOREIGN KEY (transfer_id) REFERENCES stock_transfers(id) ON DELETE CASCADE,
  CONSTRAINT fk_stl_p FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_stl_v FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_adjustments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  adj_date DATE NOT NULL,
  reason VARCHAR(200) NOT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sa_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_sa_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_adjustment_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  adjustment_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  variation_id INT UNSIGNED NULL,
  quantity_change DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  CONSTRAINT fk_sal_adj FOREIGN KEY (adjustment_id) REFERENCES stock_adjustments(id) ON DELETE CASCADE,
  CONSTRAINT fk_sal_p FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_sal_v FOREIGN KEY (variation_id) REFERENCES product_variations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE expense_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE expense_entries (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  amount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  expense_date DATE NOT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_exp_branch_date (branch_id, expense_date),
  CONSTRAINT fk_exp_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_exp_cat FOREIGN KEY (category_id) REFERENCES expense_categories(id),
  CONSTRAINT fk_exp_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE restaurant_tables (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  name VARCHAR(80) NOT NULL,
  capacity INT UNSIGNED NOT NULL DEFAULT 4,
  status ENUM('free','occupied','reserved') NOT NULL DEFAULT 'free',
  CONSTRAINT fk_rt_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE kitchen_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  table_id INT UNSIGNED NULL,
  sale_id INT UNSIGNED NULL,
  status ENUM('queued','cooking','served','cancelled') NOT NULL DEFAULT 'queued',
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ko_branch (branch_id),
  CONSTRAINT fk_ko_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_ko_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
  CONSTRAINT fk_ko_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(80) NOT NULL,
  entity_id VARCHAR(80) NULL,
  meta JSON NULL,
  ip_address VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_entity (entity, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- Seed shop types
INSERT INTO shop_types (slug, name_en, name_bn, feature_flags) VALUES
('electronics', 'Electronics Shop', 'ইলেকট্রনিক্স দোকান', JSON_OBJECT('imei',true,'warranty',true,'serial',true)),
('clothing', 'Clothing Shop', 'কাপড়ের দোকান', JSON_OBJECT('size',true,'color',true,'fabric',true)),
('cosmetic', 'Cosmetic Shop', 'কসমেটিক্স', JSON_OBJECT('batch',true,'expiry',true)),
('book', 'Book Shop', 'বইয়ের দোকান', JSON_OBJECT('author',true,'publication',true,'isbn',true)),
('grocery', 'Grocery Shop', 'মুদি দোকান', JSON_OBJECT('weight',true)),
('mobile', 'Mobile Shop', 'মোবাইল দোকান', JSON_OBJECT('imei',true,'battery_health',true,'warranty',true)),
('restaurant', 'Restaurant', 'রেস্টুরেন্ট', JSON_OBJECT('tables',true,'kitchen',true)),
('hardware', 'Hardware Shop', 'হার্ডওয়্যার', JSON_OBJECT('serial',true)),
('computer', 'Computer Shop', 'কম্পিউটার দোকান', JSON_OBJECT('imei',true,'warranty',true,'serial',true));

INSERT INTO branches (name, code, phone, address) VALUES
('Main Branch', 'MB001', '01700000000', 'Dhaka');

INSERT INTO roles (name, slug) VALUES
('Administrator', 'admin'),
('Manager', 'manager'),
('Cashier', 'cashier'),
('Salesman', 'salesman');

INSERT INTO units (name, short_name) VALUES
('Piece', 'pcs'),
('Kilogram', 'kg'),
('Liter', 'ltr'),
('Box', 'box');

INSERT INTO variation_types (name, display_order) VALUES ('Size', 1), ('Color', 2);
INSERT INTO variation_values (type_id, value, display_order) VALUES
(1, 'S', 1), (1, 'M', 2), (1, 'L', 3), (1, 'XL', 4), (1, 'XXL', 5),
(2, 'Red', 1), (2, 'Blue', 2), (2, 'Black', 3), (2, 'White', 4), (2, 'Green', 5);

INSERT INTO payment_methods (name, code, type, sort_order) VALUES
('Cash', 'cash', 'cash', 1),
('bKash', 'bkash', 'mobile', 2),
('Bank', 'bank', 'bank', 3),
('Card', 'card', 'card', 4);

INSERT INTO expense_categories (name) VALUES ('Rent'), ('Utilities'), ('Salary'), ('Misc');

INSERT INTO customer_groups (name, discount_type, discount_value) VALUES
('Retail', 'none', 0),
('VIP', 'percent', 5.0000);

INSERT INTO settings (`key`, `value`) VALUES
('business_name', 'POS26 Demo Store'),
('business_address', 'Dhaka, Bangladesh'),
('business_phone', '01700000000'),
('shop_type_id', '1'),
('default_language', 'en'),
('ui_sounds', '1'),
('invoice_template', 'a4'),
('tax_percent', '0'),
('default_profit_margin', '0'),
('sku_prefix', 'SKU'),
('max_products', '50000'),
('shift_required', '1'),
('mod_customer_groups', '1'),
('mod_rewards', '1'),
('mod_sms', '1'),
('sms_api_url', 'http://bulksmsbd.net/api/smsapi'),
('sms_api_key', ''),
('sms_sender_id', ''),
('sms_append_business', '1'),
('sms_on_sale_customer', '0'),
('sms_on_purchase_supplier', '0'),
('subscription_expires_at', DATE_ADD(CURDATE(), INTERVAL 30 DAY)),
('subscription_unlock_passcode', '$2y$10$pFBvG1K3m99ao/iHUo.LPu1tybNkpFeQblCvSjiF/puARuuAm3lTG'),
('subscription_activated_at', CURDATE());

-- Permissions (subset; System Admin bypasses)
INSERT INTO permissions (module, action, slug) VALUES
('dashboard', 'view', 'dashboard.view'),
('branches', 'view', 'branches.view'),
('branches', 'create', 'branches.create'),
('branches', 'edit', 'branches.edit'),
('branches', 'delete', 'branches.delete'),
('users', 'view', 'users.view'),
('users', 'create', 'users.create'),
('users', 'edit', 'users.edit'),
('users', 'delete', 'users.delete'),
('products', 'view', 'products.view'),
('products', 'create', 'products.create'),
('products', 'edit', 'products.edit'),
('products', 'delete', 'products.delete'),
('customers', 'view', 'customers.view'),
('customers', 'create', 'customers.create'),
('customers', 'edit', 'customers.edit'),
('customers', 'delete', 'customers.delete'),
('suppliers', 'view', 'suppliers.view'),
('suppliers', 'create', 'suppliers.create'),
('suppliers', 'edit', 'suppliers.edit'),
('suppliers', 'delete', 'suppliers.delete'),
('sales', 'view', 'sales.view'),
('sales', 'create', 'sales.create'),
('sales', 'edit', 'sales.edit'),
('sales', 'delete', 'sales.delete'),
('purchase', 'view', 'purchase.view'),
('purchase', 'create', 'purchase.create'),
('purchase', 'edit', 'purchase.edit'),
('purchase', 'delete', 'purchase.delete'),
('pos', 'access', 'pos.access'),
('reports', 'view', 'reports.view'),
('settings', 'view', 'settings.view'),
('settings', 'edit', 'settings.edit'),
('settings', 'system', 'settings.system'),
('shifts', 'view', 'shifts.view'),
('shifts', 'open', 'shifts.open'),
('shifts', 'close', 'shifts.close'),
('shifts', 'handover', 'shifts.handover'),
('shifts', 'report', 'shifts.report'),
('expenses', 'view', 'expenses.view'),
('expenses', 'create', 'expenses.create'),
('expenses', 'edit', 'expenses.edit'),
('expenses', 'delete', 'expenses.delete'),
('stock', 'transfer_view', 'stock.transfer.view'),
('stock', 'transfer_create', 'stock.transfer.create'),
('stock', 'transfer_delete', 'stock.transfer.delete'),
('stock', 'adjust_view', 'stock.adjust.view'),
('stock', 'adjust_create', 'stock.adjust.create'),
('stock', 'adjust_delete', 'stock.adjust.delete'),
('sms', 'view', 'sms.view'),
('sms', 'send', 'sms.send');

-- Grant all permissions to admin role (id=1)
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

-- Manager: broad operational
INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE slug NOT LIKE 'settings.system%' AND slug NOT IN ('branches.delete','users.delete','settings.edit');

-- Cashier
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE slug IN ('dashboard.view','pos.access','sales.view','sales.create','customers.view','products.view','shifts.view','shifts.open','shifts.close','shifts.handover');

-- Salesman
INSERT INTO role_permissions (role_id, permission_id)
SELECT 4, id FROM permissions WHERE slug IN ('dashboard.view','sales.view','sales.create','customers.view','customers.create','products.view');

-- Users: system admin + admin (password: Admin@123)
INSERT INTO users (role_id, default_branch_id, name, email, phone, password_hash, is_system_admin, language, theme) VALUES
(1, 1, 'System Admin', 'sysadmin@pos.local', NULL, '$2y$10$eqlEDpRjFPO4vH/6PfhnA.hiTeG2xjNMvjV0hBHu77lqzrATsAXVy', 1, 'en', 'dark'),
(1, 1, 'Store Admin', 'admin@pos.local', NULL, '$2y$10$eqlEDpRjFPO4vH/6PfhnA.hiTeG2xjNMvjV0hBHu77lqzrATsAXVy', 0, 'bn', 'light');

INSERT INTO user_branches (user_id, branch_id, is_default) VALUES
(1, 1, 1),
(2, 1, 1);

INSERT INTO reward_rules (branch_id, is_active, points_per_amount, amount_per_point) VALUES
(1, 1, 1.0000, 100.0000);

INSERT INTO categories (name) VALUES ('General'), ('Electronics');
INSERT INTO brands (name) VALUES ('Generic');

INSERT INTO suppliers (branch_id, name, phone) VALUES (1, 'Default Supplier', '01800000000');

INSERT INTO products (sku, barcode, name, category_id, brand_id, unit_id, product_kind, purchase_price, sale_price, track_serial, track_warranty, is_weight_based, meta) VALUES
('SKU-DEMO-1', '8801234567890', 'Demo Product', 1, 1, 1, 'simple', 50.0000, 75.0000, 0, 0, 0, NULL);

INSERT INTO product_branch_stock (product_id, branch_id, quantity) VALUES (1, 1, 100.0000);

INSERT INTO customers (branch_id, group_id, name, phone) VALUES (1, 1, 'Walk-in Customer', '01900000000');

INSERT INTO customer_reward_balances (customer_id, points) VALUES (1, 0);