CREATE DATABASE finance_app;
USE finance_app;

CREATE TABLE users(
 id INT AUTO_INCREMENT PRIMARY KEY,
 nama VARCHAR(100),
 username VARCHAR(50) UNIQUE,
 password VARCHAR(255),
 role ENUM('admin','staff') DEFAULT 'staff'
);

CREATE TABLE laporan_keuangan(
 id INT AUTO_INCREMENT PRIMARY KEY,
 nama_laporan VARCHAR(150),
 deskripsi TEXT,
 created_by INT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE akun(
 id INT AUTO_INCREMENT PRIMARY KEY,
 laporan_id INT,
 nama_akun VARCHAR(100),
 jenis ENUM('cash_in','cash_out')
);

CREATE TABLE transaksi(
 id INT AUTO_INCREMENT PRIMARY KEY,
 laporan_id INT,
 akun_id INT,
 tanggal DATE,
 deskripsi TEXT,
 nominal DECIMAL(18,2),
 foto VARCHAR(255),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE neraca(
 id INT AUTO_INCREMENT PRIMARY KEY,
 laporan_id INT,
 kategori ENUM('aktiva','pasiva'),
 subkategori VARCHAR(100),
 nama_akun VARCHAR(100),
 nilai DECIMAL(18,2)
);