CREATE TABLE institutions (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT,
  phone TEXT,
  email TEXT,
  logo_url TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE chart_of_accounts (
  id BIGSERIAL PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  account_type TEXT NOT NULL,
  parent_id BIGINT REFERENCES chart_of_accounts(id),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE budget_plans (
  id BIGSERIAL PRIMARY KEY,
  account_id BIGINT NOT NULL REFERENCES chart_of_accounts(id),
  year INTEGER NOT NULL,
  amount DOUBLE PRECISION NOT NULL DEFAULT 0,
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(account_id, year)
);

CREATE TABLE transactions (
  id BIGSERIAL PRIMARY KEY,
  transaction_number TEXT UNIQUE NOT NULL,
  transaction_date DATE NOT NULL,
  account_id BIGINT NOT NULL REFERENCES chart_of_accounts(id),
  transaction_type TEXT NOT NULL,
  amount DOUBLE PRECISION NOT NULL,
  description TEXT NOT NULL,
  reference_number TEXT,
  created_by BIGINT REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Insert default institution
INSERT INTO institutions (name, address, phone, email) 
VALUES ('BUMDes Desa Makmur', 'Jl. Desa Makmur No. 1', '021-12345678', 'bumdes@desamakmur.id');

-- Insert sample chart of accounts
INSERT INTO chart_of_accounts (code, name, account_type) VALUES
('1', 'ASET', 'asset'),
('1.1', 'Aset Lancar', 'asset'),
('1.1.1', 'Kas', 'asset'),
('1.1.2', 'Bank', 'asset'),
('1.1.3', 'Piutang', 'asset'),
('1.2', 'Aset Tetap', 'asset'),
('1.2.1', 'Bangunan', 'asset'),
('1.2.2', 'Kendaraan', 'asset'),
('2', 'KEWAJIBAN', 'liability'),
('2.1', 'Kewajiban Lancar', 'liability'),
('2.1.1', 'Hutang Dagang', 'liability'),
('3', 'MODAL', 'equity'),
('3.1', 'Modal Desa', 'equity'),
('3.2', 'Modal Masyarakat', 'equity'),
('4', 'PENDAPATAN', 'revenue'),
('4.1', 'Pendapatan Usaha', 'revenue'),
('4.2', 'Pendapatan Lain-lain', 'revenue'),
('5', 'BEBAN', 'expense'),
('5.1', 'Beban Operasional', 'expense'),
('5.2', 'Beban Administrasi', 'expense');
