-- =====================================================
-- SAVINGS MODULE
-- =====================================================

-- TABLE: savings_products
CREATE TABLE IF NOT EXISTS savings_products (
  id SERIAL PRIMARY KEY,
  code VARCHAR(20) UNIQUE NOT NULL,
  name VARCHAR(150) NOT NULL,
  type VARCHAR(20) NOT NULL CHECK (type IN ('pokok','wajib','sukarela')),
  description TEXT,
  interest_rate NUMERIC(5,4) DEFAULT 0, -- monthly interest rate (e.g. 0.005 = 0.5%/month)
  min_balance NUMERIC(15,2) DEFAULT 0,
  withdrawal_fee NUMERIC(15,2) DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: savings_accounts
CREATE TABLE IF NOT EXISTS savings_accounts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_number VARCHAR(30) UNIQUE NOT NULL,
  member_id UUID NOT NULL REFERENCES members(id),
  product_id INTEGER NOT NULL REFERENCES savings_products(id),
  balance NUMERIC(15,2) DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  opened_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: savings_transactions
CREATE TABLE IF NOT EXISTS savings_transactions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  transaction_code VARCHAR(30) UNIQUE NOT NULL,
  account_id UUID NOT NULL REFERENCES savings_accounts(id),
  member_id UUID NOT NULL REFERENCES members(id),
  type VARCHAR(20) NOT NULL CHECK (type IN ('deposit','withdrawal','interest','transfer_in','transfer_out','admin_fee')),
  amount NUMERIC(15,2) NOT NULL,
  balance_before NUMERIC(15,2) NOT NULL,
  balance_after NUMERIC(15,2) NOT NULL,
  description TEXT,
  reference_id UUID, -- links to journal entry
  processed_by UUID REFERENCES users(id),
  transaction_date TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- LOAN MODULE
-- =====================================================

-- TABLE: loan_products
CREATE TABLE IF NOT EXISTS loan_products (
  id SERIAL PRIMARY KEY,
  code VARCHAR(20) UNIQUE NOT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  max_amount NUMERIC(15,2) NOT NULL DEFAULT 50000000,
  max_tenor_months INTEGER NOT NULL DEFAULT 36,
  interest_rate NUMERIC(5,4) NOT NULL DEFAULT 0.015, -- monthly rate
  admin_fee_rate NUMERIC(5,4) DEFAULT 0.005,
  provision_fee_rate NUMERIC(5,4) DEFAULT 0.005,
  calculation_type VARCHAR(20) DEFAULT 'flat' CHECK (calculation_type IN ('flat','efektif','anuitas')),
  requires_collateral BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: loan_applications
CREATE TABLE IF NOT EXISTS loan_applications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  application_number VARCHAR(30) UNIQUE NOT NULL,
  member_id UUID NOT NULL REFERENCES members(id),
  product_id INTEGER NOT NULL REFERENCES loan_products(id),
  amount_requested NUMERIC(15,2) NOT NULL,
  tenor_months INTEGER NOT NULL,
  purpose TEXT NOT NULL,
  ktp_photo_url TEXT,
  salary_slip_url TEXT,
  credit_score NUMERIC(5,2), -- calculated: total simpanan x 3
  status VARCHAR(30) DEFAULT 'pending' CHECK (status IN ('pending','review','approved','rejected','disbursed','completed')),
  rejection_reason TEXT,
  reviewed_by UUID REFERENCES users(id),
  reviewed_at TIMESTAMPTZ,
  approved_by UUID REFERENCES users(id),
  approved_at TIMESTAMPTZ,
  disbursed_by UUID REFERENCES users(id),
  disbursed_at TIMESTAMPTZ,
  amount_approved NUMERIC(15,2),
  admin_fee NUMERIC(15,2) DEFAULT 0,
  provision_fee NUMERIC(15,2) DEFAULT 0,
  amount_disbursed NUMERIC(15,2),
  interest_rate NUMERIC(5,4),
  calculation_type VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: loan_schedules (amortization)
CREATE TABLE IF NOT EXISTS loan_schedules (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  loan_id UUID NOT NULL REFERENCES loan_applications(id),
  installment_number INTEGER NOT NULL,
  due_date DATE NOT NULL,
  principal_amount NUMERIC(15,2) NOT NULL,
  interest_amount NUMERIC(15,2) NOT NULL,
  total_payment NUMERIC(15,2) NOT NULL,
  paid_amount NUMERIC(15,2) DEFAULT 0,
  paid_at TIMESTAMPTZ,
  status VARCHAR(20) DEFAULT 'unpaid' CHECK (status IN ('unpaid','paid','partial','overdue')),
  days_overdue INTEGER DEFAULT 0,
  collectibility VARCHAR(20) DEFAULT 'lancar' CHECK (collectibility IN ('lancar','dalam_perhatian','kurang_lancar','diragukan','macet')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: loan_payments
CREATE TABLE IF NOT EXISTS loan_payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  payment_code VARCHAR(30) UNIQUE NOT NULL,
  loan_id UUID NOT NULL REFERENCES loan_applications(id),
  schedule_id UUID REFERENCES loan_schedules(id),
  member_id UUID NOT NULL REFERENCES members(id),
  amount NUMERIC(15,2) NOT NULL,
  principal_portion NUMERIC(15,2) DEFAULT 0,
  interest_portion NUMERIC(15,2) DEFAULT 0,
  payment_method VARCHAR(30) DEFAULT 'cash',
  reference_id UUID,
  processed_by UUID REFERENCES users(id),
  payment_date TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- INDEXES
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_savings_accounts_member ON savings_accounts(member_id);
CREATE INDEX IF NOT EXISTS idx_savings_transactions_account ON savings_transactions(account_id);
CREATE INDEX IF NOT EXISTS idx_loan_applications_member ON loan_applications(member_id);
CREATE INDEX IF NOT EXISTS idx_loan_applications_status ON loan_applications(status);
CREATE INDEX IF NOT EXISTS idx_loan_schedules_loan ON loan_schedules(loan_id);
CREATE INDEX IF NOT EXISTS idx_loan_schedules_due ON loan_schedules(due_date, status);