-- TextilePro ERP baseline schema (RBAC + audit + core masters scaffold)
-- MySQL/MariaDB (utf8mb4)

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  full_name VARCHAR(120) NULL,
  employee_id INT UNSIGNED NULL,
  password_hash VARCHAR(255) NOT 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,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_username (username),
  UNIQUE KEY uq_users_employee (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  code VARCHAR(80) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_permissions_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id INT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Tamper-evident audit chain (hash of previous row)
CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NULL,
  action VARCHAR(20) NOT NULL,          -- create|update|delete|login|logout
  entity VARCHAR(80) NOT NULL,          -- table/module name
  entity_id VARCHAR(64) NULL,           -- string to support composite IDs
  before_json JSON NULL,
  after_json JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  prev_hash CHAR(64) NULL,
  row_hash CHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_user (user_id),
  KEY idx_audit_entity (entity, entity_id),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed RBAC
INSERT INTO roles (name, code) VALUES
  ('Administrator', 'admin'),
  ('Module: Inventory', 'mod_inventory'),
  ('Module: Sales', 'mod_sales'),
  ('Module: HRMS', 'mod_hr'),
  ('Module: Accounts', 'mod_accounts'),
  ('Module: Dispatch', 'mod_dispatch'),
  ('Module: Analytics', 'mod_analytics'),
  ('Module: Administration', 'mod_admin')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO permissions (name, code) VALUES
  ('View users', 'admin.users.view'),
  ('Manage users', 'admin.users.manage'),
  ('Inventory: View', 'inventory.view'),
  ('Inventory: Manage', 'inventory.manage'),
  ('Accounts: View', 'accounts.view'),
  ('Accounts: Manage', 'accounts.manage'),
  ('HRMS: View', 'hr.view'),
  ('HRMS: Manage', 'hr.manage'),
  ('Sales: View', 'sales.view'),
  ('Sales: Manage', 'sales.manage'),
  ('Dispatch: View', 'dispatch.view'),
  ('Dispatch: Manage', 'dispatch.manage'),
  ('Analytics: View', 'analytics.view')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r, permissions p
WHERE r.code='admin' AND p.code IN (
  'admin.users.view','admin.users.manage','inventory.view','inventory.manage',
  'accounts.view','accounts.manage','hr.view','hr.manage','sales.view','sales.manage','dispatch.view','dispatch.manage','analytics.view'
)
ON DUPLICATE KEY UPDATE role_id=role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code='mod_inventory' AND p.code IN ('inventory.view','inventory.manage')
ON DUPLICATE KEY UPDATE role_id=role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code='mod_sales' AND p.code IN ('sales.view','sales.manage')
ON DUPLICATE KEY UPDATE role_id=role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code='mod_hr' AND p.code IN ('hr.view','hr.manage')
ON DUPLICATE KEY UPDATE role_id=role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code='mod_accounts' AND p.code IN ('accounts.view','accounts.manage')
ON DUPLICATE KEY UPDATE role_id=role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code='mod_dispatch' AND p.code IN ('dispatch.view','dispatch.manage')
ON DUPLICATE KEY UPDATE role_id=role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code='mod_analytics' AND p.code='analytics.view'
ON DUPLICATE KEY UPDATE role_id=role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code='mod_admin' AND p.code IN ('admin.users.view','admin.users.manage')
ON DUPLICATE KEY UPDATE role_id=role_id;

-- Inventory masters (TOR Module 1 baseline)
CREATE TABLE IF NOT EXISTS inv_item_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_item_cat_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories are loaded from Master Sheet import (Stock list-3.xlsx), not seeded here.

CREATE TABLE IF NOT EXISTS inv_variants (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_variant_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO inv_variants (name, code) VALUES
  ('Plain', 'PLAIN'),
  ('Dotted', 'DOTTED'),
  ('Star', 'STAR'),
  ('Check', 'CHECK')
ON DUPLICATE KEY UPDATE name=VALUES(name);

CREATE TABLE IF NOT EXISTS inv_colors (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_color_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO inv_colors (name, code) VALUES
  ('White', 'WHITE'),
  ('Black', 'BLACK'),
  ('Red', 'RED'),
  ('Blue', 'BLUE'),
  ('Green', 'GREEN'),
  ('Grey', 'GREY')
ON DUPLICATE KEY UPDATE name=VALUES(name);

CREATE TABLE IF NOT EXISTS inv_item_subcategories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_subcat_cat_name (category_id, name),
  KEY idx_inv_subcat_category (category_id),
  CONSTRAINT fk_inv_subcat_category FOREIGN KEY (category_id) REFERENCES inv_item_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inv_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_type ENUM('raw','finished') NOT NULL DEFAULT 'raw',
  category_id INT UNSIGNED NULL,
  subcategory_id INT UNSIGNED NULL,
  name VARCHAR(255) NOT NULL,
  popular_words VARCHAR(255) NULL,
  sku VARCHAR(80) NULL,
  barcode VARCHAR(80) NULL,
  hsn_code VARCHAR(20) NULL,
  uom VARCHAR(20) NOT NULL DEFAULT 'PCS',
  selling_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  gst_rate DECIMAL(5,2) NOT NULL DEFAULT 5.00,
  tentative_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  stock_qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  variant_id INT UNSIGNED NULL,
  color_id INT UNSIGNED NULL,
  gender VARCHAR(20) NULL DEFAULT 'All',
  size_label VARCHAR(40) NULL,
  quality VARCHAR(80) NULL,
  specification 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,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_items_sku (sku),
  KEY idx_inv_items_category (category_id),
  KEY idx_inv_items_subcategory (subcategory_id),
  KEY idx_inv_items_variant (variant_id),
  KEY idx_inv_items_color (color_id),
  CONSTRAINT fk_inv_items_category FOREIGN KEY (category_id) REFERENCES inv_item_categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_items_subcategory FOREIGN KEY (subcategory_id) REFERENCES inv_item_subcategories(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_items_variant FOREIGN KEY (variant_id) REFERENCES inv_variants(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_items_color FOREIGN KEY (color_id) REFERENCES inv_colors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Upgrade existing databases (safe if column already exists)
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'category_id'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE inv_items ADD COLUMN category_id INT UNSIGNED NULL AFTER item_type, ADD KEY idx_inv_items_category (category_id)',
  'DO 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- UOM is free-form varchar (PCS, DZN, SET, PAIR, etc.)
ALTER TABLE inv_items MODIFY uom VARCHAR(20) NOT NULL DEFAULT 'PCS';
ALTER TABLE inv_items MODIFY name VARCHAR(255) NOT NULL;

SET @col_variant_id := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'variant_id'
);
SET @sql_variant := IF(@col_variant_id = 0,
  'ALTER TABLE inv_items ADD COLUMN variant_id INT UNSIGNED NULL AFTER uom, ADD COLUMN color_id INT UNSIGNED NULL AFTER variant_id',
  'DO 0');
PREPARE stmt_variant FROM @sql_variant;
EXECUTE stmt_variant;
DEALLOCATE PREPARE stmt_variant;

CREATE TABLE IF NOT EXISTS inv_vendors (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  gstin VARCHAR(20) NULL,
  credit_terms_days INT UNSIGNED NOT NULL DEFAULT 0,
  phone VARCHAR(30) NULL,
  email VARCHAR(120) NULL,
  bank_account VARCHAR(50) NULL,
  ifsc VARCHAR(20) NULL,
  address 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,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inv_warehouses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(50) NULL,
  address 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,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_wh_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inv_po (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  po_no VARCHAR(40) NOT NULL,
  vendor_id INT UNSIGNED NOT NULL,
  po_date DATE NOT NULL,
  status ENUM('draft','open','closed','cancelled') NOT NULL DEFAULT 'open',
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_po_no (po_no),
  KEY idx_inv_po_vendor (vendor_id),
  CONSTRAINT fk_inv_po_vendor FOREIGN KEY (vendor_id) REFERENCES inv_vendors(id),
  CONSTRAINT fk_inv_po_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inv_po_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  po_id BIGINT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_inv_po_lines_po (po_id),
  CONSTRAINT fk_inv_po_lines_po FOREIGN KEY (po_id) REFERENCES inv_po(id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_po_lines_item FOREIGN KEY (item_id) REFERENCES inv_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inv_grn (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  grn_no VARCHAR(40) NOT NULL,
  po_id BIGINT UNSIGNED NULL,
  warehouse_id INT UNSIGNED NOT NULL,
  grn_date DATE NOT NULL,
  status ENUM('received','qc_hold','qc_fail') NOT NULL DEFAULT 'received',
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inv_grn_no (grn_no),
  KEY idx_inv_grn_wh (warehouse_id),
  CONSTRAINT fk_inv_grn_po FOREIGN KEY (po_id) REFERENCES inv_po(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_grn_wh FOREIGN KEY (warehouse_id) REFERENCES inv_warehouses(id),
  CONSTRAINT fk_inv_grn_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inv_grn_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  grn_id BIGINT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,
  qty_received DECIMAL(12,3) NOT NULL DEFAULT 0,
  qc_status ENUM('pass','fail','hold') NOT NULL DEFAULT 'pass',
  PRIMARY KEY (id),
  KEY idx_inv_grn_lines_grn (grn_id),
  CONSTRAINT fk_inv_grn_lines_grn FOREIGN KEY (grn_id) REFERENCES inv_grn(id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_grn_lines_item FOREIGN KEY (item_id) REFERENCES inv_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Module 2: Accounts (Trial Balance) - baseline
-- ----------------------------
CREATE TABLE IF NOT EXISTS acc_cost_centres (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_acc_cc_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS acc_accounts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NULL,
  type ENUM('asset','liability','income','expense','equity') NOT NULL,
  parent_id INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_acc_accounts_code (code),
  KEY idx_acc_parent (parent_id),
  CONSTRAINT fk_acc_parent FOREIGN KEY (parent_id) REFERENCES acc_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS acc_vouchers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  voucher_no VARCHAR(40) NOT NULL,
  voucher_date DATE NOT NULL,
  voucher_type ENUM('journal','receipt','payment','contra','sales','purchase') NOT NULL DEFAULT 'journal',
  narration TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_acc_voucher_no (voucher_no),
  CONSTRAINT fk_acc_voucher_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS acc_voucher_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  voucher_id BIGINT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  cost_centre_id INT UNSIGNED NULL,
  dr DECIMAL(14,2) NOT NULL DEFAULT 0,
  cr DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_note VARCHAR(255) NULL,
  PRIMARY KEY (id),
  KEY idx_acc_vl_voucher (voucher_id),
  KEY idx_acc_vl_account (account_id),
  CONSTRAINT fk_acc_vl_voucher FOREIGN KEY (voucher_id) REFERENCES acc_vouchers(id) ON DELETE CASCADE,
  CONSTRAINT fk_acc_vl_account FOREIGN KEY (account_id) REFERENCES acc_accounts(id),
  CONSTRAINT fk_acc_vl_cc FOREIGN KEY (cost_centre_id) REFERENCES acc_cost_centres(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Module 3: HRMS - baseline
-- ----------------------------
CREATE TABLE IF NOT EXISTS hr_departments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_dept_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_employees (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  emp_code VARCHAR(40) NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  department_id INT UNSIGNED NULL,
  designation VARCHAR(120) NULL,
  contract_type ENUM('permanent','contract','daily_wage') NOT NULL DEFAULT 'permanent',
  phone VARCHAR(30) NULL,
  bank_account VARCHAR(50) NULL,
  ifsc VARCHAR(20) NULL,
  doj DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_emp_code (emp_code),
  CONSTRAINT fk_hr_emp_dept FOREIGN KEY (department_id) REFERENCES hr_departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE users
  ADD CONSTRAINT fk_users_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS hr_attendance (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  att_date DATE NOT NULL,
  status ENUM('P','A','HD','WO','H') NOT NULL DEFAULT 'P',
  in_time TIME NULL,
  out_time TIME NULL,
  overtime_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_att (employee_id, att_date),
  CONSTRAINT fk_hr_att_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_leave_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  code VARCHAR(20) NOT NULL,
  annual_quota DECIMAL(8,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_lt_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_leave_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  leave_type_id INT UNSIGNED NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  reason VARCHAR(255) NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT fk_hr_lr_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_hr_lr_lt FOREIGN KEY (leave_type_id) REFERENCES hr_leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_payroll_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_year INT UNSIGNED NOT NULL,
  period_month INT UNSIGNED NOT NULL,
  run_date DATE NOT NULL,
  status ENUM('draft','final') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_payroll_period (period_year, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_payslips (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payroll_run_id BIGINT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  gross DECIMAL(12,2) NOT NULL DEFAULT 0,
  deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_pay DECIMAL(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_payslip (payroll_run_id, employee_id),
  CONSTRAINT fk_hr_ps_run FOREIGN KEY (payroll_run_id) REFERENCES hr_payroll_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_hr_ps_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_employee_kras (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  period_type ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'monthly',
  target_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  unit VARCHAR(40) NOT NULL DEFAULT 'units',
  weight DECIMAL(5,2) NOT NULL DEFAULT 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,
  PRIMARY KEY (id),
  KEY idx_hr_kra_emp (employee_id),
  CONSTRAINT fk_hr_kra_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_kra_progress (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  kra_id BIGINT UNSIGNED NOT NULL,
  period_key VARCHAR(20) NOT NULL,
  actual_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  recorded_by INT UNSIGNED NULL,
  recorded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_kra_progress (kra_id, period_key),
  CONSTRAINT fk_hr_kra_prog_kra FOREIGN KEY (kra_id) REFERENCES hr_employee_kras(id) ON DELETE CASCADE,
  CONSTRAINT fk_hr_kra_prog_user FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Module 4: Sales Tracker - baseline
-- ----------------------------
CREATE TABLE IF NOT EXISTS sales_customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  gstin VARCHAR(20) NULL,
  credit_limit DECIMAL(14,2) NOT NULL DEFAULT 0,
  payment_terms_days INT UNSIGNED NOT NULL DEFAULT 0,
  phone VARCHAR(30) NULL,
  email VARCHAR(120) NULL,
  address TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_sales_customers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_enquiries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  enquiry_date DATE NOT NULL,
  fabric_spec TEXT NULL,
  status ENUM('open','quoted','closed') NOT NULL DEFAULT 'open',
  PRIMARY KEY (id),
  CONSTRAINT fk_sales_enq_cust FOREIGN KEY (customer_id) REFERENCES sales_customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_quotations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  quote_no VARCHAR(40) NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  quote_date DATE NOT NULL,
  status ENUM('draft','sent','confirmed','cancelled') NOT NULL DEFAULT 'draft',
  PRIMARY KEY (id),
  UNIQUE KEY uq_sales_quote_no (quote_no),
  CONSTRAINT fk_sales_quote_cust FOREIGN KEY (customer_id) REFERENCES sales_customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_quote_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  quotation_id BIGINT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount DECIMAL(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  CONSTRAINT fk_sales_ql_q FOREIGN KEY (quotation_id) REFERENCES sales_quotations(id) ON DELETE CASCADE,
  CONSTRAINT fk_sales_ql_item FOREIGN KEY (item_id) REFERENCES inv_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  so_no VARCHAR(40) NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  status ENUM('open','partial','closed','cancelled') NOT NULL DEFAULT 'open',
  PRIMARY KEY (id),
  UNIQUE KEY uq_sales_so_no (so_no),
  CONSTRAINT fk_sales_so_cust FOREIGN KEY (customer_id) REFERENCES sales_customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_order_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sales_order_id BIGINT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  CONSTRAINT fk_sales_sol_so FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_sales_sol_item FOREIGN KEY (item_id) REFERENCES inv_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_invoices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_no VARCHAR(40) NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  invoice_date DATE NOT NULL,
  sales_order_id BIGINT UNSIGNED NULL,
  is_gst_invoice TINYINT(1) NOT NULL DEFAULT 0,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  tax DECIMAL(14,2) NOT NULL DEFAULT 0,
  total DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('tentative','open','paid','cancelled') NOT NULL DEFAULT 'open',
  PRIMARY KEY (id),
  UNIQUE KEY uq_sales_invoice_no (invoice_no),
  CONSTRAINT fk_sales_inv_cust FOREIGN KEY (customer_id) REFERENCES sales_customers(id),
  CONSTRAINT fk_sales_inv_so FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_invoice_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id BIGINT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,
  sku VARCHAR(80) NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_sales_inv_lines_inv (invoice_id),
  CONSTRAINT fk_sales_inv_lines_inv FOREIGN KEY (invoice_id) REFERENCES sales_invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_sales_inv_lines_item FOREIGN KEY (item_id) REFERENCES inv_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @col_gst_inv := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_invoices' AND COLUMN_NAME = 'is_gst_invoice'
);
SET @sql_gst_inv := IF(@col_gst_inv = 0,
  'ALTER TABLE sales_invoices ADD COLUMN is_gst_invoice TINYINT(1) NOT NULL DEFAULT 0 AFTER sales_order_id',
  'DO 0');
PREPARE stmt_gst_inv FROM @sql_gst_inv;
EXECUTE stmt_gst_inv;
DEALLOCATE PREPARE stmt_gst_inv;

SET @col_sell_rate := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'selling_rate'
);
SET @sql_sell_rate := IF(@col_sell_rate = 0,
  'ALTER TABLE inv_items ADD COLUMN selling_rate DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER uom',
  'DO 0');
PREPARE stmt_sell_rate FROM @sql_sell_rate;
EXECUTE stmt_sell_rate;
DEALLOCATE PREPARE stmt_sell_rate;

SET @col_stock_qty := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'stock_qty'
);
SET @sql_stock_qty := IF(@col_stock_qty = 0,
  'ALTER TABLE inv_items ADD COLUMN stock_qty DECIMAL(12,3) NOT NULL DEFAULT 0 AFTER selling_rate',
  'DO 0');
PREPARE stmt_stock_qty FROM @sql_stock_qty;
EXECUTE stmt_stock_qty;
DEALLOCATE PREPARE stmt_stock_qty;

SET @col_popular_words := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'popular_words'
);
SET @sql_popular_words := IF(@col_popular_words = 0,
  'ALTER TABLE inv_items ADD COLUMN popular_words VARCHAR(255) NULL AFTER name',
  'DO 0');
PREPARE stmt_popular_words FROM @sql_popular_words;
EXECUTE stmt_popular_words;
DEALLOCATE PREPARE stmt_popular_words;

SET @col_gst_rate := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'gst_rate'
);
SET @sql_gst_rate := IF(@col_gst_rate = 0,
  'ALTER TABLE inv_items ADD COLUMN gst_rate DECIMAL(5,2) NOT NULL DEFAULT 5.00 AFTER selling_rate',
  'DO 0');
PREPARE stmt_gst_rate FROM @sql_gst_rate;
EXECUTE stmt_gst_rate;
DEALLOCATE PREPARE stmt_gst_rate;

SET @col_tentative_cost := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'tentative_cost'
);
SET @sql_tentative_cost := IF(@col_tentative_cost = 0,
  'ALTER TABLE inv_items ADD COLUMN tentative_cost DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER gst_rate',
  'DO 0');
PREPARE stmt_tentative_cost FROM @sql_tentative_cost;
EXECUTE stmt_tentative_cost;
DEALLOCATE PREPARE stmt_tentative_cost;

SET @col_barcode := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_items' AND COLUMN_NAME = 'barcode'
);
SET @sql_barcode := IF(@col_barcode = 0,
  'ALTER TABLE inv_items ADD COLUMN barcode VARCHAR(80) NULL AFTER sku',
  'DO 0');
PREPARE stmt_barcode FROM @sql_barcode;
EXECUTE stmt_barcode;
DEALLOCATE PREPARE stmt_barcode;

ALTER TABLE sales_invoices MODIFY status ENUM('tentative','open','paid','cancelled') NOT NULL DEFAULT 'open';

CREATE TABLE IF NOT EXISTS sales_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  payment_date DATE NOT NULL,
  mode ENUM('cash','cheque','neft','upi') NOT NULL DEFAULT 'neft',
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  tds_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  reference_no VARCHAR(80) NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_sales_pay_cust FOREIGN KEY (customer_id) REFERENCES sales_customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Module 5: Dispatch - baseline
-- ----------------------------
CREATE TABLE IF NOT EXISTS dsp_transporters (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  gstin VARCHAR(20) NULL,
  phone VARCHAR(30) NULL,
  contact_person VARCHAR(120) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dsp_dispatches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dispatch_no VARCHAR(40) NOT NULL,
  sales_order_id BIGINT UNSIGNED NOT NULL,
  dispatch_date DATE NOT NULL,
  transporter_id INT UNSIGNED NULL,
  vehicle_no VARCHAR(30) NULL,
  status ENUM('planned','packed','shipped','delivered') NOT NULL DEFAULT 'planned',
  PRIMARY KEY (id),
  UNIQUE KEY uq_dsp_dispatch_no (dispatch_no),
  CONSTRAINT fk_dsp_disp_so FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id),
  CONSTRAINT fk_dsp_disp_trans FOREIGN KEY (transporter_id) REFERENCES dsp_transporters(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dsp_packing_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dispatch_id BIGINT UNSIGNED NOT NULL,
  item_id INT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  batch_no VARCHAR(50) NULL,
  weight DECIMAL(12,3) NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_dsp_pack_disp FOREIGN KEY (dispatch_id) REFERENCES dsp_dispatches(id) ON DELETE CASCADE,
  CONSTRAINT fk_dsp_pack_item FOREIGN KEY (item_id) REFERENCES inv_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dsp_lr (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dispatch_id BIGINT UNSIGNED NOT NULL,
  lr_no VARCHAR(50) NOT NULL,
  freight_terms VARCHAR(80) NULL,
  remarks VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_dsp_lr_no (lr_no),
  CONSTRAINT fk_dsp_lr_disp FOREIGN KEY (dispatch_id) REFERENCES dsp_dispatches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dsp_pod (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dispatch_id BIGINT UNSIGNED NOT NULL,
  received_date DATE NULL,
  received_qty_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_dsp_pod_disp (dispatch_id),
  CONSTRAINT fk_dsp_pod_disp FOREIGN KEY (dispatch_id) REFERENCES dsp_dispatches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed admin user (password: admin123)
-- hash generated using PHP password_hash('admin123', PASSWORD_DEFAULT)
INSERT INTO users (username, full_name, password_hash, is_active)
VALUES ('admin', 'System Admin', '$2y$10$jf8C8pc1pbo1yRRKbOV6I.ods/CS9ODX3mK7OP6Z8TdBKItCquXyC', 1)
ON DUPLICATE KEY UPDATE full_name=VALUES(full_name), is_active=VALUES(is_active);

INSERT INTO user_roles (user_id, role_id)
SELECT u.id, r.id
FROM users u, roles r
WHERE u.username='admin' AND r.code='admin'
ON DUPLICATE KEY UPDATE user_id=user_id;