-- ResellBD Step 17: reseller/supplier onboarding and future finance foundations.
-- Apply ONLY to the NEW ResellBD database after migrations 001-008 and seed 001.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS resellers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  business_name VARCHAR(190) NOT NULL,
  business_type ENUM('individual','sole_proprietorship','company') NOT NULL DEFAULT 'individual',
  national_id_last4 VARCHAR(4) NULL,
  district VARCHAR(100) NOT NULL,
  address VARCHAR(500) NOT NULL,
  social_page_url VARCHAR(500) NULL,
  expected_monthly_orders INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  application_note VARCHAR(1000) NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_resellers_user (user_id),
  KEY idx_resellers_status_created (status,created_at),
  CONSTRAINT fk_resellers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_resellers_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS suppliers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  business_name VARCHAR(190) NOT NULL,
  contact_person VARCHAR(160) NOT NULL,
  business_type ENUM('individual','sole_proprietorship','company') NOT NULL DEFAULT 'individual',
  trade_license_no VARCHAR(120) NULL,
  district VARCHAR(100) NOT NULL,
  address VARCHAR(500) NOT NULL,
  product_categories VARCHAR(500) NOT NULL,
  fulfilment_method ENUM('self_ship','platform_courier','both') NOT NULL DEFAULT 'both',
  status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  application_note VARCHAR(1000) NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_suppliers_user (user_id),
  KEY idx_suppliers_status_created (status,created_at),
  CONSTRAINT fk_suppliers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_suppliers_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Foundation only: financial calculations and postings are intentionally implemented in Step 20.
CREATE TABLE IF NOT EXISTS commissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reseller_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  order_item_id BIGINT UNSIGNED NULL,
  base_amount_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
  commission_amount_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('pending','eligible','reversed','paid') NOT NULL DEFAULT 'pending',
  rule_snapshot JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_commissions_reseller_status (reseller_id,status), KEY idx_commissions_order (order_id),
  CONSTRAINT fk_commissions_reseller FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS payouts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reseller_id BIGINT UNSIGNED NOT NULL,
  amount_paisa BIGINT UNSIGNED NOT NULL,
  method ENUM('bkash','nagad','rocket','bank_transfer','other') NOT NULL,
  destination_masked VARCHAR(100) NULL,
  status ENUM('requested','under_review','approved','paid','rejected','cancelled') NOT NULL DEFAULT 'requested',
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  paid_at DATETIME NULL,
  admin_note VARCHAR(1000) NULL,
  PRIMARY KEY (id), KEY idx_payouts_reseller_status (reseller_id,status), KEY idx_payouts_status_date (status,requested_at),
  CONSTRAINT fk_payouts_reseller FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_payouts_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Append-only double-entry ledger foundation. No financial posting UI/process is included in Step 17.
CREATE TABLE IF NOT EXISTS ledger_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  transaction_key CHAR(36) NOT NULL,
  account_code VARCHAR(80) NOT NULL,
  debit_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
  credit_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
  reference_type VARCHAR(64) NULL,
  reference_id BIGINT UNSIGNED NULL,
  description VARCHAR(500) NOT NULL,
  reversal_of_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_ledger_transaction (transaction_key), KEY idx_ledger_account_date (account_code,created_at), KEY idx_ledger_reference (reference_type,reference_id),
  CONSTRAINT fk_ledger_reversal FOREIGN KEY (reversal_of_id) REFERENCES ledger_entries(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_ledger_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT chk_ledger_one_side CHECK ((debit_paisa > 0 AND credit_paisa = 0) OR (credit_paisa > 0 AND debit_paisa = 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;