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 users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  full_name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  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 default admin user (password: admin123)
INSERT INTO users (username, email, password_hash, full_name, role) 
VALUES ('admin', 'admin@bumdes.id', '$2b$10$rO3bKpzKw5JZzKpzKw5JZOvF5vF5vF5vF5vF5vF5vF5vF5vF5vF5v', 'Administrator', 'admin');

-- 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');