-- =====================================================
-- KOPERASI DIGITAL - DATABASE SCHEMA
-- PostgreSQL 14+
-- =====================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =====================================================
-- TABLE: cooperative_profile
-- =====================================================
CREATE TABLE IF NOT EXISTS cooperative_profile (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL DEFAULT 'Koperasi Digital',
  tagline VARCHAR(300),
  logo_url TEXT,
  address TEXT,
  phone VARCHAR(20),
  email VARCHAR(100),
  npwp VARCHAR(30),
  badan_hukum VARCHAR(100),
  established_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- TABLE: users
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(150) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'member' CHECK (role IN ('super_admin','admin','manager','cashier','member')),
  full_name VARCHAR(150) NOT NULL,
  phone VARCHAR(20),
  is_active BOOLEAN DEFAULT true,
  last_login TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- TABLE: members
-- =====================================================
CREATE TABLE IF NOT EXISTS members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  cif VARCHAR(20) UNIQUE,  -- Generated upon approval
  member_number VARCHAR(20) UNIQUE,
  full_name VARCHAR(150) NOT NULL,
  nik VARCHAR(20) UNIQUE NOT NULL,
  birth_place VARCHAR(100),
  birth_date DATE,
  gender VARCHAR(10) CHECK (gender IN ('male','female')),
  address TEXT,
  rt_rw VARCHAR(20),
  kelurahan VARCHAR(100),
  kecamatan VARCHAR(100),
  kota VARCHAR(100),
  provinsi VARCHAR(100),
  ktp_photo_url TEXT,
  selfie_url TEXT,
  phone VARCHAR(20),
  email VARCHAR(150),
  occupation VARCHAR(100),
  income_monthly NUMERIC(15,2),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','active','passive','rejected')),
  rejection_reason TEXT,
  approved_by UUID REFERENCES users(id),
  approved_at TIMESTAMPTZ,
  join_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- TABLE: chart_of_accounts
-- =====================================================
CREATE TABLE IF NOT EXISTS chart_of_accounts (
  id SERIAL PRIMARY KEY,
  code VARCHAR(20) UNIQUE NOT NULL,
  name VARCHAR(150) NOT NULL,
  type VARCHAR(30) NOT NULL CHECK (type IN ('asset','liability','equity','revenue','expense')),
  normal_balance VARCHAR(10) NOT NULL CHECK (normal_balance IN ('debit','credit')),
  parent_code VARCHAR(20),
  is_active BOOLEAN DEFAULT true,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- TABLE: notifications
-- =====================================================
CREATE TABLE IF NOT EXISTS notifications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  type VARCHAR(30) DEFAULT 'info' CHECK (type IN ('info','success','warning','error')),
  is_read BOOLEAN DEFAULT false,
  link TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- INDEXES
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_members_cif ON members(cif);
CREATE INDEX IF NOT EXISTS idx_members_status ON members(status);
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id, is_read);