-- ========================================================
-- SISTEM AKUNTANSI LENGKAP - DATABASE SCHEMA (PostgreSQL 14+)
-- Sesuai Standar Akuntansi PSAK / SAK & CodeIgniter 4
-- ========================================================

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Drop existing tables
DROP TABLE IF EXISTS system_logs CASCADE;
DROP TABLE IF EXISTS activity_logs CASCADE;
DROP TABLE IF EXISTS audit_trail CASCADE;
DROP TABLE IF EXISTS document_signatories CASCADE;
DROP TABLE IF EXISTS virtual_ledgers CASCADE;
DROP TABLE IF EXISTS virtual_accounts CASCADE;
DROP TABLE IF EXISTS vendors_suppliers CASCADE;
DROP TABLE IF EXISTS journal_items CASCADE;
DROP TABLE IF EXISTS journal_entries CASCADE;
DROP TABLE IF EXISTS period_account_balances CASCADE;
DROP TABLE IF EXISTS accounts CASCADE;
DROP TABLE IF EXISTS accounting_periods CASCADE;
DROP TABLE IF EXISTS company_profile CASCADE;
DROP TABLE IF EXISTS role_permissions CASCADE;
DROP TABLE IF EXISTS user_permissions CASCADE;
DROP TABLE IF EXISTS permissions CASCADE;
DROP TABLE IF EXISTS roles CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- 1. Users
CREATE TABLE users (
  id VARCHAR(36) PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  role VARCHAR(50) NOT NULL DEFAULT 'akuntan',
  avatar VARCHAR(255),
  department VARCHAR(100),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  last_login TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_status ON users(is_active);

-- 2. Roles & Permissions (RBAC Matrix)
CREATE TABLE roles (
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE permissions (
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(50) NOT NULL,
  description TEXT
);

CREATE TABLE role_permissions (
  role_id VARCHAR(50) NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id VARCHAR(50) NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE user_permissions (
  user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  permission_id VARCHAR(50) NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, permission_id)
);

-- 3. Company Profile
CREATE TABLE company_profile (
  id SERIAL PRIMARY KEY,
  company_name VARCHAR(150) NOT NULL,
  industry VARCHAR(100) NOT NULL,
  fiscal_year VARCHAR(10) NOT NULL DEFAULT '2026',
  address TEXT NOT NULL,
  phone VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  npwp VARCHAR(50),
  website VARCHAR(100),
  tax_rate_percent NUMERIC(5,2) NOT NULL DEFAULT 22.00,
  logo_url TEXT,
  logo_source VARCHAR(20) NOT NULL DEFAULT 'local',
  storage_driver VARCHAR(20) NOT NULL DEFAULT 'local',
  cloud_bucket VARCHAR(100),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 4. Accounting Periods
CREATE TABLE accounting_periods (
  id VARCHAR(36) PRIMARY KEY,
  year INT NOT NULL,
  month INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  is_closed BOOLEAN NOT NULL DEFAULT FALSE,
  closed_at TIMESTAMP,
  closed_by VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
  initial_balance_mode VARCHAR(20) NOT NULL DEFAULT 'manual',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uniq_period_ym UNIQUE (year, month)
);

-- 5. Chart of Accounts (COA)
CREATE TABLE accounts (
  id VARCHAR(36) PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  category VARCHAR(50) NOT NULL,
  pos_saldo VARCHAR(10) NOT NULL,
  pos_laporan VARCHAR(20) NOT NULL,
  initial_debit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  initial_credit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  cash_flow_category VARCHAR(20) NOT NULL DEFAULT 'Operasi',
  description TEXT,
  linked_vendor_id VARCHAR(50),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_accounts_code ON accounts(code);
CREATE INDEX idx_accounts_category ON accounts(category);
CREATE INDEX idx_accounts_pos_laporan ON accounts(pos_laporan);
CREATE INDEX idx_accounts_vendor ON accounts(linked_vendor_id);

-- 6. Period Balances
CREATE TABLE period_account_balances (
  id BIGSERIAL PRIMARY KEY,
  period_id VARCHAR(36) NOT NULL REFERENCES accounting_periods(id) ON DELETE CASCADE,
  account_id VARCHAR(36) NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  beginning_debit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  beginning_credit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  mutation_debit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  mutation_credit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  ending_debit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  ending_credit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  is_calculated BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT uniq_period_account UNIQUE (period_id, account_id)
);

-- 7. Journal Header
CREATE TABLE journal_entries (
  id VARCHAR(36) PRIMARY KEY,
  ref_no VARCHAR(50) NOT NULL UNIQUE,
  period_id VARCHAR(36) REFERENCES accounting_periods(id) ON DELETE SET NULL,
  entry_date DATE NOT NULL,
  entry_month VARCHAR(50) NOT NULL,
  journal_type VARCHAR(20) NOT NULL DEFAULT 'Umum',
  notes TEXT NOT NULL,
  total_amount NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  is_posted BOOLEAN NOT NULL DEFAULT TRUE,
  created_by VARCHAR(36) REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_journal_date ON journal_entries(entry_date);
CREATE INDEX idx_journal_month ON journal_entries(entry_month);
CREATE INDEX idx_journal_type ON journal_entries(journal_type);

-- 8. Journal Items
CREATE TABLE journal_items (
  id VARCHAR(36) PRIMARY KEY,
  journal_id VARCHAR(36) NOT NULL REFERENCES journal_entries(id) ON DELETE CASCADE,
  account_id VARCHAR(36) NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
  account_code VARCHAR(20) NOT NULL,
  account_name VARCHAR(150) NOT NULL,
  debit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  credit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  description VARCHAR(255),
  vendor_id VARCHAR(50),
  virtual_account_id VARCHAR(50),
  va_number VARCHAR(50),
  sort_order INT NOT NULL DEFAULT 0
);

CREATE INDEX idx_item_account ON journal_items(account_id);
CREATE INDEX idx_item_code ON journal_items(account_code);
CREATE INDEX idx_item_vendor ON journal_items(vendor_id);
CREATE INDEX idx_item_va ON journal_items(virtual_account_id);

-- 9. Audit Trail
CREATE TABLE audit_trail (
  id BIGSERIAL PRIMARY KEY,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_id VARCHAR(36),
  user_name VARCHAR(100) NOT NULL,
  user_role VARCHAR(50) NOT NULL,
  module VARCHAR(50) NOT NULL,
  action VARCHAR(50) NOT NULL,
  entity_id VARCHAR(100),
  description TEXT NOT NULL,
  old_values JSONB,
  new_values JSONB,
  ip_address VARCHAR(45) NOT NULL DEFAULT '127.0.0.1',
  user_agent VARCHAR(255) NOT NULL DEFAULT 'Web Browser'
);

CREATE INDEX idx_audit_module ON audit_trail(module);
CREATE INDEX idx_audit_timestamp ON audit_trail(timestamp);

-- 10. Activity Logs
CREATE TABLE activity_logs (
  id BIGSERIAL PRIMARY KEY,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_id VARCHAR(36),
  user_name VARCHAR(100) NOT NULL,
  activity TEXT NOT NULL,
  severity VARCHAR(20) NOT NULL DEFAULT 'info',
  ip_address VARCHAR(45) NOT NULL DEFAULT '127.0.0.1'
);

CREATE INDEX idx_act_user ON activity_logs(user_id);

-- 11. System Logs
CREATE TABLE system_logs (
  id BIGSERIAL PRIMARY KEY,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  level VARCHAR(20) NOT NULL DEFAULT 'INFO',
  component VARCHAR(100) NOT NULL,
  message TEXT NOT NULL,
  context TEXT
);

-- 12. Rekanan Vendor & Supplier
CREATE TABLE vendors_suppliers (
  id VARCHAR(50) PRIMARY KEY,
  kode VARCHAR(50) NOT NULL UNIQUE,
  nama VARCHAR(150) NOT NULL,
  tipe VARCHAR(30) NOT NULL DEFAULT 'Vendor',
  kategori_usaha VARCHAR(150),
  npwp VARCHAR(50),
  alamat TEXT,
  kota VARCHAR(100),
  telepon VARCHAR(50),
  email VARCHAR(100),
  kontak_person VARCHAR(100),
  syarat_pembayaran VARCHAR(50) NOT NULL DEFAULT 'Net 30',
  plafon_kredit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  akun_hutang_piutang_kode VARCHAR(20) NOT NULL DEFAULT '2-1010',
  total_tagihan NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  total_dibayar NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  saldo_hutang NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_vendor_kode ON vendors_suppliers(kode);
CREATE INDEX idx_vendor_tipe ON vendors_suppliers(tipe);

-- 13. Virtual Account Rekanan (Multi-Bank)
CREATE TABLE virtual_accounts (
  id VARCHAR(50) PRIMARY KEY,
  vendor_id VARCHAR(50) NOT NULL REFERENCES vendors_suppliers(id) ON DELETE CASCADE,
  bank_name VARCHAR(30) NOT NULL,
  account_number VARCHAR(50) NOT NULL UNIQUE,
  account_holder_name VARCHAR(150) NOT NULL,
  va_type VARCHAR(20) NOT NULL DEFAULT 'Static',
  currency VARCHAR(10) NOT NULL DEFAULT 'IDR',
  status VARCHAR(20) NOT NULL DEFAULT 'Active',
  expired_at TIMESTAMP,
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_va_number ON virtual_accounts(account_number);
CREATE INDEX idx_va_vendor ON virtual_accounts(vendor_id);

-- 14. Mutasi Buku Pembantu (Virtual Ledger / VL)
CREATE TABLE virtual_ledgers (
  id VARCHAR(50) PRIMARY KEY,
  vendor_id VARCHAR(50) NOT NULL REFERENCES vendors_suppliers(id) ON DELETE CASCADE,
  tanggal DATE NOT NULL,
  no_referensi VARCHAR(100) NOT NULL,
  tipe_transaksi VARCHAR(50) NOT NULL,
  debet NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  kredit NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  saldo_berjalan NUMERIC(18,2) NOT NULL DEFAULT 0.00,
  jurnal_id VARCHAR(50),
  keterangan TEXT NOT NULL,
  status_pembayaran VARCHAR(30) NOT NULL DEFAULT 'Belum Lunas',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_vl_vendor ON virtual_ledgers(vendor_id);
CREATE INDEX idx_vl_tanggal ON virtual_ledgers(tanggal);

-- 15. Konfigurasi Pejabat Penandatangan Dinamis & QR Code
CREATE TABLE document_signatories (
  id VARCHAR(50) PRIMARY KEY,
  dokumen_tipe VARCHAR(50) NOT NULL DEFAULT 'ALL',
  peran_label VARCHAR(100) NOT NULL,
  nama VARCHAR(150) NOT NULL,
  jabatan VARCHAR(150) NOT NULL,
  nip_nik VARCHAR(50),
  email VARCHAR(100),
  status_tanda_tangan VARCHAR(30) NOT NULL DEFAULT 'DITANDATANGANI_DIGITAL',
  tanggal_tanda_tangan TIMESTAMP,
  enable_qr_code BOOLEAN NOT NULL DEFAULT TRUE,
  qr_code_data TEXT,
  catatan TEXT,
  kota_penetapan VARCHAR(100) NOT NULL DEFAULT 'Semarang',
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);