--
-- Host: localhost    Database: apps_kasir_griya_finance
-- ------------------------------------------------------


--
-- Table structure for table `barang`
--

CREATE TABLE `barang` (
  `id_barang` int unsigned NOT NULL AUTO_INCREMENT,
  `kode_barang` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `nama_barang` varchar(200) COLLATE utf8mb4_general_ci NOT NULL,
  `foto` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `deskripsi` text COLLATE utf8mb4_general_ci,
  `harga` decimal(12,2) NOT NULL DEFAULT '0.00',
  `stok` int NOT NULL DEFAULT '0',
  `stok_minimum` int DEFAULT '0',
  `id_kategori` int unsigned DEFAULT NULL,
  `satuan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'pcs',
  `foto_barang` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status_aktif` enum('aktif','nonaktif') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'aktif',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id_barang`),
  UNIQUE KEY `unique_kode_barang` (`kode_barang`),
  KEY `idx_kategori` (`id_kategori`),
  KEY `idx_status` (`status_aktif`),
  KEY `idx_nama` (`nama_barang`),
  KEY `fk_barang_created_by` (`created_by`),
  CONSTRAINT `fk_barang_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id_user`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_barang_kategori` FOREIGN KEY (`id_kategori`) REFERENCES `kategori_barang` (`id_kategori`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `barang`
--

INSERT INTO `barang` VALUES (1,'BRG001','Nasi Goreng Spesial','uploads/menu/1_1764721805_6849e7.webp','Nasi goreng dengan telur, ayam, dan kerupuk',15000.00,11,10,1,'porsi',NULL,'aktif','2025-12-03 00:05:22','2025-12-18 11:47:45',1),(2,'BRG002','Es Teh Manis','uploads/menu/2_1765700466_5add5d.png','Es teh manis segar',5000.00,64,20,2,'gelas',NULL,'aktif','2025-12-03 00:05:22','2025-12-18 11:47:45',1),(3,'BRG003','Kerupuk','uploads/menu/3_1765700599_6df137.png','Kerupuk goreng renyah',3000.00,174,50,3,'bungkus',NULL,'aktif','2025-12-03 00:05:22','2025-12-18 11:47:45',1),(4,'B005','Soto Ayam','uploads/menu/4_1765930655_c0c76c.webp','Soto Ayam Khas Banyuwangi',12000.00,39,20,1,'pcs',NULL,'aktif','2025-12-17 07:13:58','2025-12-18 11:47:45',1),(6,'TEST-KODE','Tesst',NULL,'aa',1000.00,100,20,1,'pcs',NULL,'aktif','2025-12-22 05:38:48','2025-12-22 05:39:09',1);

--
-- Table structure for table `kategori_barang`
--

CREATE TABLE `kategori_barang` (
  `id_kategori` int unsigned NOT NULL AUTO_INCREMENT,
  `nama_kategori` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `deskripsi` text COLLATE utf8mb4_general_ci,
  `icon` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `urutan` int DEFAULT '0',
  `status_aktif` enum('aktif','nonaktif') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'aktif',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_kategori`),
  UNIQUE KEY `unique_nama_kategori` (`nama_kategori`),
  KEY `idx_status` (`status_aktif`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `kategori_barang`
--

INSERT INTO `kategori_barang` VALUES (1,'Makanan','Menu makanan utama','bi-egg-fried',1,'aktif','2025-12-03 00:05:22','2025-12-03 00:05:22'),(2,'Minuman','Menu minuman','bi-cup-straw',2,'aktif','2025-12-03 00:05:22','2025-12-03 00:05:22'),(3,'Snack','Menu snack dan camilan','bi-bag',3,'aktif','2025-12-03 00:05:22','2025-12-03 00:05:22');

--
-- Table structure for table `laporan_barang_terlaris`
--

CREATE TABLE `laporan_barang_terlaris` (
  `id_laporan` int unsigned NOT NULL AUTO_INCREMENT,
  `id_barang` int unsigned NOT NULL,
  `periode_bulan` int NOT NULL,
  `periode_tahun` int NOT NULL,
  `total_terjual` int NOT NULL DEFAULT '0',
  `total_pendapatan` decimal(12,2) NOT NULL DEFAULT '0.00',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_laporan`),
  UNIQUE KEY `unique_barang_periode` (`id_barang`,`periode_bulan`,`periode_tahun`),
  KEY `idx_periode` (`periode_tahun`,`periode_bulan`),
  KEY `idx_barang` (`id_barang`),
  CONSTRAINT `fk_laporan_barang` FOREIGN KEY (`id_barang`) REFERENCES `barang` (`id_barang`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `laporan_barang_terlaris`
--


--
-- Table structure for table `laporan_keuangan`
--

CREATE TABLE `laporan_keuangan` (
  `id_laporan` int unsigned NOT NULL AUTO_INCREMENT,
  `tanggal` date NOT NULL,
  `total_transaksi` int NOT NULL DEFAULT '0',
  `total_pendapatan` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_diskon` decimal(12,2) DEFAULT '0.00',
  `metode_pembayaran_tunai` decimal(12,2) DEFAULT '0.00',
  `metode_pembayaran_qris` decimal(12,2) DEFAULT '0.00',
  `metode_pembayaran_lainnya` decimal(12,2) DEFAULT '0.00',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_laporan`),
  UNIQUE KEY `unique_tanggal` (`tanggal`),
  KEY `idx_tanggal` (`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `laporan_keuangan`
--


--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id_role` int unsigned NOT NULL AUTO_INCREMENT,
  `nama_role` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `deskripsi` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_role`),
  UNIQUE KEY `unique_nama_role` (`nama_role`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` VALUES (1,'owner','Pemilik usaha, memiliki akses penuh ke semua fitur','2025-12-03 00:05:22','2025-12-03 00:05:22'),(2,'kasir','Kasir yang melakukan transaksi penjualan','2025-12-03 00:05:22','2025-12-03 00:05:22');

--
-- Table structure for table `stok_log`
--

CREATE TABLE `stok_log` (
  `id_log` int unsigned NOT NULL AUTO_INCREMENT,
  `id_barang` int unsigned NOT NULL,
  `jenis_transaksi` enum('masuk','keluar','penyesuaian') COLLATE utf8mb4_general_ci NOT NULL,
  `jumlah` int NOT NULL,
  `stok_sebelum` int NOT NULL,
  `stok_sesudah` int NOT NULL,
  `keterangan` text COLLATE utf8mb4_general_ci,
  `id_transaksi` int unsigned DEFAULT NULL COMMENT 'Jika terkait transaksi penjualan',
  `id_user` int unsigned DEFAULT NULL COMMENT 'User yang melakukan perubahan stok',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_log`),
  KEY `idx_barang` (`id_barang`),
  KEY `idx_transaksi` (`id_transaksi`),
  KEY `idx_user` (`id_user`),
  KEY `idx_tanggal` (`created_at`),
  CONSTRAINT `fk_stok_log_barang` FOREIGN KEY (`id_barang`) REFERENCES `barang` (`id_barang`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_stok_log_transaksi` FOREIGN KEY (`id_transaksi`) REFERENCES `transaksi` (`id_transaksi`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_stok_log_user` FOREIGN KEY (`id_user`) REFERENCES `users` (`id_user`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `stok_log`
--


--
-- Table structure for table `transaksi`
--

CREATE TABLE `transaksi` (
  `id_transaksi` int unsigned NOT NULL AUTO_INCREMENT,
  `kode_pembayaran` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `tanggal_transaksi` date NOT NULL,
  `jam_transaksi` time NOT NULL,
  `total_item` int NOT NULL DEFAULT '0',
  `total_harga` decimal(12,2) NOT NULL DEFAULT '0.00',
  `diskon` decimal(12,2) DEFAULT '0.00',
  `total_bayar` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status_pembayaran` enum('menunggu','dibayar','batal') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'menunggu',
  `metode_pembayaran` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `catatan` text COLLATE utf8mb4_general_ci,
  `dibuat_oleh` int unsigned DEFAULT NULL COMMENT 'User yang membuat transaksi (boleh null jika User Non Login)',
  `diacc_kasir` int unsigned DEFAULT NULL COMMENT 'User kasir yang ACC transaksi',
  `tanggal_acc` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_transaksi`),
  UNIQUE KEY `unique_kode_pembayaran` (`kode_pembayaran`),
  KEY `idx_tanggal` (`tanggal_transaksi`),
  KEY `idx_status` (`status_pembayaran`),
  KEY `idx_dibuat_oleh` (`dibuat_oleh`),
  KEY `idx_diacc_kasir` (`diacc_kasir`),
  CONSTRAINT `fk_transaksi_diacc_kasir` FOREIGN KEY (`diacc_kasir`) REFERENCES `users` (`id_user`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_transaksi_dibuat_oleh` FOREIGN KEY (`dibuat_oleh`) REFERENCES `users` (`id_user`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `transaksi`
--

INSERT INTO `transaksi` VALUES (1,'PAY-20251214-123430-86D82E','2025-12-14','12:34:30',3,23000.00,0.00,23000.00,'dibayar',NULL,NULL,NULL,2,'2025-12-14 12:54:58','2025-12-14 12:34:30','2025-12-14 12:54:58'),(2,'PAY-20251214-131755-B8C9E7','2025-12-14','13:17:55',3,33000.00,0.00,33000.00,'dibayar',NULL,NULL,NULL,2,'2025-12-14 15:38:15','2025-12-14 13:17:55','2025-12-14 15:38:15'),(3,'PAY-20251214-143810-C2574A','2025-12-14','14:38:10',6,46000.00,0.00,46000.00,'menunggu',NULL,NULL,NULL,NULL,NULL,'2025-12-14 14:38:10','2025-12-14 14:38:10'),(4,'PAY-20251214-144634-48F0D0','2025-12-14','14:46:34',7,73000.00,0.00,73000.00,'dibayar',NULL,NULL,NULL,2,'2025-12-14 15:39:43','2025-12-14 14:46:34','2025-12-14 15:39:43'),(5,'PAY-20251214-150333-626183','2025-12-14','15:03:33',12,92000.00,0.00,92000.00,'menunggu',NULL,NULL,NULL,NULL,NULL,'2025-12-14 15:03:33','2025-12-14 15:03:33'),(6,'PAY-20251214-153633-323C2F','2025-12-14','15:36:33',15,85000.00,0.00,85000.00,'dibayar',NULL,NULL,NULL,2,'2025-12-14 15:41:46','2025-12-14 15:36:33','2025-12-14 15:41:46'),(7,'PAY-20251214-154011-379D5B','2025-12-14','15:40:11',15,225000.00,0.00,225000.00,'dibayar',NULL,NULL,NULL,2,'2025-12-14 15:40:44','2025-12-14 15:40:11','2025-12-14 15:40:44'),(8,'PAY-20251214-154423-A052F1','2025-12-14','15:44:23',20,100000.00,0.00,100000.00,'menunggu',NULL,NULL,NULL,NULL,NULL,'2025-12-14 15:44:23','2025-12-14 15:44:23'),(9,'PAY-20251214-160616-3B8EA9','2025-12-14','16:06:16',10,84000.00,0.00,84000.00,'menunggu',NULL,NULL,NULL,NULL,NULL,'2025-12-14 16:06:16','2025-12-14 16:06:16'),(10,'PAY-20251214-165446-5D4C08','2025-12-14','16:54:46',2,30000.00,0.00,30000.00,'menunggu',NULL,NULL,NULL,NULL,NULL,'2025-12-14 16:54:46','2025-12-14 16:54:46'),(11,'PAY-20251217-070841-354352','2025-12-17','07:08:41',4,26000.00,0.00,26000.00,'dibayar',NULL,NULL,NULL,2,'2025-12-17 07:10:50','2025-12-17 07:08:41','2025-12-17 07:10:50'),(12,'PAY-20251218-114745-C21179','2025-12-18','11:47:45',5,38000.00,0.00,38000.00,'dibayar',NULL,NULL,NULL,2,'2025-12-18 11:49:06','2025-12-18 11:47:45','2025-12-18 11:49:06');

--
-- Table structure for table `transaksi_detail`
--

CREATE TABLE `transaksi_detail` (
  `id_detail` int unsigned NOT NULL AUTO_INCREMENT,
  `id_transaksi` int unsigned NOT NULL,
  `id_barang` int unsigned NOT NULL,
  `jumlah` int NOT NULL DEFAULT '1',
  `harga_satuan` decimal(12,2) NOT NULL,
  `diskon_item` decimal(12,2) DEFAULT '0.00',
  `subtotal` decimal(12,2) NOT NULL,
  `catatan_item` text COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_detail`),
  KEY `idx_transaksi` (`id_transaksi`),
  KEY `idx_barang` (`id_barang`),
  CONSTRAINT `fk_detail_barang` FOREIGN KEY (`id_barang`) REFERENCES `barang` (`id_barang`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_detail_transaksi` FOREIGN KEY (`id_transaksi`) REFERENCES `transaksi` (`id_transaksi`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `transaksi_detail`
--

INSERT INTO `transaksi_detail` VALUES (1,1,1,1,15000.00,0.00,15000.00,NULL,'2025-12-14 12:34:30'),(2,1,2,1,5000.00,0.00,5000.00,NULL,'2025-12-14 12:34:30'),(3,1,3,1,3000.00,0.00,3000.00,NULL,'2025-12-14 12:34:30'),(4,2,1,2,15000.00,0.00,30000.00,NULL,'2025-12-14 13:17:55'),(5,2,3,1,3000.00,0.00,3000.00,NULL,'2025-12-14 13:17:55'),(6,3,1,2,15000.00,0.00,30000.00,NULL,'2025-12-14 14:38:10'),(7,3,2,2,5000.00,0.00,10000.00,NULL,'2025-12-14 14:38:10'),(8,3,3,2,3000.00,0.00,6000.00,NULL,'2025-12-14 14:38:10'),(9,4,1,4,15000.00,0.00,60000.00,NULL,'2025-12-14 14:46:34'),(10,4,2,2,5000.00,0.00,10000.00,NULL,'2025-12-14 14:46:34'),(11,4,3,1,3000.00,0.00,3000.00,NULL,'2025-12-14 14:46:34'),(12,5,1,4,15000.00,0.00,60000.00,NULL,'2025-12-14 15:03:33'),(13,5,2,4,5000.00,0.00,20000.00,NULL,'2025-12-14 15:03:33'),(14,5,3,4,3000.00,0.00,12000.00,NULL,'2025-12-14 15:03:33'),(15,6,1,3,15000.00,0.00,45000.00,NULL,'2025-12-14 15:36:33'),(16,6,2,2,5000.00,0.00,10000.00,NULL,'2025-12-14 15:36:33'),(17,6,3,10,3000.00,0.00,30000.00,NULL,'2025-12-14 15:36:33'),(18,7,1,15,15000.00,0.00,225000.00,NULL,'2025-12-14 15:40:11'),(19,8,2,20,5000.00,0.00,100000.00,NULL,'2025-12-14 15:44:23'),(20,9,1,4,15000.00,0.00,60000.00,NULL,'2025-12-14 16:06:16'),(21,9,2,3,5000.00,0.00,15000.00,NULL,'2025-12-14 16:06:16'),(22,9,3,3,3000.00,0.00,9000.00,NULL,'2025-12-14 16:06:16'),(23,10,1,2,15000.00,0.00,30000.00,NULL,'2025-12-14 16:54:46'),(24,11,1,1,15000.00,0.00,15000.00,NULL,'2025-12-17 07:08:41'),(25,11,2,1,5000.00,0.00,5000.00,NULL,'2025-12-17 07:08:41'),(26,11,3,2,3000.00,0.00,6000.00,NULL,'2025-12-17 07:08:41'),(27,12,1,1,15000.00,0.00,15000.00,NULL,'2025-12-18 11:47:45'),(28,12,2,1,5000.00,0.00,5000.00,NULL,'2025-12-18 11:47:45'),(29,12,3,2,3000.00,0.00,6000.00,NULL,'2025-12-18 11:47:45'),(30,12,4,1,12000.00,0.00,12000.00,NULL,'2025-12-18 11:47:45');


