-- PRESTIGE BARBERSHOP PROFESSIONAL v2.2
-- Jalankan pada database v2.1 yang SUDAH DIPILIH. Backup database terlebih dahulu.
CREATE TABLE IF NOT EXISTS finance_categories (
 id INT AUTO_INCREMENT PRIMARY KEY, type ENUM('income','expense') NOT NULL, code VARCHAR(40) NULL, name VARCHAR(120) NOT NULL,
 description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, created_by INT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_finance_category_type_name(type,name), UNIQUE KEY uq_finance_category_type_code(type,code),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_finance_category(type,is_active,sort_order)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS incomes (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, income_date DATE NOT NULL, category_id INT NULL, category VARCHAR(120) NOT NULL,
 description TEXT NOT NULL, amount DECIMAL(15,2) NOT NULL, payment_method VARCHAR(50) NOT NULL DEFAULT 'Cash', reference_no VARCHAR(100) NULL,
 created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(category_id) REFERENCES finance_categories(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id),
 INDEX idx_income_date(income_date), INDEX idx_income_category(category_id)
) ENGINE=InnoDB;
-- Untuk penambahan category_id pada expenses, lebih aman gunakan upgrade_v2_2.php karena halaman tersebut memeriksa struktur tabel lebih dahulu.
INSERT IGNORE INTO finance_categories(type,code,name,description,is_active,sort_order) VALUES
('income','INC-OTHER','Pemasukan Lainnya','Pemasukan non-transaksi jasa',1,10),('income','INC-CAPITAL','Penambahan Modal','Setoran atau penambahan modal usaha',1,20),('income','INC-ASSET','Penjualan Aset','Hasil penjualan aset/peralatan',1,30),
('expense','EXP-OPS','Operasional','Biaya operasional rutin',1,10),('expense','EXP-SUPPLY','Bahan & Persediaan','Pembelian bahan, produk, dan persediaan',1,20),('expense','EXP-SALARY','Gaji/Honor','Gaji, honor, dan upah di luar bonus otomatis',1,30),('expense','EXP-UTILITY','Utilitas','Listrik, air, internet, dan utilitas lain',1,40),('expense','EXP-MAINT','Perawatan','Perawatan peralatan dan fasilitas',1,50),('expense','EXP-OTHER','Pengeluaran Lainnya','Pengeluaran di luar kategori utama',1,90);