-- https://www.phpmyadmin.net/
--
-- Waktu pembuatan: 01 Nov 2024 pada 10:00
-- Versi server: 10.4.27-MariaDB
-- Versi PHP: 8.2.0

START TRANSACTION;


--
-- 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 `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;

