-- =========================================================
-- 033_monthly_loans_foundation.sql
-- Sprint 13: Peminjaman Bulanan foundation
-- =========================================================

CREATE TABLE IF NOT EXISTS loan_monthly_partners (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  cluster VARCHAR(120) NULL,
  note VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_loan_monthly_partners_location_active (location_id, is_active),
  KEY idx_loan_monthly_partners_name (location_id, name),
  CONSTRAINT fk_loan_monthly_partners_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_monthly_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  partner_id BIGINT UNSIGNED NOT NULL,
  transaction_date DATE NOT NULL,
  period_ym CHAR(7) NOT NULL,
  direction ENUM('main_to_partner','partner_to_main') NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  unit_name_snapshot VARCHAR(40) NULL,
  qty DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  allow_decimal_qty TINYINT(1) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  item_status ENUM('belum_diganti','diganti') NOT NULL DEFAULT 'belum_diganti',
  payment_status ENUM('belum_dibayar','dibayar') NOT NULL DEFAULT 'belum_dibayar',
  handover_from VARCHAR(120) NULL,
  handover_to VARCHAR(120) NULL,
  handover_at DATETIME NULL,
  source ENUM('manual','landing','carry_over') NOT NULL DEFAULT 'manual',
  carry_over_from_transaction_id BIGINT UNSIGNED NULL,
  carry_over_from_period CHAR(7) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_by_name_snapshot VARCHAR(120) NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  updated_by_name_snapshot VARCHAR(120) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_loan_monthly_transactions_period (location_id, partner_id, period_ym),
  KEY idx_loan_monthly_transactions_date (location_id, transaction_date),
  KEY idx_loan_monthly_transactions_status (location_id, item_status, payment_status),
  KEY idx_loan_monthly_transactions_carry (carry_over_from_transaction_id),
  CONSTRAINT fk_loan_monthly_transactions_partner FOREIGN KEY (partner_id) REFERENCES loan_monthly_partners(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_loan_monthly_transactions_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_loan_monthly_transactions_carry FOREIGN KEY (carry_over_from_transaction_id) REFERENCES loan_monthly_transactions(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_monthly_closings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  partner_id BIGINT UNSIGNED NOT NULL,
  period_ym CHAR(7) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status ENUM('draft','locked') NOT NULL DEFAULT 'draft',
  landing_token VARCHAR(80) NOT NULL,
  report_note TEXT NULL,
  qr_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_by_name_snapshot VARCHAR(120) NULL,
  locked_by_user_id BIGINT UNSIGNED NULL,
  locked_by_name_snapshot VARCHAR(120) NULL,
  locked_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_loan_monthly_closings_period (location_id, partner_id, period_ym),
  UNIQUE KEY uq_loan_monthly_closings_token (landing_token),
  KEY idx_loan_monthly_closings_status (location_id, status),
  CONSTRAINT fk_loan_monthly_closings_partner FOREIGN KEY (partner_id) REFERENCES loan_monthly_partners(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_monthly_closing_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  closing_id BIGINT UNSIGNED NOT NULL,
  transaction_id BIGINT UNSIGNED NOT NULL,
  transaction_date DATE NOT NULL,
  period_ym CHAR(7) NOT NULL,
  direction ENUM('main_to_partner','partner_to_main') NOT NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  unit_name_snapshot VARCHAR(40) NULL,
  qty DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  allow_decimal_qty TINYINT(1) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  item_status ENUM('belum_diganti','diganti') NOT NULL DEFAULT 'belum_diganti',
  payment_status ENUM('belum_dibayar','dibayar') NOT NULL DEFAULT 'belum_dibayar',
  handover_from VARCHAR(120) NULL,
  handover_to VARCHAR(120) NULL,
  handover_at DATETIME NULL,
  source ENUM('manual','landing','carry_over') NOT NULL DEFAULT 'manual',
  carry_over_from_period CHAR(7) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_loan_monthly_closing_item_pair (closing_id, transaction_id),
  KEY idx_loan_monthly_closing_items_status (closing_id, item_status, payment_status),
  CONSTRAINT fk_loan_monthly_closing_items_closing FOREIGN KEY (closing_id) REFERENCES loan_monthly_closings(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_loan_monthly_closing_items_transaction FOREIGN KEY (transaction_id) REFERENCES loan_monthly_transactions(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loan_monthly_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  partner_id BIGINT UNSIGNED NULL,
  transaction_id BIGINT UNSIGNED NULL,
  closing_id BIGINT UNSIGNED NULL,
  source ENUM('transaction','landing','closing','carry_over') NOT NULL DEFAULT 'transaction',
  action VARCHAR(60) NOT NULL,
  transaction_date DATE NULL,
  period_ym CHAR(7) NULL,
  direction ENUM('main_to_partner','partner_to_main') NULL,
  product_name_snapshot VARCHAR(180) NULL,
  qty DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  allow_decimal_qty TINYINT(1) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  item_status ENUM('belum_diganti','diganti') NULL,
  handover_from VARCHAR(120) NULL,
  handover_to VARCHAR(120) NULL,
  handover_at DATETIME NULL,
  meta_json TEXT NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_loan_monthly_logs_period (location_id, period_ym),
  KEY idx_loan_monthly_logs_status (location_id, item_status),
  KEY idx_loan_monthly_logs_transaction (transaction_id),
  CONSTRAINT fk_loan_monthly_logs_partner FOREIGN KEY (partner_id) REFERENCES loan_monthly_partners(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_loan_monthly_logs_transaction FOREIGN KEY (transaction_id) REFERENCES loan_monthly_transactions(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_loan_monthly_logs_closing FOREIGN KEY (closing_id) REFERENCES loan_monthly_closings(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;