-- =====================================================
-- ACCOUNTING MODULE
-- =====================================================

-- TABLE: journal_entries
CREATE TABLE IF NOT EXISTS journal_entries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  journal_number VARCHAR(30) UNIQUE NOT NULL,
  date DATE NOT NULL DEFAULT CURRENT_DATE,
  description TEXT NOT NULL,
  type VARCHAR(30) DEFAULT 'automatic' CHECK (type IN ('automatic','manual')),
  reference_type VARCHAR(50), -- 'savings_transaction','loan_disbursement','loan_payment', etc.
  reference_id UUID,
  total_debit NUMERIC(15,2) NOT NULL DEFAULT 0,
  total_credit NUMERIC(15,2) NOT NULL DEFAULT 0,
  status VARCHAR(20) DEFAULT 'posted' CHECK (status IN ('draft','posted','reversed')),
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: journal_lines
CREATE TABLE IF NOT EXISTS journal_lines (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  journal_id UUID NOT NULL REFERENCES journal_entries(id),
  account_code VARCHAR(20) NOT NULL REFERENCES chart_of_accounts(code),
  description TEXT,
  debit NUMERIC(15,2) DEFAULT 0,
  credit NUMERIC(15,2) DEFAULT 0,
  line_order INTEGER DEFAULT 1,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- PPOB MODULE
-- =====================================================

-- TABLE: ppob_categories
CREATE TABLE IF NOT EXISTS ppob_categories (
  id SERIAL PRIMARY KEY,
  code VARCHAR(20) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  icon VARCHAR(50),
  is_active BOOLEAN DEFAULT true
);

-- TABLE: ppob_products
CREATE TABLE IF NOT EXISTS ppob_products (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES ppob_categories(id),
  code VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  price NUMERIC(15,2),  -- NULL = dynamic (based on customer number)
  margin NUMERIC(15,2) DEFAULT 1000, -- profit per transaction
  provider VARCHAR(50),
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0
);

-- TABLE: ppob_transactions
CREATE TABLE IF NOT EXISTS ppob_transactions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  transaction_code VARCHAR(30) UNIQUE NOT NULL,
  member_id UUID NOT NULL REFERENCES members(id),
  savings_account_id UUID REFERENCES savings_accounts(id), -- deducted from sukarela
  ppob_product_id INTEGER REFERENCES ppob_products(id),
  customer_number VARCHAR(100), -- meter number, phone number, etc.
  customer_name VARCHAR(150),
  amount NUMERIC(15,2) NOT NULL,
  admin_fee NUMERIC(15,2) DEFAULT 0,
  margin NUMERIC(15,2) DEFAULT 0,
  total_amount NUMERIC(15,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','success','failed','refunded')),
  provider_ref VARCHAR(100),
  payment_proof TEXT,
  processed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- E-COMMERCE (WASSERBA) MODULE
-- =====================================================

-- TABLE: product_categories
CREATE TABLE IF NOT EXISTS product_categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  is_active BOOLEAN DEFAULT true
);

-- TABLE: products
CREATE TABLE IF NOT EXISTS products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  category_id INTEGER REFERENCES product_categories(id),
  sku VARCHAR(50) UNIQUE,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  price NUMERIC(15,2) NOT NULL,
  stock INTEGER DEFAULT 0,
  unit VARCHAR(20) DEFAULT 'pcs',
  image_url TEXT,
  is_active BOOLEAN DEFAULT true,
  paylater_available BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: orders
CREATE TABLE IF NOT EXISTS orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  order_number VARCHAR(30) UNIQUE NOT NULL,
  member_id UUID NOT NULL REFERENCES members(id),
  total_amount NUMERIC(15,2) NOT NULL,
  payment_method VARCHAR(30) DEFAULT 'cash' CHECK (payment_method IN ('cash','savings','paylater')),
  paylater_tenor INTEGER, -- months if paylater
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','confirmed','processing','shipped','completed','cancelled')),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: order_items
CREATE TABLE IF NOT EXISTS order_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  order_id UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price NUMERIC(15,2) NOT NULL,
  subtotal NUMERIC(15,2) NOT NULL
);

-- =====================================================
-- COMMUNITY HUB MODULE
-- =====================================================

-- TABLE: community_fee_types
CREATE TABLE IF NOT EXISTS community_fee_types (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  amount NUMERIC(15,2) NOT NULL,
  frequency VARCHAR(20) DEFAULT 'monthly' CHECK (frequency IN ('monthly','quarterly','annual','once')),
  is_active BOOLEAN DEFAULT true
);

-- TABLE: community_fee_payments
CREATE TABLE IF NOT EXISTS community_fee_payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  payment_code VARCHAR(30) UNIQUE NOT NULL,
  member_id UUID NOT NULL REFERENCES members(id),
  fee_type_id INTEGER REFERENCES community_fee_types(id),
  amount NUMERIC(15,2) NOT NULL,
  period_year INTEGER,
  period_month INTEGER,
  status VARCHAR(20) DEFAULT 'paid',
  payment_date TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: donations
CREATE TABLE IF NOT EXISTS donations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title VARCHAR(200) NOT NULL,
  description TEXT,
  target_amount NUMERIC(15,2),
  collected_amount NUMERIC(15,2) DEFAULT 0,
  start_date DATE,
  end_date DATE,
  is_active BOOLEAN DEFAULT true,
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- TABLE: donation_contributions
CREATE TABLE IF NOT EXISTS donation_contributions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  donation_id UUID NOT NULL REFERENCES donations(id),
  member_id UUID NOT NULL REFERENCES members(id),
  amount NUMERIC(15,2) NOT NULL,
  message TEXT,
  is_anonymous BOOLEAN DEFAULT false,
  payment_date TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- VIRTUAL ACCOUNT / PAYMENT
-- =====================================================

-- TABLE: virtual_accounts
CREATE TABLE IF NOT EXISTS virtual_accounts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  va_number VARCHAR(30) UNIQUE NOT NULL,
  member_id UUID NOT NULL REFERENCES members(id),
  purpose VARCHAR(30) CHECK (purpose IN ('savings_deposit','loan_payment','ppob','community_fee','donation')),
  reference_id UUID,
  amount NUMERIC(15,2),
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active','paid','expired')),
  expired_at TIMESTAMPTZ,
  paid_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- SHU (Sisa Hasil Usaha)
-- =====================================================
CREATE TABLE IF NOT EXISTS shu_calculations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  year INTEGER NOT NULL,
  total_net_profit NUMERIC(15,2) NOT NULL,
  member_portion_rate NUMERIC(5,4) DEFAULT 0.40, -- 40% for members
  reserve_rate NUMERIC(5,4) DEFAULT 0.20,        -- 20% for reserve
  management_rate NUMERIC(5,4) DEFAULT 0.10,     -- 10% for management
  social_rate NUMERIC(5,4) DEFAULT 0.10,         -- 10% for social
  education_rate NUMERIC(5,4) DEFAULT 0.10,      -- 10% for education
  other_rate NUMERIC(5,4) DEFAULT 0.10,          -- 10% other
  status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft','approved','distributed')),
  calculated_by UUID REFERENCES users(id),
  approved_by UUID REFERENCES users(id),
  distributed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS shu_member_shares (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  shu_id UUID NOT NULL REFERENCES shu_calculations(id),
  member_id UUID NOT NULL REFERENCES members(id),
  modal_portion NUMERIC(15,2) DEFAULT 0,
  transaction_portion NUMERIC(15,2) DEFAULT 0,
  total_share NUMERIC(15,2) DEFAULT 0,
  is_distributed BOOLEAN DEFAULT false,
  distributed_at TIMESTAMPTZ
);

-- =====================================================
-- INDEXES
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_journal_entries_date ON journal_entries(date);
CREATE INDEX IF NOT EXISTS idx_journal_entries_reference ON journal_entries(reference_type, reference_id);
CREATE INDEX IF NOT EXISTS idx_journal_lines_journal ON journal_lines(journal_id);
CREATE INDEX IF NOT EXISTS idx_ppob_transactions_member ON ppob_transactions(member_id);
CREATE INDEX IF NOT EXISTS idx_orders_member ON orders(member_id);