-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Waktu pembuatan: 01 Nov 2024 pada 10:00
-- Versi server: 10.4.27-MariaDB
-- Versi PHP: 8.2.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_keuangan_v2`
--
CREATE DATABASE IF NOT EXISTS `db_keuangan_v2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `db_keuangan_v2`;

-- --------------------------------------------------------

--
-- Struktur dari tabel `users`
--
CREATE TABLE `users` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) DEFAULT NULL,
  `role` ENUM('admin','user') NOT NULL DEFAULT 'user',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data untuk tabel `users`
--
INSERT INTO `users` (`user_id`, `username`, `password_hash`, `full_name`, `role`) VALUES
(1, 'admin', '$2y$10$E.qR.w./aM9T89t67Uuxs.BULgIAgIuNcHlILWsQ.NlYJBFc4.u1G', 'Admin Utama', 'admin');

-- --------------------------------------------------------

--
-- Struktur dari tabel `accounts` (Chart of Accounts / COA)
--
CREATE TABLE `accounts` (
  `account_id` INT(11) NOT NULL AUTO_INCREMENT,
  `account_code` VARCHAR(20) NOT NULL UNIQUE,
  `account_name` VARCHAR(100) NOT NULL,
  `account_type` ENUM('Aset','Liabilitas','Ekuitas','Pendapatan','Biaya') NOT NULL,
  `normal_balance` ENUM('Debit','Kredit') NOT NULL,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data untuk tabel `accounts`
--
INSERT INTO `accounts` (`account_id`, `account_code`, `account_name`, `account_type`, `normal_balance`) VALUES
(1, '101', 'Kas', 'Aset', 'Debit'),
(2, '102', 'Perlengkapan', 'Aset', 'Debit'),
(3, '201', 'Utang Usaha', 'Liabilitas', 'Kredit'),
(4, '301', 'Modal Awal', 'Ekuitas', 'Kredit'),
(5, '401', 'Pendapatan Jasa', 'Pendapatan', 'Kredit'),
(6, '501', 'Biaya Gaji', 'Biaya', 'Debit'),
(7, '502', 'Biaya Listrik', 'Biaya', 'Debit'),
(8, '103', 'Bank BCA', 'Aset', 'Debit'),
(9, '104', 'Bank Mandiri', 'Aset', 'Debit'),
(10, '503', 'Biaya Langsung (HPP/Modal)', 'Biaya', 'Debit');

-- --------------------------------------------------------

--
-- Struktur dari tabel `transactions`
--
CREATE TABLE `transactions` (
  `transaction_id` INT(11) NOT NULL AUTO_INCREMENT,
  `transaction_date` DATE NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `user_id_fk` (`user_id`),
  CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Struktur dari tabel `transaction_details`
--
CREATE TABLE `transaction_details` (
  `detail_id` INT(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` INT(11) NOT NULL,
  `account_id` INT(11) NOT NULL,
  `type` ENUM('Debit','Kredit') NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  PRIMARY KEY (`detail_id`),
  KEY `transaction_id_fk` (`transaction_id`),
  KEY `account_id_fk` (`account_id`),
  CONSTRAINT `transaction_id_fk` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`transaction_id`) ON DELETE CASCADE,
  CONSTRAINT `account_id_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Struktur dari tabel `categories`
--
CREATE TABLE `categories` (
  `category_id` INT(11) NOT NULL AUTO_INCREMENT,
  `category_name` VARCHAR(100) NOT NULL,
  `category_type` ENUM('Pendapatan','Biaya') NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data untuk tabel `categories`
--
INSERT INTO `categories` (`category_id`, `category_name`, `category_type`) VALUES
(1, 'Gaji Karyawan', 'Biaya'),
(2, 'Penjualan Jasa A', 'Pendapatan'),
(3, 'Biaya Operasional', 'Biaya'),
(4, 'Biaya Marketing', 'Biaya'); -- <-- INI ADALAH BARIS YANG DIPERBAIKI

-- --------------------------------------------------------

--
-- Struktur dari tabel `bank_accounts`
--
CREATE TABLE `bank_accounts` (
  `bank_account_id` INT(11) NOT NULL AUTO_INCREMENT,
  `bank_name` VARCHAR(50) NOT NULL,
  `account_holder_name` VARCHAR(100) NOT NULL,
  `account_number` VARCHAR(50) NOT NULL,
  `account_id_coa` INT(11) NOT NULL COMMENT 'FK ke tabel accounts (akun Aset)',
  PRIMARY KEY (`bank_account_id`),
  KEY `account_id_coa_fk` (`account_id_coa`),
  CONSTRAINT `account_id_coa_fk` FOREIGN KEY (`account_id_coa`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data untuk tabel `bank_accounts`
--
INSERT INTO `bank_accounts` (`bank_account_id`, `bank_name`, `account_holder_name`, `account_number`, `account_id_coa`) VALUES
(1, 'Bank BCA', 'PT. Usaha Jaya', '1234567890', 8),
(2, 'Bank Mandiri', 'PT. Usaha Jaya', '0987654321', 9);


COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;