db.dbml

// DBML schema for finance contract-related tables
// Pattern: Class Table Inheritance — base tables + per-type extension tables

// ============================================================
// EXTERNAL / REFERENCE TABLES
// ============================================================

Table investments {
  id bigint [pk, increment]
  ext_investment_id varchar [note: 'External investment ID']
  name varchar(255) [not null, note: 'Season name']
  start_year bigint [default: 0, note: 'Season start year']
  end_year bigint [default: 0, note: 'Season end year']
  status smallint [not null, default: 1, note: 'Season status']
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

Table hubs {
  id integer [pk, increment]
  bu_id integer [not null, note: 'Reference to bus.id']
  code varchar [unique, not null]
  name varchar [not null]
  address text
  created_at timestamp
  updated_at timestamp
}

Table bus {
  id integer [pk, increment]
  code varchar [unique, not null]
  name varchar [not null]
  address text
  created_at timestamp
  updated_at timestamp
}

Table frm_framers {
  id integer [primary key]
}

// ============================================================
// BASE CONTRACT TABLE
// ============================================================

Table main_contracts {
  id bigint [pk, increment]
  code varchar [not null, unique, note: 'Auto-generated contract code']
  name varchar [note: 'Contract name']
  ext_id varchar [note: 'External contract ID']
  investment_id bigint [note: 'Reference to investments.id']
  type varchar [note: 'investment | land_lease | equipment_investment']
  heir_info jsonb
  info jsonb
  value numeric
  authorized_person varchar
  heir_person varchar
  guarantor_person varchar
  representative_person varchar
  frm_farmer_id integer [note: 'Reference to FRM farmer table (planned)']
  bu_code varchar [note: 'Reference to bus.code']
  scf_contract_id integer [note: 'Reference to SCF contract table (planned)']
  status varchar
  approval_info jsonb
  hub_code varchar [note: 'Reference to hubs.code (external table)']
  officer_name varchar [note: 'Reference to agriculture officer (external table)']
  signed_at timestamp
  started_at timestamp
  end_at timestamp
  note text
  cancelled_at timestamp
  cancelled_reason text
  is_deleted boolean [not null, default: false, note: 'Soft delete flag']
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

// ============================================================
// CONTRACT EXTENSION TABLES (1:1 with main_contracts)
// ============================================================

Table contract_investment_ext {
  id bigint [pk, increment]
  main_contract_id bigint [not null, unique, note: 'FK → main_contracts.id']
  erp_id integer [note: 'Reference to ERP table (planned)']
  plant_type varchar
  completed_status varchar
  completed_at timestamp
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

Table contract_land_lease_ext {
  id bigint [pk, increment]
  main_contract_id bigint [not null, unique, note: 'FK → main_contracts.id']
  actual_leased_area numeric
  contract_area numeric
  total_land_rent numeric
  debt_info jsonb [note: 'Debt information']
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

Table contract_equipment_ext {
  id bigint [pk, increment]
  main_contract_id bigint [not null, unique, note: 'FK → main_contracts.id']
  minimum_deduction_rate numeric
  refundable_amount numeric
  non_refundable_amount numeric
  minimum_deduction_amount numeric
  total_contract_value numeric
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

// ============================================================
// BASE CONTRACT ITEMS TABLE
// ============================================================

Table contract_items {
  id bigint [pk, increment]
  code varchar [not null, unique, note: 'Auto-generated contract item code']
  name varchar(100) [not null, note: 'Contract item name']
  ext_id varchar [not null, note: 'External ID']
  main_contract_id bigint [not null, note: 'FK → main_contracts.id']
  parent_item_id bigint [note: 'appendix→sugarcane_detail (1:1 unique); land_parcel_detail→land_lease_detail (1:many)']
  type varchar [not null, note: 'sugarcane_detail | appendix | land_lease_detail | land_parcel_detail | equipment_detail']
  bu_code varchar [note: 'Reference to bus.code']
  hub_code varchar [note: 'Reference to hubs.code']
  status varchar
  is_deleted boolean [not null, default: false, note: 'Soft delete flag']
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

// ============================================================
// ITEM EXTENSION TABLES (1:1 with contract_items)
// ============================================================

Table contract_item_sugarcane_ext {
  id bigint [pk, increment]
  contract_item_id bigint [not null, unique, note: 'FK → contract_items.id']
  parcel_land_id varchar [not null, note: 'Reference to parcel_lands table (planned)']
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

Table contract_item_land_parcel_ext {
  id bigint [pk, increment]
  contract_item_id bigint [not null, unique, note: 'FK → contract_items.id']
  parcel_land_id integer [note: 'Reference to parcel_lands table (planned)']
  created_at timestamp [default: `current_timestamp`]
  updated_at timestamp [default: `current_timestamp`]
}

// ============================================================
// RELATIONSHIPS
// ============================================================

// External references
Ref hubs_to_bus: hubs.bu_id > bus.id

// Contracts base → external
Ref main_contracts_to_hubs: main_contracts.hub_code > hubs.code
Ref main_contracts_to_bus: main_contracts.bu_code > bus.code
Ref main_contracts_to_frm: main_contracts.frm_farmer_id > frm_framers.id
Ref main_contracts_to_investments: main_contracts.investment_id > investments.id

// Contract extensions → base (1:1)
Ref contract_investment_ext_to_main_contracts: contract_investment_ext.main_contract_id - main_contracts.id
Ref contract_land_lease_ext_to_main_contracts: contract_land_lease_ext.main_contract_id - main_contracts.id
Ref contract_equipment_ext_to_main_contracts: contract_equipment_ext.main_contract_id - main_contracts.id

// Contract items → main_contracts
Ref contract_items_to_main_contracts: contract_items.main_contract_id > main_contracts.id
Ref contract_items_to_bus: contract_items.bu_code > bus.code
Ref contract_items_to_hubs: contract_items.hub_code > hubs.code

// Contract items self-reference (parent hierarchy)
Ref contract_items_to_parent: contract_items.parent_item_id > contract_items.id

// Item extensions → base
Ref contract_item_sugarcane_ext_to_items: contract_item_sugarcane_ext.contract_item_id - contract_items.id
Ref contract_item_land_parcel_ext_to_items: contract_item_land_parcel_ext.contract_item_id - contract_items.id

// External/planned references (tables not in this file):
// - main_contracts.scf_contract_id -> scf_contracts.id (planned)
// - contract_investment_ext.erp_id -> erps.id (planned)
// - contract_item_sugarcane_ext.parcel_land_id -> parcel_lands.id (planned)
// - contract_item_land_parcel_ext.parcel_land_id -> parcel_lands.id (planned)