CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(50) NULL UNIQUE,
  role ENUM('OWNER','ADMIN','MANAGER','OFFICE_STAFF','ACCOUNTS','SALES','ENGINEER','SERVICE_MANAGER','STORE') NOT NULL DEFAULT 'OFFICE_STAFF',
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  force_password_change TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NULL,
  entity_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(80) NULL,
  user_agent VARCHAR(255) NULL,
  meta_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_activity_user (user_id),
  INDEX idx_activity_entity (entity_type, entity_id),
  CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  company_name VARCHAR(180) NULL,
  phone VARCHAR(60) NOT NULL,
  email VARCHAR(190) NULL,
  area VARCHAR(120) NULL,
  address TEXT NULL,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance_due DECIMAL(14,2) NOT NULL DEFAULT 0,
  note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_customer_phone (phone),
  INDEX idx_customer_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  ledger_date DATE NOT NULL,
  ref_type VARCHAR(50) NOT NULL,
  ref_id BIGINT UNSIGNED NULL,
  particulars VARCHAR(255) NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  running_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_customer_ledger (customer_id, ledger_date),
  CONSTRAINT fk_customer_ledger_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(220) NOT NULL,
  category VARCHAR(120) NULL,
  brand VARCHAR(120) NULL,
  model VARCHAR(120) NULL,
  unit VARCHAR(30) NOT NULL DEFAULT 'pcs',
  purchase_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  selling_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  stock_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  low_stock_alert DECIMAL(14,3) NOT NULL DEFAULT 0,
  warranty_note TEXT NULL,
  description TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_product_name (name),
  INDEX idx_product_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_movements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id BIGINT UNSIGNED NOT NULL,
  movement_date DATE NOT NULL,
  movement_type ENUM('opening','in','out','adjustment') NOT NULL,
  ref_type VARCHAR(80) NULL,
  ref_id BIGINT UNSIGNED NULL,
  qty_in DECIMAL(14,3) NOT NULL DEFAULT 0,
  qty_out DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_stock_product_date (product_id, movement_date),
  CONSTRAINT fk_stock_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_stock_user 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 suppliers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  country VARCHAR(100) NULL,
  contact_person VARCHAR(150) NULL,
  phone VARCHAR(60) NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance_due DECIMAL(14,2) NOT NULL DEFAULT 0,
  note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_supplier_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS supplier_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  supplier_id BIGINT UNSIGNED NOT NULL,
  ledger_date DATE NOT NULL,
  ref_type VARCHAR(50) NOT NULL,
  ref_id BIGINT UNSIGNED NULL,
  particulars VARCHAR(255) NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  running_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_supplier_ledger (supplier_id, ledger_date),
  CONSTRAINT fk_supplier_ledger_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lc_tt_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  record_type ENUM('LC','TT') NOT NULL,
  supplier_id BIGINT UNSIGNED NOT NULL,
  pi_no VARCHAR(120) NULL,
  lc_no VARCHAR(120) NULL,
  tt_ref VARCHAR(120) NULL,
  currency VARCHAR(20) NOT NULL DEFAULT 'USD',
  currency_rate DECIMAL(14,4) NOT NULL DEFAULT 0,
  foreign_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  bdt_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  bank_charge DECIMAL(14,2) NOT NULL DEFAULT 0,
  shipping_charge DECIMAL(14,2) NOT NULL DEFAULT 0,
  customs_charge DECIMAL(14,2) NOT NULL DEFAULT 0,
  cnf_transport DECIMAL(14,2) NOT NULL DEFAULT 0,
  other_charge DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_landed_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
  eta_date DATE NULL,
  status ENUM('draft','ordered','in_transit','customs','received','closed','cancelled') NOT NULL DEFAULT 'draft',
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_lc_supplier (supplier_id),
  INDEX idx_lc_status (status),
  CONSTRAINT fk_lc_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lc_user 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 office_tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(220) NOT NULL,
  description TEXT NULL,
  priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  status ENUM('new','in_progress','waiting','completed','cancelled') NOT NULL DEFAULT 'new',
  assigned_to BIGINT UNSIGNED NULL,
  due_date DATE NULL,
  related_type VARCHAR(80) NULL,
  related_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_task_status (status),
  INDEX idx_task_assigned (assigned_to),
  CONSTRAINT fk_task_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_task_created 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 task_comments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  task_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  comment TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_task_comment_task FOREIGN KEY (task_id) REFERENCES office_tasks(id) ON DELETE CASCADE,
  CONSTRAINT fk_task_comment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_tickets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_no VARCHAR(80) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NOT NULL,
  machine_model VARCHAR(150) NULL,
  machine_serial VARCHAR(150) NULL,
  issue_title VARCHAR(220) NOT NULL,
  issue_details TEXT NULL,
  warranty_type ENUM('warranty','non_warranty','amc') NOT NULL DEFAULT 'non_warranty',
  status ENUM('open','assigned','in_progress','waiting_parts','completed','cancelled') NOT NULL DEFAULT 'open',
  priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  assigned_engineer_id BIGINT UNSIGNED NULL,
  scheduled_date DATE NULL,
  resolution_note TEXT NULL,
  service_charge DECIMAL(14,2) NOT NULL DEFAULT 0,
  collection_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ticket_status (status),
  INDEX idx_ticket_customer (customer_id),
  CONSTRAINT fk_ticket_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_ticket_engineer FOREIGN KEY (assigned_engineer_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_ticket_created 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 ticket_parts_used (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty DECIMAL(14,3) NOT NULL,
  unit_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ticket_part_ticket FOREIGN KEY (ticket_id) REFERENCES service_tickets(id) ON DELETE CASCADE,
  CONSTRAINT fk_ticket_part_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  CONSTRAINT fk_ticket_part_user 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 invoices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(80) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NOT NULL,
  invoice_date DATE NOT NULL,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount DECIMAL(14,2) NOT NULL DEFAULT 0,
  vat DECIMAL(14,2) NOT NULL DEFAULT 0,
  transport_charge DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  due_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('draft','confirmed','cancelled') NOT NULL DEFAULT 'confirmed',
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_invoice_customer (customer_id),
  INDEX idx_invoice_date (invoice_date),
  CONSTRAINT fk_invoice_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_invoice_user 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 invoice_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  item_name VARCHAR(220) NOT NULL,
  qty DECIMAL(14,3) NOT NULL,
  unit VARCHAR(30) NOT NULL DEFAULT 'pcs',
  unit_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_invoice_item_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_invoice_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  invoice_id BIGINT UNSIGNED NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  payment_method ENUM('cash','bank','bkash','nagad','cheque','other') NOT NULL DEFAULT 'cash',
  note VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_payment_date (payment_date),
  CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payment_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  CONSTRAINT fk_payment_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- COLORJET Import, Warranty Repair, Supplier Ledger & Logistics
-- Converted from uploaded PHP package into Node.js API module.
-- Additive only: does not drop/alter existing business data.
-- =========================================================

CREATE TABLE IF NOT EXISTS module_registry (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  module_key VARCHAR(100) NOT NULL UNIQUE,
  display_name VARCHAR(180) NOT NULL,
  state ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS warranty_rmas (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  rma_number VARCHAR(80) NOT NULL UNIQUE,
  customer_name VARCHAR(191) NOT NULL,
  customer_phone VARCHAR(60) NULL,
  customer_id BIGINT UNSIGNED NULL,
  machine_model VARCHAR(191) NULL,
  machine_serial_number VARCHAR(191) NULL,
  installation_date DATE NULL,
  warranty_status VARCHAR(32) NOT NULL DEFAULT 'pending_verification',
  warranty_expiry_date DATE NULL,
  board_category VARCHAR(100) NULL,
  board_type VARCHAR(80) NOT NULL DEFAULT 'Other',
  board_serial_number VARCHAR(191) NULL,
  identify_number VARCHAR(191) NULL,
  supplier_id BIGINT UNSIGNED NULL,
  fault_description TEXT NULL,
  engineer_observation TEXT NULL,
  customer_complaint TEXT NULL,
  quantity DECIMAL(18,3) NOT NULL DEFAULT 1.000,
  unit VARCHAR(30) NOT NULL DEFAULT 'pcs',
  repair_estimate DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  replacement_estimate DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(12) NOT NULL DEFAULT 'USD',
  sending_method VARCHAR(50) NULL,
  carrier_name VARCHAR(191) NULL,
  tracking_number VARCHAR(191) NULL,
  airway_bill_number VARCHAR(191) NULL,
  estimated_supplier_received_date DATE NULL,
  actual_supplier_received_date DATE NULL,
  expected_return_date DATE NULL,
  status VARCHAR(64) NOT NULL DEFAULT 'draft',
  resolution_type VARCHAR(40) NULL,
  supplier_remarks TEXT NULL,
  internal_remarks TEXT NULL,
  sync_status VARCHAR(30) NOT NULL DEFAULT 'pending',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_rma_supplier_status (supplier_id,status),
  INDEX idx_rma_machine_serial (machine_serial_number),
  INDEX idx_rma_board_serial (board_serial_number),
  INDEX idx_rma_created_status (created_at,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS warranty_rma_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  rma_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  previous_status VARCHAR(64) NULL,
  new_status VARCHAR(64) NULL,
  note TEXT NULL,
  event_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BIGINT UNSIGNED NULL,
  INDEX idx_rma_events_rma_time (rma_id,event_at),
  INDEX idx_rma_events_status (new_status,event_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_shipments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shipment_number VARCHAR(80) NOT NULL UNIQUE,
  supplier_id BIGINT UNSIGNED NULL,
  related_lc_id BIGINT UNSIGNED NULL,
  related_purchase_order_id BIGINT UNSIGNED NULL,
  related_rma_id BIGINT UNSIGNED NULL,
  shipment_type VARCHAR(40) NOT NULL DEFAULT 'new_purchase',
  shipment_mode VARCHAR(40) NOT NULL DEFAULT 'sea_cargo',
  freight_responsibility VARCHAR(50) NOT NULL DEFAULT 'colorjet_paid',
  carrier_name VARCHAR(191) NULL,
  forwarder_name VARCHAR(191) NULL,
  tracking_number VARCHAR(191) NULL,
  airway_bill_number VARCHAR(191) NULL,
  bill_of_lading_number VARCHAR(191) NULL,
  container_number VARCHAR(191) NULL,
  vessel_name VARCHAR(191) NULL,
  china_port VARCHAR(191) NULL,
  departure_date DATE NULL,
  eta_chittagong DATE NULL,
  chittagong_arrival_date DATE NULL,
  port_status VARCHAR(60) NOT NULL DEFAULT 'not_arrived',
  cnf_agent VARCHAR(191) NULL,
  customs_status VARCHAR(60) NOT NULL DEFAULT 'not_started',
  local_transporter VARCHAR(191) NULL,
  warehouse_name VARCHAR(191) NULL,
  estimated_freight DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  actual_freight DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  freight_adjustment DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(12) NOT NULL DEFAULT 'USD',
  exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  status VARCHAR(64) NOT NULL DEFAULT 'draft',
  notes TEXT NULL,
  sync_status VARCHAR(30) NOT NULL DEFAULT 'pending',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_shipment_supplier_status (supplier_id,status),
  INDEX idx_shipment_eta (eta_chittagong,status),
  INDEX idx_shipment_rma (related_rma_id),
  INDEX idx_shipment_lc (related_lc_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_shipment_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shipment_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  rma_id BIGINT UNSIGNED NULL,
  product_name VARCHAR(191) NOT NULL,
  model VARCHAR(191) NULL,
  sku VARCHAR(191) NULL,
  supplier_part_number VARCHAR(191) NULL,
  identify_number VARCHAR(191) NULL,
  ordered_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  shipped_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  received_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  short_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  excess_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  damaged_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  qc_hold_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  warranty_replacement_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  free_of_cost_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  unit VARCHAR(30) NOT NULL DEFAULT 'pcs',
  purchase_value DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  weight DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  cbm DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_shipment_items_shipment (shipment_id),
  INDEX idx_shipment_items_product (product_id),
  INDEX idx_shipment_items_rma (rma_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_shipment_costs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shipment_id BIGINT UNSIGNED NOT NULL,
  cost_head VARCHAR(80) NOT NULL,
  description VARCHAR(500) NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(12) NOT NULL DEFAULT 'BDT',
  exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  bdt_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  allocation_method VARCHAR(40) NOT NULL DEFAULT 'by_value',
  is_manual_adjustment TINYINT(1) NOT NULL DEFAULT 0,
  approval_status VARCHAR(30) NOT NULL DEFAULT 'approved',
  approved_by BIGINT UNSIGNED NULL,
  entry_date DATE NOT NULL,
  attachment_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_shipment_costs_shipment_date (shipment_id,entry_date),
  INDEX idx_shipment_costs_head (cost_head),
  INDEX idx_shipment_costs_approval (approval_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_supplier_ledger_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  supplier_id BIGINT UNSIGNED NOT NULL,
  entry_date DATE NOT NULL,
  entry_type VARCHAR(60) NOT NULL,
  reference_type VARCHAR(60) NULL,
  reference_id BIGINT UNSIGNED NULL,
  reference_number VARCHAR(100) NULL,
  module_name VARCHAR(80) NOT NULL DEFAULT 'import_logistics',
  particulars TEXT NOT NULL,
  debit DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  credit DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(12) NOT NULL DEFAULT 'BDT',
  exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  bdt_debit DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  bdt_credit DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  attachment_id BIGINT UNSIGNED NULL,
  approval_status VARCHAR(30) NOT NULL DEFAULT 'approved',
  created_by BIGINT UNSIGNED NULL,
  approved_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reversed_at DATETIME NULL,
  reversal_of_id BIGINT UNSIGNED NULL,
  INDEX idx_import_supplier_ledger_supplier_date (supplier_id,entry_date,id),
  INDEX idx_import_supplier_ledger_reference (reference_type,reference_id),
  INDEX idx_import_supplier_ledger_status (approval_status,reversed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  receipt_number VARCHAR(80) NOT NULL UNIQUE,
  shipment_id BIGINT UNSIGNED NOT NULL,
  warehouse_name VARCHAR(191) NULL,
  received_date DATE NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'store_verification',
  store_note TEXT NULL,
  approval_note TEXT NULL,
  received_by BIGINT UNSIGNED NULL,
  approved_by BIGINT UNSIGNED NULL,
  posted_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  posted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_import_receipts_shipment_status (shipment_id,status),
  INDEX idx_import_receipts_date (received_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_receipt_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  receipt_id BIGINT UNSIGNED NOT NULL,
  shipment_item_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  received_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  short_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  excess_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  damaged_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  qc_hold_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  accepted_qty DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  bin_location VARCHAR(191) NULL,
  note TEXT NULL,
  posted_movement_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_import_receipt_items_receipt (receipt_id),
  INDEX idx_import_receipt_items_shipment_item (shipment_item_id),
  INDEX idx_import_receipt_items_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_discrepancies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shipment_id BIGINT UNSIGNED NOT NULL,
  shipment_item_id BIGINT UNSIGNED NULL,
  receipt_id BIGINT UNSIGNED NULL,
  supplier_id BIGINT UNSIGNED NULL,
  discrepancy_type VARCHAR(40) NOT NULL,
  quantity DECIMAL(18,3) NOT NULL DEFAULT 0.000,
  estimated_value DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(12) NOT NULL DEFAULT 'BDT',
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  claim_reference VARCHAR(100) NULL,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  resolved_by BIGINT UNSIGNED NULL,
  INDEX idx_import_discrepancy_supplier_status (supplier_id,status),
  INDEX idx_import_discrepancy_shipment (shipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_logistics_documents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(50) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 CHAR(64) NOT NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_import_documents_entity (entity_type,entity_id,deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_logistics_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  description TEXT NOT NULL,
  meta_json JSON NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_import_events_entity_time (entity_type,entity_id,created_at),
  INDEX idx_import_events_type_time (event_type,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_odoo_sync_queue (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  action_name VARCHAR(80) NOT NULL,
  payload_json JSON NOT NULL,
  sync_status VARCHAR(30) NOT NULL DEFAULT 'pending',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  UNIQUE KEY uq_import_odoo_sync (entity_type,entity_id,action_name),
  INDEX idx_import_odoo_queue_status (sync_status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO module_registry (module_key, display_name, state)
VALUES ('import_logistics', 'Import, Warranty & Supplier Logistics', 'active');