-- JLBB Finance & Profit Control - MySQL/MariaDB
-- Export database aplikasi tanggal 24 Agustus 2026
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS commercial_documents;
DROP TABLE IF EXISTS approvals;
DROP TABLE IF EXISTS finance_entries;
DROP TABLE IF EXISTS assets;
DROP TABLE IF EXISTS projections;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS clients;

CREATE TABLE clients (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company VARCHAR(255) NOT NULL,contact VARCHAR(255) NOT NULL DEFAULT '',phone VARCHAR(100) NOT NULL DEFAULT '',email VARCHAR(255) NOT NULL DEFAULT '',address TEXT NOT NULL,tax_id VARCHAR(100) NOT NULL DEFAULT '',created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE projects (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(255) NOT NULL,service_type VARCHAR(255) NOT NULL DEFAULT '',client_id INT UNSIGNED NULL,contract_value DOUBLE NOT NULL DEFAULT 0,budget DOUBLE NOT NULL DEFAULT 0,progress DOUBLE NOT NULL DEFAULT 0,status VARCHAR(100) NOT NULL DEFAULT 'Berjalan',start_date VARCHAR(20) NOT NULL DEFAULT '',end_date VARCHAR(20) NOT NULL DEFAULT '',created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_projects_client FOREIGN KEY(client_id) REFERENCES clients(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE finance_entries (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,date VARCHAR(20) NOT NULL,due_date VARCHAR(20) NOT NULL DEFAULT '',document_no VARCHAR(120) NOT NULL DEFAULT '',category VARCHAR(100) NOT NULL,direction VARCHAR(30) NOT NULL,description TEXT NOT NULL,amount DOUBLE NOT NULL DEFAULT 0,paid_amount DOUBLE NOT NULL DEFAULT 0,notification_dismissed TINYINT NOT NULL DEFAULT 0,status VARCHAR(100) NOT NULL DEFAULT 'Draft',project_id INT UNSIGNED NULL,client_id INT UNSIGNED NULL,notes TEXT NOT NULL,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX idx_document_no(document_no),CONSTRAINT fk_finance_project FOREIGN KEY(project_id) REFERENCES projects(id),CONSTRAINT fk_finance_client FOREIGN KEY(client_id) REFERENCES clients(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE assets (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(255) NOT NULL,category VARCHAR(120) NOT NULL,acquisition_date VARCHAR(20) NOT NULL,acquisition_value DOUBLE NOT NULL DEFAULT 0,useful_life INT NOT NULL DEFAULT 4,`condition` VARCHAR(100) NOT NULL DEFAULT 'Baik',location VARCHAR(255) NOT NULL DEFAULT 'Kantor JLBB',created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE projections (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,period VARCHAR(100) NOT NULL,scenario VARCHAR(100) NOT NULL DEFAULT 'Moderat',projected_income DOUBLE NOT NULL DEFAULT 0,projected_expense DOUBLE NOT NULL DEFAULT 0,notes TEXT NOT NULL,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE commercial_documents (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,type VARCHAR(50) NOT NULL,document_no VARCHAR(120) NOT NULL UNIQUE,date VARCHAR(20) NOT NULL,due_date VARCHAR(20) NOT NULL DEFAULT '',client_id INT UNSIGNED NULL,project_id INT UNSIGNED NULL,subject TEXT NOT NULL,items_json LONGTEXT NOT NULL,tax_mode VARCHAR(30) NOT NULL DEFAULT 'exclude',tax_rate DOUBLE NOT NULL DEFAULT 11,discount_type VARCHAR(30) NOT NULL DEFAULT 'percent',discount_value DOUBLE NOT NULL DEFAULT 0,subtotal DOUBLE NOT NULL DEFAULT 0,discount_amount DOUBLE NOT NULL DEFAULT 0,tax_amount DOUBLE NOT NULL DEFAULT 0,total DOUBLE NOT NULL DEFAULT 0,paid_amount DOUBLE NOT NULL DEFAULT 0,status VARCHAR(100) NOT NULL DEFAULT 'Draft',source_document_id INT UNSIGNED NULL,sent_at VARCHAR(20) NOT NULL DEFAULT '',sent_to VARCHAR(255) NOT NULL DEFAULT '',payment_date VARCHAR(20) NOT NULL DEFAULT '',payment_method VARCHAR(100) NOT NULL DEFAULT '',terms TEXT NOT NULL,notes TEXT NOT NULL,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_documents_client FOREIGN KEY(client_id) REFERENCES clients(id),CONSTRAINT fk_documents_project FOREIGN KEY(project_id) REFERENCES projects(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE approvals (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,report_type VARCHAR(50) NOT NULL,period VARCHAR(7) NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'Disetujui',approved_by VARCHAR(255) NOT NULL,approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,notes TEXT NOT NULL,UNIQUE KEY uq_approval_period(report_type,period)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO clients(id,company,contact,phone,email,address,tax_id) VALUES
(1,'fftyryrty','rtyrtr','trytrty','rtyrtyry@dktott.com','rtyrtyrtyrty','rtyry'),
(2,'Client1','PIC','','','','');
INSERT INTO projects(id,code,name,service_type,client_id,contract_value,budget,progress,status,start_date,end_date) VALUES
(2,'08909','Andalalin Kawasan Bengkong','Penyusunan ANDALALIN',2,10000000,85000000,0,'Selesai','','');
INSERT INTO commercial_documents(id,type,document_no,date,due_date,client_id,project_id,subject,items_json,tax_mode,tax_rate,discount_type,discount_value,subtotal,discount_amount,tax_amount,total,paid_amount,status,source_document_id,terms,notes,sent_at,sent_to,payment_date,payment_method) VALUES
(1,'Penawaran','PEN/JLBB/2026/001','2026-08-24','',1,NULL,'tryrtyrt','[{"description":"Jasa konsultansi lingkungan","qty":1,"unit":"Paket","price":0}]','exclude',11,'percent',0,0,0,0,0,0,'Disetujui',NULL,'trty','rtyrtyry','','','',''),
(2,'Tagihan','INV/JLBB/2026/001','2026-08-24','2026-05-04',1,NULL,'ertertert','[{"description":"Jasa konsultansi lingkungan","qty":1,"unit":"Paket","price":0}]','exclude',11,'percent',0,0,0,0,0,0,'Lunas',NULL,'gfffffffffffffffff','fggggggggggggggggggggg','','','',''),
(3,'Kwitansi','KWT/JLBB/2026/458339','2026-08-24','',1,NULL,'ertertert','[{"description":"Jasa konsultansi lingkungan","qty":1,"unit":"Paket","price":0}]','exclude',11,'percent',0,0,0,0,0,0,'Lunas',2,'gfffffffffffffffff','fggggggggggggggggggggg','','','',''),
(4,'Kwitansi','KWT/JLBB/2026/473506','2026-08-24','',1,NULL,'ertertert','[{"description":"Jasa konsultansi lingkungan","qty":1,"unit":"Paket","price":0}]','exclude',11,'percent',0,0,0,0,0,0,'Lunas',2,'gfffffffffffffffff','fggggggggggggggggggggg','','','',''),
(5,'Penawaran','PEN/JLBB/2026/002','2026-08-24','',2,2,'Penyusunan ANDALALIN','[{"description":"Tenaga ahli & personel — Penyusunan ANDALALIN","qty":1,"unit":"Paket","price":82500000},{"description":"Survei dan pengumpulan data — Penyusunan ANDALALIN","qty":1,"unit":"Paket","price":22500000},{"description":"Uji laboratorium/peralatan — Penyusunan ANDALALIN","qty":1,"unit":"Paket","price":15000000},{"description":"Pembahasan, administrasi & dokumen — Penyusunan ANDALALIN","qty":1,"unit":"Paket","price":12000000},{"description":"Perjalanan dan logistik — Penyusunan ANDALALIN","qty":1,"unit":"Paket","price":10500000},{"description":"Kontinjensi lingkup — Penyusunan ANDALALIN","qty":1,"unit":"Paket","price":7500000}]','exclude',11,'percent',0,150000000,0,16500000,166500000,0,'Disetujui',NULL,'Pembayaran 30% setelah SPK, 40% setelah penyampaian draft, dan 30% setelah dokumen final/persetujuan sesuai ruang lingkup.','Template estimasi minimum. Durasi indikatif 3–6 bulan. Dasar teknis: Permenhub 17/2021 dan ketentuan lalu lintas yang berlaku. Estimasi jasa belum termasuk PPN, PNBP/retribusi resmi, pengujian khusus di luar ruang lingkup, dan perjalanan di luar Batam.','','','','');
INSERT INTO finance_entries(id,date,due_date,document_no,category,direction,description,amount,paid_amount,status,project_id,client_id,notes,notification_dismissed) VALUES
(1,'2026-08-24','','PEN/JLBB/2026/001','Penawaran','Masuk','tryrtyrt',0,0,'Disetujui',NULL,1,'Pajak exclude, diskon 0',0),
(2,'2026-08-24','2026-05-04','INV/JLBB/2026/001','Invoice','Masuk','ertertert',0,0,'Telah Dibayarkan',NULL,1,'Pajak exclude, diskon 0',1),
(3,'2026-08-24','','KWT/JLBB/2026/458339','Penerimaan','Masuk','Pembayaran ertertert',0,0,'Lunas',NULL,1,'Kwitansi dari INV/JLBB/2026/001',0),
(4,'2026-08-24','','KWT/JLBB/2026/473506','Penerimaan','Masuk','Pembayaran ertertert',0,0,'Lunas',NULL,1,'Kwitansi dari INV/JLBB/2026/001',0),
(5,'2026-08-24','','PEN/JLBB/2026/002','Penawaran','Masuk','Penyusunan ANDALALIN',166500000,0,'Disetujui',2,2,'Pajak exclude, diskon 0',0),
(6,'2026-08-24','','TRX-2026-00006','Pengeluaran','Keluar','belanja ',10000,0,'Disetujui',2,2,'ok',0),
(7,'2026-08-24','','TRX-2026-00007','Penerimaan','Masuk','termmin 1',12000,0,'Disetujui',2,2,'ok',0);
SET FOREIGN_KEY_CHECKS=1;
