-- ========================================================
-- Database Dump for POS App CI4
-- Universal Compatibility (cPanel MariaDB / MySQL 5.7 / MySQL 8.0)
-- Generated at: 2026-09-10 10:29:06
-- ========================================================

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = '+07:00';

DROP TABLE IF EXISTS `cashier_schedules`;
DROP TABLE IF EXISTS `cashier_shifts`;
DROP TABLE IF EXISTS `expenses`;
DROP TABLE IF EXISTS `incomes`;
DROP TABLE IF EXISTS `product_bundles`;
DROP TABLE IF EXISTS `product_units`;
DROP TABLE IF EXISTS `production_materials`;
DROP TABLE IF EXISTS `productions`;
DROP TABLE IF EXISTS `products`;
DROP TABLE IF EXISTS `raw_materials`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `stock_transfers`;
DROP TABLE IF EXISTS `stores`;
DROP TABLE IF EXISTS `transaction_items`;
DROP TABLE IF EXISTS `transactions`;
DROP TABLE IF EXISTS `units`;
DROP TABLE IF EXISTS `users`;

-- --------------------------------------------------------
-- Table structure for table `cashier_schedules`
-- --------------------------------------------------------
CREATE TABLE `cashier_schedules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `store_id` int NOT NULL,
  `user_id` int NOT NULL,
  `schedule_type` enum('recurring','specific_date') DEFAULT 'recurring',
  `day_of_week` enum('monday','tuesday','wednesday','thursday','friday','saturday','sunday') DEFAULT NULL,
  `specific_date` date DEFAULT NULL,
  `shift_name` varchar(50) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `grace_before_minutes` int NOT NULL DEFAULT '30',
  `grace_after_minutes` int NOT NULL DEFAULT '60',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_store_user_sched` (`store_id`,`user_id`),
  KEY `idx_day_type` (`schedule_type`,`day_of_week`,`specific_date`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `cashier_shifts`
-- --------------------------------------------------------
CREATE TABLE `cashier_shifts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `store_id` int NOT NULL,
  `user_id` int NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  `starting_cash` decimal(15,2) NOT NULL DEFAULT '0.00',
  `cash_sales` decimal(15,2) NOT NULL DEFAULT '0.00',
  `non_cash_sales` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_transactions` int NOT NULL DEFAULT '0',
  `expected_cash` decimal(15,2) NOT NULL DEFAULT '0.00',
  `actual_cash` decimal(15,2) DEFAULT NULL,
  `difference` decimal(15,2) DEFAULT NULL,
  `status` enum('open','closed') DEFAULT 'open',
  `deposit_status` enum('pending','deposited','verified') DEFAULT 'pending',
  `deposit_time` datetime DEFAULT NULL,
  `deposit_notes` varchar(255) DEFAULT NULL,
  `notes` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_store_user` (`store_id`,`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `cashier_shifts`
INSERT INTO `cashier_shifts` VALUES 
('3', '2', '1', '2026-09-10 17:03:01', '2026-09-10 17:03:40', '100000.00', '0.00', '0.00', '0', '100000.00', '100000.00', '0.00', 'closed', 'pending', NULL, NULL, '', '2026-09-10 17:03:01', '2026-09-10 17:03:40');

-- --------------------------------------------------------
-- Table structure for table `expenses`
-- --------------------------------------------------------
CREATE TABLE `expenses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_id` int DEFAULT NULL,
  `category` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `user_id` int NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `store_id` (`store_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `expenses`
INSERT INTO `expenses` VALUES 
('1', '1', 'Sewa alat', 'Just tes', '1000000.00', '1', '2025-09-11 11:04:16');

-- --------------------------------------------------------
-- Table structure for table `incomes`
-- --------------------------------------------------------
CREATE TABLE `incomes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_id` int DEFAULT NULL,
  `category` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `user_id` int NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `store_id` (`store_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `incomes`
INSERT INTO `incomes` VALUES 
('1', NULL, 'Modal Awal', 'Modal Awal Disetor Koperasi Sekolah', '25000000.00', '1', '2026-08-01 08:00:00'),
('2', '1', 'Suntikan Dana/Modal Tambahan', 'Modal Kas Kecil Kantin', '1500000.00', '1', '2026-08-15 09:00:00');

-- --------------------------------------------------------
-- Table structure for table `product_bundles`
-- --------------------------------------------------------
CREATE TABLE `product_bundles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bundle_product_id` int NOT NULL,
  `item_product_id` int NOT NULL,
  `item_product_unit_id` int NOT NULL,
  `quantity` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `bundle_product_id` (`bundle_product_id`),
  KEY `item_product_id` (`item_product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `product_bundles`
INSERT INTO `product_bundles` VALUES 
('1', '304', '301', '301', '1'),
('2', '304', '302', '302', '1'),
('3', '304', '303', '303', '1');

-- --------------------------------------------------------
-- Table structure for table `product_units`
-- --------------------------------------------------------
CREATE TABLE `product_units` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `unit_id` int NOT NULL,
  `conversion_factor` float DEFAULT '1',
  `price` decimal(10,2) NOT NULL,
  `purchase_price` decimal(10,2) NOT NULL,
  `production_cost` decimal(10,2) DEFAULT NULL,
  `warehouse_stock` int NOT NULL,
  `outlet_stock` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `unit_id` (`unit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=305 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `product_units`
INSERT INTO `product_units` VALUES 
('2', '1', '1', '1', '50000.00', '30000.00', '0.00', '50', '20'),
('3', '2', '1', '1', '2000.00', '1200.00', '0.00', '40', '0'),
('4', '1', '1', '1', '51000.00', '31000.00', '0.00', '0', '20'),
('5', '3', '1', '1', '5000.00', '4500.00', '0.00', '100', '46'),
('101', '101', '2', '1', '12000.00', '7000.00', NULL, '0', '50'),
('102', '102', '3', '1', '4000.00', '1500.00', NULL, '0', '100'),
('103', '103', '2', '1', '8000.00', '4500.00', NULL, '0', '30'),
('104', '104', '1', '1', '4000.00', '2500.00', NULL, '0', '79'),
('201', '201', '1', '1', '55000.00', '35000.00', NULL, '10', '25'),
('202', '202', '1', '1', '60000.00', '38000.00', NULL, '15', '30'),
('203', '203', '1', '1', '65000.00', '42000.00', NULL, '10', '20'),
('301', '301', '1', '1', '45000.00', '30000.00', NULL, '20', '40'),
('302', '302', '1', '1', '40000.00', '28000.00', NULL, '20', '40'),
('303', '303', '1', '1', '45000.00', '30000.00', NULL, '20', '40'),
('304', '304', '4', '1', '120000.00', '88000.00', NULL, '0', '30');

-- --------------------------------------------------------
-- Table structure for table `production_materials`
-- --------------------------------------------------------
CREATE TABLE `production_materials` (
  `id` int NOT NULL AUTO_INCREMENT,
  `production_id` int NOT NULL,
  `raw_material_id` int NOT NULL,
  `quantity_used` decimal(12,2) NOT NULL,
  `cost_per_unit` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `production_id` (`production_id`),
  KEY `raw_material_id` (`raw_material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `productions`
-- --------------------------------------------------------
CREATE TABLE `productions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `production_no` varchar(50) NOT NULL,
  `store_id` int NOT NULL,
  `product_id` int NOT NULL,
  `product_unit_id` int NOT NULL,
  `quantity_produced` int NOT NULL,
  `labor_cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_material_cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  `cost_per_piece` decimal(15,2) NOT NULL DEFAULT '0.00',
  `production_date` date NOT NULL,
  `notes` text,
  `user_id` int NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `store_id` (`store_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `products`
-- --------------------------------------------------------
CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `store_id` int NOT NULL,
  `product_type` enum('standard','manufactured','bundle') NOT NULL DEFAULT 'standard',
  `description` text,
  `product_image` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_products_stores` (`store_id`)
) ENGINE=InnoDB AUTO_INCREMENT=305 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `products`
INSERT INTO `products` VALUES 
('1', 'Tes', 'ffgy-ff', 'PRD000001', '1', 'standard', 'tyfgh', 'uploads/products/1757519054_62aafaa9dd04a138c5fa.png', '2025-09-10 14:41:46'),
('2', 'Beng beng', 'bb', 'PRD000002', '1', 'standard', '', 'uploads/products/1757520175_b2ec8c4101ca63daec92.jpg', '2025-09-10 22:54:49'),
('3', 'Susu Kotak', NULL, 'PRD000003', '1', 'standard', NULL, NULL, '2025-09-12 14:04:17'),
('101', 'Nasi Goreng Spesial Telur', 'KTN-NASGOR', NULL, '1', 'standard', 'Nasi goreng kantin dengan telur ceplok', NULL, '2026-09-02 01:19:07'),
('102', 'Es Teh Manis', 'KTN-ESTEH', NULL, '1', 'standard', 'Es teh manis segar', NULL, '2026-09-02 01:19:07'),
('103', 'Roti Bakar Coklat Keju', 'KTN-ROTBAK', NULL, '1', 'standard', 'Roti bakar isi coklat keju', NULL, '2026-09-02 01:19:07'),
('104', 'Air Mineral 600ml', 'KTN-AQUA', NULL, '1', 'standard', 'Air mineral botol dingin', NULL, '2026-09-02 01:19:07'),
('201', 'Baju Seragam Putih SD/SMP Uk. S', 'SRG-PUTIH-S', NULL, '2', 'manufactured', 'Kemeja seragam putih lengan pendek ukuran S', NULL, '2026-09-02 01:19:07'),
('202', 'Baju Seragam Putih SD/SMP Uk. M', 'SRG-PUTIH-M', 'PRD000202', '2', 'manufactured', 'Kemeja seragam putih lengan pendek ukuran M', NULL, '2026-09-02 01:19:07'),
('203', 'Baju Seragam Pramuka Uk. M', 'SRG-PRAMUKA-M', NULL, '2', 'manufactured', 'Kemeja seragam pramuka lengan pendek ukuran M', NULL, '2026-09-02 01:19:07'),
('301', 'Buku Matematika Kelas 7 Kurikulum Merdeka', 'BK-MTK-7', NULL, '3', 'standard', 'Buku teks utama matematika', NULL, '2026-09-02 01:19:07'),
('302', 'Buku Bahasa Indonesia Kelas 7', 'BK-BIN-7', NULL, '3', 'standard', 'Buku teks bahasa indonesia', NULL, '2026-09-02 01:19:07'),
('303', 'Buku IPA Terpadu Kelas 7', 'BK-IPA-7', NULL, '3', 'standard', 'Buku teks ilmu pengetahuan alam', NULL, '2026-09-02 01:19:07'),
('304', 'Paket Lengkap Buku Kelas 7 Semester 1 (3 Buku)', 'PKT-BK-7-SM1', NULL, '3', 'bundle', 'Paket hemat buku pelajaran semester 1', NULL, '2026-09-02 01:19:07');

-- --------------------------------------------------------
-- Table structure for table `raw_materials`
-- --------------------------------------------------------
CREATE TABLE `raw_materials` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_id` int NOT NULL,
  `material_name` varchar(255) NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `unit` varchar(50) NOT NULL DEFAULT 'meter',
  `stock` decimal(12,2) NOT NULL DEFAULT '0.00',
  `cost_per_unit` decimal(15,2) NOT NULL DEFAULT '0.00',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `store_id` (`store_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `raw_materials`
INSERT INTO `raw_materials` VALUES 
('1', '2', 'Kain Katun Tetoron Putih', 'MAT-KAIN-PUTIH', 'meter', '150.00', '25000.00', '2026-09-02 01:19:07', '2026-09-02 01:19:07'),
('2', '2', 'Kain Drill Coklat Pramuka', 'MAT-KAIN-PRAMUKA', 'meter', '100.00', '32000.00', '2026-09-02 01:19:07', '2026-09-02 01:19:07'),
('3', '2', 'Kancing Kemeja Putih', 'MAT-KANCING-PUTIH', 'gross', '10.00', '18000.00', '2026-09-02 01:19:07', '2026-09-02 01:19:07'),
('4', '2', 'Kancing Pramuka', 'MAT-KANCING-PRAMUKA', 'gross', '8.00', '20000.00', '2026-09-02 01:19:07', '2026-09-02 01:19:07'),
('5', '2', 'Benang Jahit Putih', 'MAT-BENANG-PUTIH', 'roll', '20.00', '6000.00', '2026-09-02 01:19:07', '2026-09-02 01:19:07');

-- --------------------------------------------------------
-- Table structure for table `roles`
-- --------------------------------------------------------
CREATE TABLE `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `roles`
INSERT INTO `roles` VALUES 
('1', 'superadmin'),
('2', 'admin_unit'),
('3', 'kasir');

-- --------------------------------------------------------
-- Table structure for table `stock_transfers`
-- --------------------------------------------------------
CREATE TABLE `stock_transfers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_unit_id` int NOT NULL,
  `quantity` int NOT NULL,
  `transfer_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_unit_id` (`product_unit_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `stores`
-- --------------------------------------------------------
CREATE TABLE `stores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_name` varchar(255) NOT NULL,
  `store_type` enum('kantin','konveksi','buku','umum') NOT NULL DEFAULT 'umum',
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `paper_size` varchar(10) NOT NULL DEFAULT '58mm',
  `print_method` varchar(20) NOT NULL DEFAULT 'browser',
  `receipt_footer` varchar(255) NOT NULL DEFAULT 'Terima kasih telah berbelanja!',
  `enforce_schedule` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `stores`
INSERT INTO `stores` VALUES 
('1', 'Kantin Sekolah', 'kantin', 'Jl. Raya No. 1, Kotas', '', '58mm', 'browser', 'Terima kasih telah berbelanja!', '1'),
('2', 'Konveksi & Seragam', 'konveksi', 'yr', '', '58mm', 'browser', 'Terima kasih telah berbelanja!', '1'),
('3', 'Toko Buku & ATK', 'buku', 'Gedung Koperasi Lantai 1', '08123456789', '58mm', 'browser', 'Terima kasih telah berbelanja!', '1');

-- --------------------------------------------------------
-- Table structure for table `transaction_items`
-- --------------------------------------------------------
CREATE TABLE `transaction_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transaction_id` int NOT NULL,
  `product_id` int NOT NULL,
  `product_unit_id` int NOT NULL,
  `quantity` int NOT NULL,
  `price_per_unit` decimal(10,2) NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `transaction_id` (`transaction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `transaction_items`
INSERT INTO `transaction_items` VALUES 
('1', '2', '2', '3', '2', '2000.00', '4000.00'),
('2', '3', '1', '2', '1', '50000.00', '50000.00'),
('3', '4', '2', '3', '2', '2000.00', '4000.00'),
('4', '5', '2', '3', '1', '2000.00', '2000.00'),
('5', '6', '2', '3', '1', '2000.00', '2000.00'),
('6', '7', '2', '3', '2', '2000.00', '4000.00'),
('7', '8', '2', '3', '9', '2000.00', '18000.00'),
('8', '10', '1', '2', '3', '50000.00', '150000.00'),
('9', '10', '2', '3', '2', '2000.00', '4000.00'),
('10', '11', '2', '3', '2', '2000.00', '4000.00'),
('11', '12', '3', '5', '2', '5000.00', '10000.00'),
('12', '13', '3', '5', '1', '5000.00', '5000.00'),
('13', '14', '1', '2', '1', '50000.00', '50000.00'),
('14', '14', '2', '3', '1', '2000.00', '2000.00'),
('15', '15', '1', '2', '3', '50000.00', '150000.00'),
('16', '16', '1', '2', '2', '50000.00', '100000.00'),
('17', '17', '2', '3', '1', '2000.00', '2000.00'),
('18', '18', '1', '2', '1', '50000.00', '50000.00'),
('19', '19', '1', '2', '2', '0.00', '100000.00'),
('20', '19', '3', '5', '4', '0.00', '20000.00'),
('21', '20', '3', '5', '3', '0.00', '15000.00'),
('22', '21', '1', '2', '3', '0.00', '150000.00'),
('23', '22', '1', '2', '3', '50000.00', '150000.00'),
('24', '23', '4', '6', '3', '15000.00', '45000.00'),
('25', '23', '5', '7', '2', '12000.00', '24000.00'),
('26', '24', '3', '5', '1', '5000.00', '5000.00'),
('27', '25', '104', '104', '1', '4000.00', '4000.00');

-- --------------------------------------------------------
-- Table structure for table `transactions`
-- --------------------------------------------------------
CREATE TABLE `transactions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transaction_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `total_amount` decimal(10,2) NOT NULL,
  `user_id` int NOT NULL,
  `store_id` int NOT NULL,
  `shift_id` int unsigned DEFAULT NULL,
  `payment_method` enum('tunai','qris','lainnya') NOT NULL DEFAULT 'tunai',
  `nominal_bayar` decimal(15,2) NOT NULL DEFAULT '0.00',
  `kembalian` decimal(15,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `store_id` (`store_id`),
  KEY `idx_shift_id` (`shift_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `transactions`
INSERT INTO `transactions` VALUES 
('1', '2025-09-10 16:32:54', '6000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('2', '2025-09-10 16:41:18', '4000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('3', '2025-09-10 16:42:15', '50000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('4', '2025-09-10 16:49:47', '4000.00', '3', '1', NULL, 'tunai', '0.00', '0.00'),
('5', '2025-09-10 23:52:22', '2000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('6', '2025-09-10 23:56:26', '2000.00', '3', '1', NULL, 'tunai', '0.00', '0.00'),
('7', '2025-09-11 10:19:26', '4000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('8', '2025-09-11 11:12:04', '18000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('10', '2025-09-19 18:53:29', '154000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('11', '2025-09-19 19:08:33', '4000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('12', '2025-09-19 19:09:55', '10000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('13', '2025-09-19 20:54:18', '5000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('14', '2025-09-20 15:14:51', '52000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('15', '2025-09-20 15:22:30', '150000.00', '3', '1', NULL, 'tunai', '0.00', '0.00'),
('16', '2025-09-20 15:25:48', '100000.00', '4', '1', NULL, 'tunai', '0.00', '0.00'),
('17', '2025-09-20 15:36:44', '2000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('18', '2025-09-20 15:50:42', '50000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('19', '2025-09-20 19:24:39', '120000.00', '1', '1', NULL, 'tunai', '0.00', '0.00'),
('20', '2025-09-20 19:29:17', '15000.00', '1', '1', NULL, 'tunai', '0.00', '5000.00'),
('21', '2025-09-20 19:30:26', '150000.00', '1', '1', NULL, 'tunai', '200000.00', '50000.00'),
('22', '2025-09-20 19:35:27', '150000.00', '1', '1', NULL, 'tunai', '200000.00', '50000.00'),
('23', '2025-09-21 22:09:23', '69000.00', '1', '1', NULL, 'qris', '69000.00', '0.00'),
('24', '2026-09-07 16:39:00', '5000.00', '4', '1', NULL, 'tunai', '5000.00', '0.00'),
('25', '2026-09-07 16:39:49', '4000.00', '4', '1', NULL, 'tunai', '5000.00', '1000.00');

-- --------------------------------------------------------
-- Table structure for table `units`
-- --------------------------------------------------------
CREATE TABLE `units` (
  `id` int NOT NULL AUTO_INCREMENT,
  `unit_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `units`
INSERT INTO `units` VALUES 
('1', 'Pcs'),
('2', 'Porsi'),
('3', 'Gelas'),
('4', 'Paket'),
('5', 'Set');

-- --------------------------------------------------------
-- Table structure for table `users`
-- --------------------------------------------------------
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `store_id` int DEFAULT NULL,
  `role_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `fk_users_stores` (`store_id`),
  KEY `fk_users_roles` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `users`
INSERT INTO `users` VALUES 
('1', 'superadmin', '$2y$10$WHZATkzyS6b0wMxEGjD14O8cRAIWEHHtPC396u6I1col9OP2fKyt2', 'superadmin@example.com', '2025-09-10 05:06:07', '2025-09-10 05:06:07', NULL, '1'),
('3', 'kasir_1', '$2y$10$HbNjzujl79EP6wXWlLUHGO7iltH03WGWAiD0Rc3yFGQXoI4Z9KGPu', 'kasir@1sda.com', '2025-09-10 16:49:28', '2025-09-10 16:49:28', '1', '3'),
('4', 'admin_toko', '$2y$10$iBKUP80SjeZCP.YBfQFOge.7.y8wUTCOV48sUBt6KCIA9tJTdtucu', 'admin@toko.com', '2025-09-20 15:24:53', '2025-09-20 15:24:53', '1', '2'),
('11', 'admin_kantin', '$2y$10$WHZATkzyS6b0wMxEGjD14O8cRAIWEHHtPC396u6I1col9OP2fKyt2', 'kantin@koperasi.sch.id', '2026-09-02 01:30:02', '2026-09-02 01:30:02', '1', '2'),
('12', 'admin_konveksi', '$2y$10$WHZATkzyS6b0wMxEGjD14O8cRAIWEHHtPC396u6I1col9OP2fKyt2', 'konveksi@koperasi.sch.id', '2026-09-02 01:30:02', '2026-09-02 01:30:02', '2', '2'),
('13', 'admin_buku', '$2y$10$WHZATkzyS6b0wMxEGjD14O8cRAIWEHHtPC396u6I1col9OP2fKyt2', 'buku@koperasi.sch.id', '2026-09-02 01:30:02', '2026-09-02 01:30:02', '3', '2'),
('21', 'kasir_kantin', '$2y$10$WHZATkzyS6b0wMxEGjD14O8cRAIWEHHtPC396u6I1col9OP2fKyt2', 'kasir.kantin@koperasi.sch.id', '2026-09-02 01:30:02', '2026-09-02 01:30:02', '1', '3'),
('22', 'kasir_konveksi', '$2y$10$WHZATkzyS6b0wMxEGjD14O8cRAIWEHHtPC396u6I1col9OP2fKyt2', 'kasir.konveksi@koperasi.sch.id', '2026-09-02 01:30:02', '2026-09-02 01:30:02', '2', '3'),
('23', 'kasir_buku', '$2y$10$WHZATkzyS6b0wMxEGjD14O8cRAIWEHHtPC396u6I1col9OP2fKyt2', 'kasir.buku@koperasi.sch.id', '2026-09-02 01:30:02', '2026-09-02 01:30:02', '3', '3');

-- --------------------------------------------------------
-- Foreign Key Constraints
-- --------------------------------------------------------
ALTER TABLE `expenses` ADD CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE SET NULL;
ALTER TABLE `expenses` ADD CONSTRAINT `expenses_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
ALTER TABLE `product_units` ADD CONSTRAINT `product_units_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;
ALTER TABLE `product_units` ADD CONSTRAINT `product_units_ibfk_2` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE CASCADE;
ALTER TABLE `products` ADD CONSTRAINT `fk_products_stores` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE;
ALTER TABLE `stock_transfers` ADD CONSTRAINT `stock_transfers_ibfk_1` FOREIGN KEY (`product_unit_id`) REFERENCES `product_units` (`id`) ON DELETE CASCADE;
ALTER TABLE `stock_transfers` ADD CONSTRAINT `stock_transfers_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
ALTER TABLE `transaction_items` ADD CONSTRAINT `transaction_items_ibfk_1` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE;
ALTER TABLE `transactions` ADD CONSTRAINT `fk_transactions_stores` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE;
ALTER TABLE `transactions` ADD CONSTRAINT `fk_transactions_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
ALTER TABLE `users` ADD CONSTRAINT `fk_users_roles` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE RESTRICT;
ALTER TABLE `users` ADD CONSTRAINT `fk_users_stores` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE SET NULL;

COMMIT;
SET FOREIGN_KEY_CHECKS=1;