-- Database: kodefin

-- Table: user
CREATE TABLE IF NOT EXISTS `user` (
  `id_user` INT AUTO_INCREMENT PRIMARY KEY,
  `nama_user` VARCHAR(100) NOT NULL,
  `login_user` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: kategori_pemasukan
CREATE TABLE IF NOT EXISTS `kategori_pemasukan` (
  `id_kategori_pemasukan` INT AUTO_INCREMENT PRIMARY KEY,
  `nama_kategori` VARCHAR(100) NOT NULL,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: kategori_pengeluaran
CREATE TABLE IF NOT EXISTS `kategori_pengeluaran` (
  `id_kategori_pengeluaran` INT AUTO_INCREMENT PRIMARY KEY,
  `nama_kategori` VARCHAR(100) NOT NULL,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: bank
CREATE TABLE IF NOT EXISTS `bank` (
  `id_bank` INT AUTO_INCREMENT PRIMARY KEY,
  `nama_bank` VARCHAR(100) NOT NULL,
  `no_rekening` VARCHAR(50) NOT NULL,
  `cabang` VARCHAR(100) NOT NULL,
  `kota` VARCHAR(100) NOT NULL,
  `tipe_akun` ENUM('asset','liability') NOT NULL DEFAULT 'asset',
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: pemasukan
CREATE TABLE IF NOT EXISTS `pemasukan` (
  `id_pemasukan` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_pemasukan` DATE NOT NULL,
  `id_kategori_pemasukan` INT NOT NULL,
  `id_bank` INT NOT NULL,
  `dari` VARCHAR(100) NOT NULL,
  `deskripsi` TEXT,
  `bukti_transaksi` VARCHAR(255),
  `nilai_pemasukan` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  `edit_time` DATETIME,
  `edit_oleh` VARCHAR(100),
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (`id_kategori_pemasukan`) REFERENCES `kategori_pemasukan`(`id_kategori_pemasukan`),
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: pengeluaran
CREATE TABLE IF NOT EXISTS `pengeluaran` (
  `id_pengeluaran` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_pengeluaran` DATE NOT NULL,
  `id_kategori_pengeluaran` INT NOT NULL,
  `id_bank` INT NOT NULL,
  `dari` VARCHAR(100) NOT NULL,
  `deskripsi` TEXT,
  `bukti_transaksi` VARCHAR(255),
  `nilai_pengeluaran` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  `edit_time` DATETIME,
  `edit_oleh` VARCHAR(100),
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (`id_kategori_pengeluaran`) REFERENCES `kategori_pengeluaran`(`id_kategori_pengeluaran`),
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: rencana_pemasukan
CREATE TABLE IF NOT EXISTS `rencana_pemasukan` (
  `id_rencana_pemasukan` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_pemasukan` DATE NOT NULL,
  `id_kategori_pemasukan` INT NOT NULL,
  `id_bank` INT NOT NULL,
  `dari` VARCHAR(100) NOT NULL,
  `deskripsi` TEXT,
  `bukti_transaksi` VARCHAR(255),
  `nilai_pemasukan` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `is_executed` TINYINT(1) NOT NULL DEFAULT 0,
  `executed_time` DATETIME,
  `executed_oleh` VARCHAR(100),
  `id_pemasukan` INT,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  `edit_time` DATETIME,
  `edit_oleh` VARCHAR(100),
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (`id_kategori_pemasukan`) REFERENCES `kategori_pemasukan`(`id_kategori_pemasukan`),
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: rencana_pengeluaran
CREATE TABLE IF NOT EXISTS `rencana_pengeluaran` (
  `id_rencana_pengeluaran` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_pengeluaran` DATE NOT NULL,
  `id_kategori_pengeluaran` INT NOT NULL,
  `id_bank` INT NOT NULL,
  `dari` VARCHAR(100) NOT NULL,
  `deskripsi` TEXT,
  `bukti_transaksi` VARCHAR(255),
  `nilai_pengeluaran` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `is_executed` TINYINT(1) NOT NULL DEFAULT 0,
  `executed_time` DATETIME,
  `executed_oleh` VARCHAR(100),
  `id_pengeluaran` INT,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  `edit_time` DATETIME,
  `edit_oleh` VARCHAR(100),
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (`id_kategori_pengeluaran`) REFERENCES `kategori_pengeluaran`(`id_kategori_pengeluaran`),
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: anggaran (for budget plans)
CREATE TABLE IF NOT EXISTS `anggaran` (
  `id_anggaran` INT AUTO_INCREMENT PRIMARY KEY,
  `bulan` VARCHAR(7) NOT NULL,
  `tipe` ENUM('pemasukan', 'pengeluaran') NOT NULL,
  `id_kategori` INT NOT NULL,
  `nilai_rencana` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: utang (debts we owe)
CREATE TABLE IF NOT EXISTS `utang` (
  `id_utang` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_utang` DATE NOT NULL,
  `nama_pemberi` VARCHAR(100) NOT NULL,
  `deskripsi` TEXT,
  `bukti_transaksi` VARCHAR(255),
  `nilai_utang` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `interest_persen` DECIMAL(5,2),
  `interest_rupiah` DECIMAL(15,2),
  `total_pengembalian` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `id_bank_penerimaan` INT,
  `id_pemasukan` INT,
  `status` ENUM('belum_lunas', 'lunas') DEFAULT 'belum_lunas',
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  `edit_time` DATETIME,
  `edit_oleh` VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: piutang (debts owed to us)
CREATE TABLE IF NOT EXISTS `piutang` (
  `id_piutang` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_piutang` DATE NOT NULL,
  `nama_peminjam` VARCHAR(100) NOT NULL,
  `deskripsi` TEXT,
  `bukti_transaksi` VARCHAR(255),
  `nilai_piutang` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `id_bank_pengeluaran` INT,
  `id_pengeluaran` INT,
  `status` ENUM('belum_lunas', 'lunas') DEFAULT 'belum_lunas',
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  `edit_time` DATETIME,
  `edit_oleh` VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: pembayaran_utang (payments for debts we owe)
CREATE TABLE IF NOT EXISTS `pembayaran_utang` (
  `id_pembayaran_utang` INT AUTO_INCREMENT PRIMARY KEY,
  `id_utang` INT NOT NULL,
  `tgl_pembayaran` DATE NOT NULL,
  `id_bank` INT NOT NULL,
  `nilai_pembayaran` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `id_pengeluaran` INT,
  `deskripsi` TEXT,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  FOREIGN KEY (`id_utang`) REFERENCES `utang`(`id_utang`) ON DELETE CASCADE,
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: pembayaran_piutang (payments for debts owed to us)
CREATE TABLE IF NOT EXISTS `pembayaran_piutang` (
  `id_pembayaran_piutang` INT AUTO_INCREMENT PRIMARY KEY,
  `id_piutang` INT NOT NULL,
  `tgl_pembayaran` DATE NOT NULL,
  `id_bank` INT NOT NULL,
  `nilai_pembayaran` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `id_pemasukan` INT,
  `deskripsi` TEXT,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  FOREIGN KEY (`id_piutang`) REFERENCES `piutang`(`id_piutang`) ON DELETE CASCADE,
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: transfer (transfer between banks)
CREATE TABLE IF NOT EXISTS `transfer` (
  `id_transfer` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_transfer` DATE NOT NULL,
  `id_bank_dari` INT NOT NULL,
  `id_bank_ke` INT NOT NULL,
  `nilai_transfer` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `id_pengeluaran` INT,
  `id_pemasukan` INT,
  `bukti_transaksi` VARCHAR(255),
  `deskripsi` TEXT,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  FOREIGN KEY (`id_bank_dari`) REFERENCES `bank`(`id_bank`),
  FOREIGN KEY (`id_bank_ke`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: investor (data investor)
CREATE TABLE IF NOT EXISTS `investor` (
  `id_investor` INT AUTO_INCREMENT PRIMARY KEY,
  `nama_lengkap` VARCHAR(255) NOT NULL,
  `tipe_investor` ENUM('tetap', 'by project') DEFAULT 'tetap',
  `tipe_id` VARCHAR(50) NOT NULL,
  `no_id` VARCHAR(100) NOT NULL,
  `tempat_lahir` VARCHAR(255) NOT NULL,
  `tgl_lahir` DATE NOT NULL,
  `alamat_lengkap` TEXT NOT NULL,
  `no_hp` VARCHAR(50) NOT NULL,
  `no_wa` VARCHAR(50) NOT NULL,
  `email` VARCHAR(255),
  `scan_ktp` VARCHAR(255),
  `keterangan` TEXT,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: investasi (investment data)
CREATE TABLE IF NOT EXISTS `investasi` (
  `id_investasi` INT AUTO_INCREMENT PRIMARY KEY,
  `tgl_investasi` DATE NOT NULL,
  `id_investor` INT NOT NULL,
  `id_bank` INT NOT NULL,
  `nilai_investasi` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `id_pemasukan` INT,
  `margin_persen` DECIMAL(5,2),
  `margin_rupiah` DECIMAL(15,2),
  `nilai_pengembalian` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `jangka_waktu` INT NOT NULL COMMENT 'in months',
  `tgl_pengembalian` DATE NOT NULL,
  `lampiran_perjanjian` VARCHAR(255),
  `bukti_transaksi` VARCHAR(255),
  `deskripsi` TEXT,
  `status` ENUM('berlangsung', 'selesai') DEFAULT 'berlangsung',
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  FOREIGN KEY (`id_investor`) REFERENCES `investor`(`id_investor`),
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: pembayaran_investasi (payments for investments)
CREATE TABLE IF NOT EXISTS `pembayaran_investasi` (
  `id_pembayaran_investasi` INT AUTO_INCREMENT PRIMARY KEY,
  `id_investasi` INT NOT NULL,
  `tgl_pembayaran` DATE NOT NULL,
  `id_investor` INT NOT NULL,
  `id_bank` INT NOT NULL,
  `nilai_pembayaran` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `id_pengeluaran` INT,
  `bank_penerima` VARCHAR(255),
  `deskripsi` TEXT,
  `input_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `input_oleh` VARCHAR(100) NOT NULL,
  FOREIGN KEY (`id_investasi`) REFERENCES `investasi`(`id_investasi`) ON DELETE CASCADE,
  FOREIGN KEY (`id_investor`) REFERENCES `investor`(`id_investor`),
  FOREIGN KEY (`id_bank`) REFERENCES `bank`(`id_bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default admin user (password: admin123)
INSERT INTO `user` (`nama_user`, `login_user`, `password`, `input_oleh`) VALUES
('Administrator', 'admin', '$2y$10$YqJd1n5j1OZ5b7iK4iZ1Ue9x7x7x7x7x7x7x7x7x7x7x7x7x7', 'system');