-- ============================================================
-- DATABASE INSTALASI - APLIKASI KEUANGAN
-- Untuk diimport di hosting (cPanel phpMyAdmin / MySQL)
-- ============================================================
-- Cara pakai:
-- 1. Buat database baru di cPanel (misal: username_keuangan)
-- 2. Import file ini ke database tersebut
-- 3. Sesuaikan koneksi di koneksi.php (nama DB, user, password, host)
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8mb4;

-- Jika ingin buat database via SQL (banyak hosting hanya izin lewat cPanel):
-- CREATE DATABASE IF NOT EXISTS `nama_database_anda` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `nama_database_anda`;

-- --------------------------------------------------------
-- Tabel: user (login admin & manajemen)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_nama` varchar(100) NOT NULL,
  `user_username` varchar(100) NOT NULL,
  `user_password` varchar(100) NOT NULL,
  `user_foto` varchar(100) DEFAULT NULL,
  `user_level` varchar(20) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default login: admin / 123456  dan  manajemen / 123456 (ganti password setelah instalasi!)
INSERT INTO `user` (`user_id`, `user_nama`, `user_username`, `user_password`, `user_foto`, `user_level`) VALUES
(1, 'Administrator', 'admin', 'e10adc3949ba59abbe56e057f20f883e', NULL, 'administrator'),
(2, 'Manajemen', 'manajemen', 'e10adc3949ba59abbe56e057f20f883e', NULL, 'manajemen');

-- --------------------------------------------------------
-- Tabel: bank (rekening bank)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bank` (
  `bank_id` int(11) NOT NULL AUTO_INCREMENT,
  `bank_nama` varchar(255) NOT NULL,
  `bank_nomor` varchar(255) NOT NULL DEFAULT '',
  `bank_pemilik` varchar(255) NOT NULL DEFAULT '',
  `bank_saldo` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`bank_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `bank` (`bank_id`, `bank_nama`, `bank_nomor`, `bank_pemilik`, `bank_saldo`) VALUES
(1, 'Kas Utama', '0', 'Perusahaan', 0);

-- --------------------------------------------------------
-- Tabel: kategori (kategori transaksi)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `kategori` (
  `kategori_id` int(11) NOT NULL AUTO_INCREMENT,
  `kategori` varchar(255) NOT NULL,
  PRIMARY KEY (`kategori_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kategori` (`kategori_id`, `kategori`) VALUES
(1, 'Operasional'),
(2, 'Pemasukan Lain'),
(3, 'Pengeluaran Lain');

-- --------------------------------------------------------
-- Tabel: transaksi
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transaksi` (
  `transaksi_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaksi_tanggal` date NOT NULL,
  `transaksi_jenis` enum('Pengeluaran','Pemasukan') NOT NULL,
  `transaksi_kategori` int(11) NOT NULL,
  `transaksi_nominal` bigint(20) NOT NULL DEFAULT 0,
  `transaksi_keterangan` text,
  `transaksi_foto` varchar(255) NOT NULL DEFAULT '',
  `transaksi_bank` int(11) NOT NULL,
  PRIMARY KEY (`transaksi_id`),
  KEY `transaksi_bank` (`transaksi_bank`),
  KEY `transaksi_kategori` (`transaksi_kategori`),
  KEY `transaksi_tanggal` (`transaksi_tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tabel: profil_perusahaan (setting nama, logo, alamat)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `profil_perusahaan` (
  `profil_id` int(11) NOT NULL AUTO_INCREMENT,
  `profil_nama` varchar(255) NOT NULL,
  `profil_alamat` text,
  `profil_telepon` varchar(50) DEFAULT NULL,
  `profil_email` varchar(100) DEFAULT NULL,
  `profil_website` varchar(255) DEFAULT NULL,
  `profil_logo` varchar(255) DEFAULT NULL,
  `profil_deskripsi` text,
  PRIMARY KEY (`profil_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `profil_perusahaan` (`profil_id`, `profil_nama`, `profil_alamat`, `profil_telepon`, `profil_email`, `profil_website`, `profil_logo`, `profil_deskripsi`) VALUES
(1, 'Nama Perusahaan Anda', 'Alamat perusahaan', '08xxxxxxxxxx', 'email@perusahaan.com', '', '', 'Aplikasi Keuangan');

-- --------------------------------------------------------
-- Tabel: hutang (opsional)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `hutang` (
  `hutang_id` int(11) NOT NULL AUTO_INCREMENT,
  `hutang_tanggal` date NOT NULL,
  `hutang_nominal` int(11) NOT NULL DEFAULT 0,
  `hutang_keterangan` text,
  PRIMARY KEY (`hutang_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tabel: piutang (opsional)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `piutang` (
  `piutang_id` int(11) NOT NULL AUTO_INCREMENT,
  `piutang_tanggal` date NOT NULL,
  `piutang_nominal` int(11) NOT NULL DEFAULT 0,
  `piutang_keterangan` text,
  PRIMARY KEY (`piutang_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tabel: laporan_kasir (pembayaran tunai dari laporan kasir)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `laporan_kasir` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tanggal` date NOT NULL,
  `shift` enum('Pagi','Siang','Malam') NOT NULL,
  `nominal` bigint(20) NOT NULL DEFAULT 0,
  `keterangan` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tanggal` (`tanggal`),
  KEY `shift` (`shift`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Selesai. Setelah import:
-- 1. Login: username admin / password 123456 (atau manajemen / 123456)
-- 2. Ganti password di menu Ganti Password
-- 3. Isi profil perusahaan di Setting Profil
-- 4. Tambah bank & kategori sesuai kebutuhan
-- ============================================================
