table Education {
  id bigint pk notnull inc
  label varchar(200) notnull
}

table YearlyIncome {
  id bigint pk notnull inc
  label varchar(200) notnull
}

table SourceOfIncome {
  id bigint pk notnull inc
  label varchar(200) notnull
}

table Bank {
  id bigint pk notnull inc
  logo_url varchar(200) notnull
  name varchar(200) notnull
}

table BloodType {
  id bigint pk notnull inc
  label varchar(200) notnull
}

table Religion {
  id bigint pk notnull inc
  label varchar(200) notnull
}

table MaritalStatus {
  id bigint pk notnull inc
  label varchar(200) notnull
}

table Occupation {
  id bigint pk notnull inc
  label varchar(200) notnull
}

table Country {
  id bigint pk notnull inc
  flag_url varchar(200) notnull
  code varchar(5) notnull
  name varchar(200) notnull
}

table Currency {
  id bigint pk notnull inc
  id_country Country.id notnull
  name varchar(50) notnull
  symbol varchar(10) notnull
}

table Provinsi {
  id bigint pk notnull inc
  name varchar(200) notnull
}

table KotaKabupaten {
  id bigint pk notnull inc
  id_provinsi Provinsi.id notnull
  name varchar(200) notnull
}

table Kecamatan {
  id bigint pk notnull inc
  id_kota_kabupaten KotaKabupaten.id notnull
  name varchar(200) notnull
}

table DesaKelurahan {
  id bigint pk notnull inc
  id_kecamatan Kecamatan.id notnull
  name varchar(200) notnull
  kodepos varchar(5)
}

table HeirType {
  id bigint pk notnull inc
  label varchar(200) notnull
}


table InvestmentManager {
  id bigint pk inc notnull
  logo_url varchar(200) notnull
  name varchar(200) notnull
  registration_number varchar(200)
  description text
  address text
  permit_number varchar(200)
  last_aum bigint

  created_at timestamp notnull
}

table MFType {
  id bigint pk inc notnull
  icon_url varchar(200) notnull
  label varchar(100) notnull
  created_at timestamp notnull
}

table MutualFund {
  id bigint pk inc notnull
  id_invement_manager InvestmentManager.id notnull
  id_custody_bank Bank.id notnull
  id_type MFType.id notnull
  id_currency Currency.id notnull
  is_sharia boolean notnull
  is_etf boolean notnull default=false
  is_index boolean notnull default=false

  name varchar(500) notnull
  description text

  // fee usually written in text like "Max 0.25%"
  custody_bank_fee varchar(100)
  investment_manager_fee varchar(100)
  subscription_fee varchar(100)
  redemption_fee varchar(100)
  switching_fee varchar(100)

  // Net Asset Value
  nav bigint notnull
  nav_date date notnull
  nav_change float notnull

  // Assets Under Management
  aum bigint notnull

  ipo_date date notnull

  minimum_subscription bigint notnull
  minimum_redemption bigint notnull
  minimum_switching bigint notnull

  sinvest_code varchar(50) notnull

  // this equals to nav_change
  day_return float

  week_return float
  month_return float
  half_year_return float
  year_return float
  three_year_return float
  five_year_return float

  latest_propectus_url varchar(200) notnull
  latest_fund_fact_sheet_url varchar(200) notnull

  created_at timestamp notnull
}

table DailyNAV {
  id bigint pk inc notnull
  id_mutual_fund MutualFund.id notnull
  nav bigint notnull
  date date notnull
}

table MonthlyAUM {
  id bigint pk inc notnull
  id_mutual_fund MutualFund.id notnull
  aum bigint notnull
  date date notnull
}

table MutualFundPortfolio {
  id bigint pk inc notnull
  id_mutual_fund MutualFund.id notnull
  name varchar(200) notnull
  percentage float notnull
}


table Portfolio {
  id bigint pk notnull inc
  id_user User.id notnull
  id_mutual_fund MutualFund.id notnull
  unit bigint notnull default=0
  created_at timestamp notnull
}


table PaymentMethod {
  id bigint pk inc notnull

  // identifier for developer to
  // show correct payment method
  // screen
  code varchar(10) notnull

  logo_url varchar(255) notnull
  name varchar(255) notnull
  created_at timestamp notnull
}

table Transaction {
  id bigint pk inc notnull
  id_user User.id notnull
  invoice_number varchar(200) notnull
}

enum SubscriptionStatus {
  Baru
  Diproses
  PembayaranGagal
  VerifikasiMI
  VerifikasiBankKustodian
  Selesai
}

table Subscription {
  id bigint pk inc notnull
  id_transaction Transaction.id notnull
  id_mutual_fund MutualFund.id notnull
  id_payment_method PaymentMethod.id notnull

  status SubscriptionStatus notnull default=SubscriptionStatus.Baru

  // amount in currency
  amount bigint notnull

  // actual NAV when subscription done
  nav bigint

  created_at timestamp notnull
}

table SubscriptionStatusHistory {
  id bigint pk notnull inc
  id_subscription Subscription.id notnull
  status SubscriptionStatus notnull
  notes text
  created_at timestamp notnull
}

table Redemption {
  id bigint pk inc notnull
  id_transaction Transaction.id notnull
  id_portfolio Portfolio.id notnull
  status RedemptionStatus notnull default=RedemptionStatus.Diproses

  // redemption unit
  unit bigint notnull

  // actual NAV when redemption done
  nav bigint

  created_at timestamp notnull
}

enum RedemptionStatus {
  Diproses
  Gagal
  VerifikasiMI
  DalamPengirimanBK
  Selesai
}

table RedemptionStatusHistory {
  id bigint pk notnull inc
  id_redemption Redemption.id notnull
  status RedemptionStatus notnull
  notes text
  created_at timestamp notnull
}

enum SwitchingStatus {
  Diproses
  Gagal
  Selesai
}

table Switching {
  id bigint pk inc notnull
  id_transaction Transaction.id notnull
  id_portfolio_from Portfolio.id notnull
  id_mf_switch_to MutualFund.id notnull
  status RedemptionStatus notnull

  // total unit portfolio that being switched
  unit_switched bigint notnull

  // total received unit switched to new mutua fund
  unit_received bigint

  // actual NAV when switching done
  nav_switched bigint
  nav_received bigint

  created_at timestamp notnull
}

table SwitchingStatusHistory {
  id bigint pk inc notnull
  id_switching Switching.id notnull
  status SwitchingStatus notnull
  notes text
  created_at timestamp notnull
}


table SMSOTP {
  id bigint pk inc notnull

  // trace who sent the OTP
  id_registration_user RegistrationUser.id
  id_user User.id

  phone_number varchar(20) notnull
  code varchar(10) notnull
  text text notnull
  sent_at timestamp
  created_at timestamp notnull
}

table EmailOTP {
  id bigint pk inc notnull

  // trace who sent the OTP
  id_user User.id
  id_registration_user RegistrationUser.id

  email varchar(200) notnull
  code varchar(10) notnull
  sent_at timestamp
  created_at timestamp notnull
}

table RegistrationUser {
  id bigint pk inc notnull

  // unique code identifier for registration
  // different on each register.
  // On mobile apps, the app stores this uuid
  // to resume registration process.
  registration_uuid varchar(36) notnull

  // When user complete the registration
  // new User record will be created.
  // This derivative column, true after
  // new User record created.
  has_complete_registration boolean default=false

  email varchar(200) notnull
  phone_number varchar(20) notnull

  id_last_education Education.id
  id_yearly_income YearlyIncome.id
  id_source_of_income SourceOfIncome.id
  id_bank Bank.id

  bank_account_number varchar(100)
  bank_account_branch varchar(100)

  // the name of people who has the bank account
  bank_account_name varchar(200)

  signature_image_url varchar(200)
  pin_hash varchar(100)

  tax_card_image_url varchar(100)
  tax_number varchar(100)

  id_occupation Occupation.id
  occupation_position varchar(500)
  job_company_name varchar(500)
  job_company_phone_number varchar(20)
  job_company_email varchar(200)
  job_company_address text

  created_at timestamp notnull
}

enum JenisKelamin {
  L
  P
}

table NationalIDCardData {
  id bigint pk notnull inc
  id_registration_user RegistrationUser.id notnull
  id_card_photo_url varchar(200) notnull
  selfie_with_id_card_photo_url varchar(200) notnull
  nik varchar(16) notnull
  nama varchar(200) notnull
  tempat_lahir varchar(100) notnull
  tanggal_lahir_yyyymmdd varchar(8) notnull
  jenis_kelamin JenisKelamin notnull
  id_blood_type BloodType.id notnull
  id_religion Religion.id notnull
  id_marital_status MaritalStatus.id notnull

  // Address on National ID Card
  id_provinsi Provinsi.id notnull
  id_kota_kabupaten KotaKabupaten.id notnull
  id_kecamatan Kecamatan.id notnull
  id_desa_kelurahan DesaKelurahan.id notnull
  rt_rw text
  alamat text notnull

  id_nationality Country.id notnull
  berlaku_sampai_yyyymmdd varchar(8) notnull

  // Living address
  id_provinsi_dom Provinsi.id notnull
  id_kota_kabupaten_dom KotaKabupaten.id notnull
  id_kecamatan_dom Kecamatan.id notnull
  id_desa_kelurahan_dom DesaKelurahan.id notnull
  rt_rw_dom text
  alamat_dom text notnull

  created_at timestamp notnull
}

table InheritanceData {
  id bigint pk inc notnull
  id_registration_user RegistrationUser.id notnull
  fullname varchar(200) notnull
  type HeirType.id notnull
}

enum RQQuestionType {
  MULTIPLE_CHOICES
  WRITE_ANSWER
}

table RegistrationQuestionnaireQuestion {
  id bigint pk inc notnull
  active boolean default=true
  question text notnull
  type RQQuestionType notnull
  order int notnull default=0
  created_at timestamp notnull
}

table RQQuestionAnswer {
  id bigint pk inc notnull
  id_rq_question RegistrationQuestionnaireQuestion.id notnull
  order int notnull default=0
  answer text notnull
  created_at timestamp notnull
}

table UserQuestionnaireAnswer {
  id bigint pk inc notnull
  id_rq_question RegistrationQuestionnaireQuestion.id notnull
  id_registration_user RegistrationUser.id notnull
  answer text notnull
  created_at timestamp notnull
}

table User {
  id bigint pk inc notnull
  id_registration_user RegistrationUser.id notnull

  // email and phone number copied from 
  // RegistrationUser record
  email varchar(200) notnull
  phone_number varchar(20) notnull
  pin_hash varchar(100) notnull
}
