-- Driver COD full-submission invoice workflow
-- Run manually only if you are not using Sequelize sync(). Safe with MySQL 8+.

CREATE TABLE IF NOT EXISTS driver_cod_settlement_invoices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  driver_id BIGINT UNSIGNED NOT NULL,
  invoice_no VARCHAR(80) NOT NULL,
  last_submission_at DATETIME NULL,
  submission_at DATETIME NOT NULL,
  period_start DATETIME NULL,
  period_end DATETIME NOT NULL,
  order_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_cod_amount DECIMAL(14,3) NOT NULL DEFAULT 0,
  submitted_amount DECIMAL(14,3) NOT NULL DEFAULT 0,
  payment_method ENUM('CASH','BANK','CARD','WALLET','OTHER') NOT NULL DEFAULT 'CASH',
  status ENUM('DRAFT','GENERATED','SETTLED','CANCELLED') NOT NULL DEFAULT 'SETTLED',
  settlement_reference_no VARCHAR(100) NULL,
  remarks VARCHAR(1000) NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  is_delete TINYINT NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  deleted_by BIGINT UNSIGNED NULL,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY idx_dcod_inv_no (tenant_id, invoice_no, is_delete),
  KEY idx_dcod_inv_drv (tenant_id, driver_id, submission_at, is_delete),
  KEY idx_dcod_inv_stat (tenant_id, status, is_delete)
);

CREATE TABLE IF NOT EXISTS driver_cod_settlement_invoice_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  driver_cod_settlement_invoice_id BIGINT UNSIGNED NOT NULL,
  cod_collection_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  driver_id BIGINT UNSIGNED NOT NULL,
  order_no VARCHAR(80) NULL,
  awb_no VARCHAR(80) NULL,
  customer_name VARCHAR(150) NULL,
  cod_amount DECIMAL(14,3) NOT NULL DEFAULT 0,
  collected_amount DECIMAL(14,3) NOT NULL DEFAULT 0,
  received_amount DECIMAL(14,3) NOT NULL DEFAULT 0,
  collected_at DATETIME NULL,
  delivery_status VARCHAR(80) NULL,
  failure_reason VARCHAR(300) NULL,
  driver_remarks VARCHAR(1000) NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  is_delete TINYINT NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  deleted_by BIGINT UNSIGNED NULL,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_dcod_item_inv (tenant_id, driver_cod_settlement_invoice_id, is_delete),
  KEY idx_dcod_item_order (tenant_id, order_id, is_delete),
  KEY idx_dcod_item_col (tenant_id, cod_collection_id, is_delete)
);
