CREATE TABLE peran ( id INTEGER PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO peran (id, nama, created_at, updated_at) VALUES (1, 'admin', NOW(), NOW()), (2, 'pegawai_toko', NOW(), NOW()); CREATE TABLE pengguna ( id INT PRIMARY KEY AUTO_INCREMENT, id_peran INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_pengguna_peran FOREIGN KEY (id_peran) REFERENCES peran (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `pengguna` (`id`, `id_peran`, `created_at`, `updated_at`) VALUES (1, 1, '2026-03-04 11:22:32', '2026-03-04 11:22:32'), (21, 2, '2026-03-15 12:37:10', '2026-03-15 12:37:10'), (22, 2, '2026-03-15 12:37:12', '2026-03-15 12:37:12'), (23, 2, '2026-03-15 12:37:21', '2026-03-15 12:37:21'), (24, 2, '2026-03-15 12:37:24', '2026-03-15 12:37:24'), (25, 2, '2026-03-16 09:32:29', '2026-03-16 09:32:29'), (26, 2, '2026-03-20 12:53:38', '2026-03-20 12:53:38'), (27, 2, '2026-04-15 17:32:02', '2026-04-15 17:32:02'), (28, 2, '2026-04-20 16:42:21', '2026-04-20 16:42:21'), (29, 2, '2026-04-20 16:42:34', '2026-04-20 16:42:34'); CREATE TABLE admin ( id INT PRIMARY KEY AUTO_INCREMENT, id_pengguna INT NOT NULL UNIQUE, username VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_admin_pengguna FOREIGN KEY (id_pengguna) REFERENCES pengguna (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `admin` (`id`, `id_pengguna`, `username`, `password`, `created_at`, `updated_at`) VALUES (1, 1, 'admin', '$2y$12$i/6xjQnTYoFbc3d5Lg/XzuhNP2lS.Fv6YBY3mwB2B4n9sSZAkQcJO', '2026-03-04 11:24:37', '2026-03-05 11:53:15'); -- TO CREATE ADMIN ACCOUNT, READ README.MD CREATE TABLE toko ( id INT PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, alamat VARCHAR(255) NULL, no_telepon VARCHAR(20) NULL, status_aktif BOOLEAN NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL ); INSERT INTO `toko` (`id`, `nama`, `alamat`, `no_telepon`, `status_aktif`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', 1, '2026-03-12 07:04:04', '2026-03-20 13:01:31', NULL), (2, 'Toko Kedua', 'Jln Thambrin No. 2, Medan', '081234567890', 1, '2026-03-12 07:04:08', '2026-03-17 10:50:57', NULL), (3, 'Toko Ketiga', 'Jln Thambrin No. 3, Medan', '081234567890', 1, '2026-03-12 07:04:14', '2026-03-16 12:12:57', '2026-03-12 07:04:22'), (4, 'Toko Ketiga', 'Jln Thambrin No. 3, Medan', '081234567890', 1, '2026-03-12 07:04:26', '2026-03-20 09:58:43', NULL), (5, 'Toko Keempat', 'Jln Thambrin No. 4, Medan', '081234567890', 1, '2026-04-20 16:00:59', '2026-04-20 16:00:59', NULL), (6, 'Toko Kelima', 'Jln Thambrin No. 5, Medan', '081234567890', 1, '2026-04-20 16:08:28', '2026-04-20 16:08:28', NULL); CREATE TABLE akun_toko ( id INT PRIMARY KEY AUTO_INCREMENT, id_pengguna INT NOT NULL, id_toko INT NOT NULL, username VARCHAR(255) NOT NULL, otu_otp VARCHAR(6) NOT NULL, status_terpakai BOOLEAN NOT NULL, expires_at TIMESTAMP NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_akun_toko_pengguna FOREIGN KEY (id_pengguna) REFERENCES pengguna (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_akun_toko_toko FOREIGN KEY (id_toko) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `akun_toko` (`id`, `id_pengguna`, `id_toko`, `username`, `otu_otp`, `status_terpakai`, `expires_at`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 21, 1, 'akun_toko_pertama_1', '980425', 1, '2026-03-15 05:37:10', '2026-03-15 05:37:10', '2026-03-16 03:12:55', NULL), (2, 22, 1, 'akun_toko_pertama_2', '460074', 0, '2026-03-15 05:37:12', '2026-03-15 05:37:12', '2026-03-16 03:12:58', NULL), (3, 23, 2, 'akun_toko_kedua_1', '262960', 1, '2026-04-15 05:37:21', '2026-03-15 05:37:21', '2026-03-16 03:36:01', '2026-03-16 03:36:01'), (4, 24, 2, 'akun_toko_kedua_2', '843378', 1, '2026-04-15 05:37:24', '2026-03-15 05:37:24', '2026-04-15 17:21:05', NULL), (5, 27, 1, 'akun_toko_pertama_1', '318041', 1, '2026-05-16 17:32:02', '2026-04-15 17:32:02', '2026-04-15 17:32:27', NULL), (6, 28, 4, 'akun_toko_ketiga_1', '957793', 1, '2026-05-21 16:42:21', '2026-04-20 16:42:21', '2026-04-20 16:43:10', NULL), (7, 29, 5, 'akun_toko_keempat_1', '987066', 1, '2026-05-21 16:42:34', '2026-04-20 16:42:34', '2026-04-20 16:43:50', NULL); CREATE TABLE `personal_access_tokens` ( `id` bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `tokenable_type` varchar(255) NOT NULL, `tokenable_id` bigint(20) UNSIGNED NOT NULL, `id_akun_toko` int(11) DEFAULT NULL, `name` text NOT NULL, `token` varchar(64) NOT NULL, `abilities` text DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `last_used_at` timestamp NULL DEFAULT NULL, `expires_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ); ALTER TABLE `personal_access_tokens` ADD UNIQUE KEY `personal_access_tokens_token_unique` (`token`), ADD KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`), ADD KEY `personal_access_tokens_id_akun_toko_foreign` (`id_akun_toko`), ADD KEY `personal_access_tokens_expires_at_index` (`expires_at`); INSERT INTO `personal_access_tokens` (`id`, `tokenable_type`, `tokenable_id`, `id_akun_toko`, `name`, `token`, `abilities`, `ip_address`, `last_used_at`, `expires_at`, `created_at`, `updated_at`) VALUES (1, 'App\\Models\\TokoKacamata\\Pengguna', 1, NULL, 'Apidog/1.0.0 (https://apidog.com)', '437a4c2c29239a2dc7d9cb18ddf13d70a4ac6563f1a2c93ca5e71bd3c1af9781', '[\"*\"]', '127.0.0.1', '2026-04-20 16:52:36', '2026-05-16 17:31:29', '2026-04-15 17:31:29', '2026-04-20 16:52:36'), (2, 'App\\Models\\TokoKacamata\\Pengguna', 27, 5, 'Apidog/1.0.0 (https://apidog.com)', 'd4746ee68a943dfa1c157ba4d33d5af9880d0e612bf44a7f6a1077750bd7828f', '[\"*\"]', '127.0.0.1', '2026-04-20 16:40:20', '2026-05-16 17:32:02', '2026-04-15 17:32:27', '2026-04-20 16:40:20'), (3, 'App\\Models\\TokoKacamata\\Pengguna', 28, 6, 'Apidog/1.0.0 (https://apidog.com)', '457e981448d387af6e3025043980875c2f53a7989416904ca862d59756314afb', '[\"*\"]', '127.0.0.1', '2026-04-20 16:44:41', '2026-05-21 16:42:21', '2026-04-20 16:43:10', '2026-04-20 16:44:41'), (4, 'App\\Models\\TokoKacamata\\Pengguna', 29, 7, 'Apidog/1.0.0 (https://apidog.com)', '1b21489d0039726ff8a5174aa5b000954bcf501138e41951878777a9736a73bc', '[\"*\"]', '127.0.0.1', '2026-04-20 16:44:46', '2026-05-21 16:42:34', '2026-04-20 16:43:50', '2026-04-20 16:44:46'); CREATE TABLE konfigurasi_stok_menipis ( id INT PRIMARY KEY AUTO_INCREMENT, gagang INT NOT NULL, lensa INT NOT NULL, softlense_biasa INT NOT NULL, produk_lainnya INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL ); INSERT INTO konfigurasi_stok_menipis (gagang, lensa, softlense_biasa, produk_lainnya, created_at, updated_at, deleted_at) VALUES (0, 0, 0, 0, NOW(), NOW(), NULL); CREATE TABLE merek_gagang ( id INT PRIMARY KEY AUTO_INCREMENT, id_toko INT NOT NULL, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_merek_gagang_toko FOREIGN KEY (id_toko) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `merek_gagang` (`id`, `id_toko`, `nama`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 1, 'Merek A1', '2026-03-19 21:28:06', '2026-03-20 21:23:08', '2026-03-20 21:23:08'), (2, 1, 'Merek B1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (3, 1, 'Merek C1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (4, 1, 'Merek D1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (5, 1, 'Merek E1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (6, 1, 'Merek F1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (7, 1, 'Merek G1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (8, 1, 'Merek H1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (9, 1, 'Merek I1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (10, 1, 'Merek J1', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (11, 1, 'Merek A2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (12, 1, 'Merek B2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (13, 1, 'Merek C2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (14, 1, 'Merek D2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (15, 1, 'Merek E2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (16, 1, 'Merek F2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (17, 1, 'Merek G2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (18, 1, 'Merek H2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (19, 1, 'Merek I2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (20, 1, 'Merek J2', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (21, 1, 'Merek A3', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (22, 1, 'Merek B3', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (23, 1, 'Merek C3', '2026-03-19 21:28:06', '2026-03-19 21:28:06', NULL), (24, 2, 'Merek A1', '2026-03-20 16:57:48', '2026-03-20 16:57:48', NULL), (25, 2, 'Merek B1', '2026-03-20 16:57:48', '2026-03-20 16:57:48', NULL), (26, 2, 'Merek C1', '2026-03-20 16:57:48', '2026-03-20 16:57:48', NULL); CREATE TABLE gagang ( id INT PRIMARY KEY AUTO_INCREMENT, id_merek_gagang INT NOT NULL, serial VARCHAR(255) NOT NULL, quantity INT NOT NULL, kode_harga VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, constraint fk_gagang_merek_gagang FOREIGN KEY (id_merek_gagang) REFERENCES merek_gagang (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `gagang` (`id`, `id_merek_gagang`, `serial`, `quantity`, `kode_harga`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 1, 'A1-001', 10, 'ABC', '2026-03-19 21:30:11', '2026-03-19 21:30:11', NULL), (2, 1, 'A1-002', 15, 'ABC', '2026-03-19 21:30:11', '2026-03-19 21:30:11', NULL), (3, 1, 'A1-003', 20, 'ABC', '2026-03-19 21:30:11', '2026-03-19 21:30:11', NULL), (4, 2, 'B1-001', 10, 'ABC', '2026-03-19 22:04:45', '2026-03-19 22:04:45', NULL), (5, 2, 'B1-002', 15, 'ABC', '2026-03-19 22:04:45', '2026-03-19 22:04:45', NULL), (6, 2, 'B1-003', 0, 'ABC', '2026-03-19 22:04:45', '2026-03-20 20:38:20', NULL), (7, 1, 'A1-004', 25, 'ABC', '2026-03-20 20:29:30', '2026-03-20 20:29:30', NULL); CREATE TABLE tipe_jenis_lensa ( id INT PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO tipe_jenis_lensa (id, nama, created_at, updated_at) VALUES (1, "SPH", NOW(), NOW()), (2, "SPH and ADD", NOW(), NOW()); CREATE TABLE jenis_lensa ( id INT PRIMARY KEY AUTO_INCREMENT, id_toko INT NOT NULL, id_tipe_jenis_lensa INT NOT NULL, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_jenis_lensa_toko FOREIGN KEY (id_toko) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_jenis_lensa_tipe_jenis_lensa FOREIGN KEY (id_tipe_jenis_lensa) REFERENCES tipe_jenis_lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO jenis_lensa (id, id_toko, id_tipe_jenis_lensa, nama, created_at, updated_at, deleted_at) VALUES (1, 1, 1, 'Jenis Lensa 1 - SPH', NOW(), NOW(), NULL), (2, 1, 1, 'Jenis Lensa 2 - SPH', NOW(), NOW(), NULL), (3, 1, 2, 'Jenis Lensa 3 - SPH and ADD', NOW(), NOW(), NULL), (4, 2, 1, 'Jenis Lensa 1 - SPH', NOW(), NOW(), NULL), (5, 2, 2, 'Jenis Lensa 2 - SPH and ADD', NOW(), NOW(), NULL), (6, 2, 2, 'Jenis Lensa 3 - SPH and ADD', NOW(), NOW(), NOW()); CREATE TABLE lensa ( id INT PRIMARY KEY AUTO_INCREMENT, id_jenis_lensa INT NOT NULL, sph VARCHAR(255) NULL, `add` VARCHAR(255) NULL, quantity INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_lensa_jenis_lensa FOREIGN KEY (id_jenis_lensa) REFERENCES jenis_lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO lensa (id, id_jenis_lensa, sph, `add`, quantity, created_at, updated_at, deleted_at) VALUES (1, 1, '-100', NULL, 10, NOW(), NOW(), NULL), (2, 1, '-200', NULL, 15, NOW(), NOW(), NULL), (3, 1, '-300', NULL, 20, NOW(), NOW(), NULL), (4, 2, '-100', NULL, 10, NOW(), NOW(), NULL), (5, 2, '-200', NULL, 15, NOW(), NOW(), NULL), (6, 2, '-300', NULL, 20, NOW(), NOW(), NULL), (7, 3, '-100', NULL, 10, NOW(), NOW(), NULL), (8, 3, '-200', NULL, 15, NOW(), NOW(), NULL), (9, 3, NULL, '+100', 20, NOW(), NOW(), NULL), (10, 3, NULL, '+200', 25, NOW(), NOW(), NULL), (11, 3, '-100', '+100', 30, NOW(), NOW(), NULL), (12, 3, '-200', '+100', 35, NOW(), NOW(), NULL), (13, 3, '-200', '+200', 40, NOW(), NOW(), NULL), (14, 4, '-100', NULL, 10, NOW(), NOW(), NULL), (15, 5, '-100', NULL, 15, NOW(), NOW(), NULL), (16, 6, '-100', '+100', 20, NOW(), NOW(), NULL); CREATE TABLE merging_stok_lensa_toko ( id INT PRIMARY KEY AUTO_INCREMENT, id_toko_asal INT NOT NULL, id_toko_tujuan INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_merging_stok_lensa_toko_toko_asal FOREIGN KEY (id_toko_asal) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_merging_stok_lensa_toko_toko_tujuan FOREIGN KEY (id_toko_tujuan) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `merging_stok_lensa_toko` (`id`, `id_toko_asal`, `id_toko_tujuan`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 1, 4, '2026-04-20 16:03:40', '2026-04-20 16:14:22', NULL), (2, 2, 5, '2026-04-20 16:11:27', '2026-04-20 16:11:27', NULL), (3, 1, 6, '2026-04-20 16:39:22', '2026-04-20 16:39:22', NULL); CREATE TABLE merek_softlense_biasa ( id INT PRIMARY KEY AUTO_INCREMENT, id_toko INT NOT NULL, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_merek_softlense_biasa_toko FOREIGN KEY (id_toko) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO merek_softlense_biasa (id, id_toko, nama, created_at, updated_at, deleted_at) VALUES (1, 1, 'Merek Toko 1 - Softlensa 1', NOW(), NOW(), NULL), (2, 1, 'Merek Toko 1 - Softlensa 2', NOW(), NOW(), NULL), (3, 1, 'Merek Toko 1 - Softlensa 3', NOW(), NOW(), NULL), (4, 2, 'Merek Toko 2 - Softlensa 1', NOW(), NOW(), NULL), (5, 2, 'Merek Toko 2 - Softlensa 2', NOW(), NOW(), NULL), (6, 2, 'Merek Toko 2 - Softlensa 3', NOW(), NOW(), NULL), (7, 2, 'Merek Toko 2 - Softlensa 4', NOW(), NOW(), NULL); CREATE TABLE softlense_biasa ( id INT PRIMARY KEY AUTO_INCREMENT, id_merek_softlense_biasa INT NOT NULL, warna VARCHAR(255) NOT NULL, diameter VARCHAR(255) NOT NULL, harga INT NOT NULL, quantity INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_softlense_biasa_merek_softlense_biasa FOREIGN KEY (id_merek_softlense_biasa) REFERENCES merek_softlense_biasa (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `softlense_biasa` (`id`, `id_merek_softlense_biasa`, `warna`, `diameter`, `harga`, `quantity`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 1, 'Merah', '10mm', 10000, 9, '2026-04-13 16:47:36', '2026-04-16 12:55:37', NULL), (2, 1, 'Merah', '20mm', 20000, 13, '2026-04-13 16:47:36', '2026-04-16 12:55:37', NULL), (3, 1, 'Merah', '30mm', 30000, 20, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (4, 1, 'Biru', '10mm', 10000, 10, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (5, 1, 'Biru', '20mm', 20000, 15, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (6, 1, 'Biru', '30mm', 30000, 20, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (7, 1, 'Kuning', '10mm', 10000, 25, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (8, 2, 'Merah', '10mm', 10000, 10, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (9, 2, 'Biru', '10mm', 10000, 10, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (10, 3, 'Kuning', '10mm', 10000, 25, '2026-04-13 16:47:36', '2026-04-13 16:47:36', NULL), (11, 7, 'Emas', '10mm', 777777, 3, '2026-04-16 12:54:29', '2026-04-16 12:58:13', NULL); CREATE TABLE produk_lainnya ( id INT PRIMARY KEY AUTO_INCREMENT, id_toko INT NOT NULL, nama VARCHAR(255) NOT NULL, harga INT NOT NULL, quantity INT NOT NULL, is_hadiah BOOLEAN NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_produk_lainnya_toko FOREIGN KEY (id_toko) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO produk_lainnya (id, id_toko, nama, harga, quantity, is_hadiah, created_at, updated_at, deleted_at) VALUES (1, 1, 'Toko 1 - Produk Lainnya 1', 10000, 10, FALSE, NOW(), NOW(), NULL), (2, 1, 'Toko 1 - Produk Lainnya 2', 20000, 15, TRUE, NOW(), NOW(), NULL), (3, 1, 'Toko 1 - Produk Lainnya 3', 30000, 20, TRUE, NOW(), NOW(), NULL), (4, 2, 'Toko 2 - Produk Lainnya 1', 10000, 10, FALSE, NOW(), NOW(), NULL), (5, 2, 'Toko 2 - Produk Lainnya 2', 20000, 15, FALSE, NOW(), NOW(), NULL), (6, 2, 'Toko 2 - Produk Lainnya 3', 30000, 20, TRUE, NOW(), NOW(), NULL), (7, 2, 'Toko 2 - Produk Lainnya 4', 40000, 5, TRUE, NOW(), NOW(), NULL); CREATE TABLE jenis_transaksi ( id INT PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO jenis_transaksi (id, nama, created_at, updated_at) VALUES (1, 'Gagang - Lensa', NOW(), NOW()), (2, 'Softlense Minus', NOW(), NOW()), (3, 'Softlense Biasa', NOW(), NOW()), (4, 'Produk Lainnya', NOW(), NOW()); CREATE TABLE transaksi_header ( id INT PRIMARY KEY AUTO_INCREMENT, id_jenis_transaksi INT NOT NULL, id_toko INT NOT NULL, nama_toko VARCHAR(255) NOT NULL, alamat_toko VARCHAR(255) NOT NULL, no_telepon_toko VARCHAR(255) NOT NULL, no_bon VARCHAR(255) NULL, tanggal_pemesanan DATE NOT NULL, harga_total INT NOT NULL, status_pembayaran BOOLEAN NOT NULL, notes VARCHAR(1000) NULL, alasan_penghapusan VARCHAR(255) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_transaksi_header_jenis_transaksi FOREIGN KEY (id_jenis_transaksi) REFERENCES jenis_transaksi (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_header_toko FOREIGN KEY (id_toko) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO transaksi_header (id, id_jenis_transaksi, id_toko, nama_toko, alamat_toko, no_telepon_toko, no_bon, tanggal_pemesanan, harga_total, status_pembayaran, notes, alasan_penghapusan, created_at, updated_at, deleted_at, updated_by) VALUES (1, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000001', '2026-04-15', 100000, 1, 'Testing 1', NULL, '2026-04-13 11:25:10', '2026-04-13 11:25:10', NULL, 1), (2, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000002', '2026-04-15', 100000, 1, 'Testing 2', NULL, '2026-04-13 11:34:03', '2026-04-13 11:34:03', NULL, 1), (3, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000003', '2026-04-15', 100000, 0, 'Testing 3', NULL, '2026-04-13 11:39:41', '2026-04-13 11:39:41', NULL, 1), (4, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000004', '2026-04-15', 100000, 0, 'Testing 4', NULL, '2026-04-13 11:45:50', '2026-04-13 11:45:50', NULL, 1), (5, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000005', '2026-04-15', 100000, 1, 'Testing 1', NULL, '2026-04-13 11:50:08', '2026-04-13 11:50:08', NULL, 1), (6, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000006', '2026-04-15', 100000, 1, 'Testing 5', NULL, '2026-04-13 11:51:38', '2026-04-13 11:51:38', NULL, 1), (7, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000007', '2026-04-15', 100000, 1, 'Testing 5', NULL, '2026-04-13 11:56:04', '2026-04-13 11:56:04', NULL, 1), (12, 2, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000008', '2026-04-15', 100000, 1, 'Testing 1', NULL, '2026-04-15 10:47:08', '2026-04-15 10:47:08', NULL, 27), (14, 3, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', NULL, '2026-04-15', 100000, 1, 'Testing 1', NULL, '2026-04-16 05:55:37', '2026-04-16 05:57:46', NULL, 27), (15, 3, 2, 'Toko Kedua', 'Jln Thambrin No. 2, Medan', '081234567890', NULL, '2026-04-20', 500000, 1, 'Testing 2', NULL, '2026-04-16 05:57:14', '2026-04-16 05:57:50', NULL, 1), (16, 3, 2, 'Toko Kedua', 'Jln Thambrin No. 2, Medan', '081234567890', NULL, '2026-04-20', 200000, 1, 'Testing 3', NULL, '2026-04-16 05:58:13', '2026-04-16 05:58:13', NULL, 1), (17, 4, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', NULL, '2026-04-15', 77777, 1, 'Testing 1', NULL, '2026-04-16 07:26:43', '2026-04-16 07:26:43', NULL, 27), (19, 1, 1, 'Toko Pertama', 'Jln Thambrin No. 1, Medan', '081234567890', '000009', '2026-04-15', 100000, 0, 'Testing 6', NULL, '2026-04-18 18:44:32', '2026-04-18 18:44:32', NULL, 1); CREATE TABLE jenis_transaksi_lensa ( id INT PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO jenis_transaksi_lensa (id, nama, created_at, updated_at) VALUES (1, 'Lensa terdaftar di Inventori', NOW(), NOW()), (2, 'Preorder Lensa yang terdaftar di Inventori', NOW(), NOW()), (3, 'Preorder Lensa yang tidak terdaftar di Inventori', NOW(), NOW()), (4, 'Lensa bawa sendiri', NOW(), NOW()); CREATE TABLE transaksi_gagang_lensa ( id INT PRIMARY KEY AUTO_INCREMENT, id_transaksi_header INT NOT NULL, nama_customer VARCHAR(255) NOT NULL, alamat_customer VARCHAR(255) NULL, no_telepon_customer VARCHAR(20) NULL, tanggal_selesai_pesanan DATE NULL, tanggal_pengambilan_pesanan DATE NULL, is_gagang_sendiri BOOLEAN NOT NULL, id_gagang INT NULL, serial_gagang VARCHAR(255) NULL, kode_harga_gagang VARCHAR(255) NULL, nama_merek_gagang VARCHAR(255) NULL, jarak_mata VARCHAR(255) NULL, id_jenis_transaksi_lensa_kiri INT NOT NULL, id_lensa_kiri INT NULL, sph_lensa_kiri VARCHAR(255) NULL, add_lensa_kiri VARCHAR(255) NULL, nama_jenis_lensa_kiri VARCHAR(255) NULL, sph_lensa_kiri_po VARCHAR(255) NULL, cyl_lensa_kiri_po VARCHAR(255) NULL, axis_lensa_kiri_po VARCHAR(255) NULL, add_lensa_kiri_po VARCHAR(255) NULL, id_jenis_transaksi_lensa_kanan INT NOT NULL, id_lensa_kanan INT NULL, sph_lensa_kanan VARCHAR(255) NULL, add_lensa_kanan VARCHAR(255) NULL, nama_jenis_lensa_kanan VARCHAR(255) NULL, sph_lensa_kanan_po VARCHAR(255) NULL, cyl_lensa_kanan_po VARCHAR(255) NULL, axis_lensa_kanan_po VARCHAR(255) NULL, add_lensa_kanan_po VARCHAR(255) NULL, nama_jenis_lensa_po VARCHAR(255) NULL, waktu_tiba_lensa_po DATETIME NULL, warna_lensa VARCHAR(255) NULL, uang_muka INT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_transaksi_gagang_lensa_transaksi_header FOREIGN KEY (id_transaksi_header) REFERENCES transaksi_header (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_gagang_lensa_gagang FOREIGN KEY (id_gagang) REFERENCES gagang (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_gagang_lensa_jenis_transaksi_lensa_kiri FOREIGN KEY (id_jenis_transaksi_lensa_kiri) REFERENCES jenis_transaksi_lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_gagang_lensa_lensa_kiri FOREIGN KEY (id_lensa_kiri) REFERENCES lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_gagang_lensa_jenis_transaksi_lensa_kanan FOREIGN KEY (id_jenis_transaksi_lensa_kanan) REFERENCES jenis_transaksi_lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_gagang_lensa_lensa_kanan FOREIGN KEY (id_lensa_kanan) REFERENCES lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO transaksi_gagang_lensa (id, id_transaksi_header, nama_customer, alamat_customer, no_telepon_customer, tanggal_selesai_pesanan, tanggal_pengambilan_pesanan, is_gagang_sendiri, id_gagang, serial_gagang, kode_harga_gagang, nama_merek_gagang, jarak_mata, id_jenis_transaksi_lensa_kiri, id_lensa_kiri, sph_lensa_kiri, add_lensa_kiri, nama_jenis_lensa_kiri, sph_lensa_kiri_po, cyl_lensa_kiri_po, axis_lensa_kiri_po, add_lensa_kiri_po, id_jenis_transaksi_lensa_kanan, id_lensa_kanan, sph_lensa_kanan, add_lensa_kanan, nama_jenis_lensa_kanan, sph_lensa_kanan_po, cyl_lensa_kanan_po, axis_lensa_kanan_po, add_lensa_kanan_po, nama_jenis_lensa_po, waktu_tiba_lensa_po, warna_lensa, uang_muka, created_at, updated_at, deleted_at, updated_by) VALUES (1, 1, 'Andi', 'Jln Testing 1', '081234567890', '2026-04-15', '2026-04-14', 0, 5, 'B1-002', 'ABC', 'Merek B1', '5cm', 1, 11, '-100', '+100', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, 1, 13, '-200', '+200', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, NULL, NULL, 'Merah', 100000, '2026-04-13 11:25:10', '2026-04-13 11:25:10', NULL, 1), (2, 2, 'Budi', 'Jln Testing 2', '081234567890', '2026-04-15', '2026-04-14', 1, NULL, NULL, NULL, NULL, NULL, 1, 11, '-100', '+100', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, 1, 13, '-200', '+200', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, NULL, NULL, 'Biru', 100000, '2026-04-13 11:34:03', '2026-04-13 11:34:03', NULL, 1), (3, 3, 'Cici', 'Jln Testing 3', '081234567890', '2026-04-18', NULL, 1, NULL, NULL, NULL, NULL, NULL, 2, 11, '-100', '+100', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, 2, 13, '-200', '+200', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, NULL, NULL, 'Kuning', 30000, '2026-04-13 11:39:41', '2026-04-13 11:39:41', NULL, 1), (4, 4, 'Doni', 'Jln Testing 4', '081234567890', '2026-04-18', '2026-04-20', 1, NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, '+100', '60', '90', '-100', 3, NULL, NULL, NULL, NULL, '+200', '120', '150', '-200', 'Bifokal', NULL, 'Hijau', 50000, '2026-04-13 11:45:50', '2026-04-19 16:33:59', NULL, 1), (5, 5, 'Andi', 'Jln Testing 1', '081234567890', '2026-04-15', '2026-04-14', 0, 5, 'B1-002', 'ABC', 'Merek B1', '5cm', 1, 11, '-100', '+100', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, 1, 13, '-200', '+200', 'Jenis Lensa 3 - SPH and ADD', NULL, NULL, NULL, NULL, NULL, NULL, 'Merah', 100000, '2026-04-13 11:50:08', '2026-04-13 11:50:08', NULL, 1), (6, 6, 'Eko', 'Jln Testing 5', '081234567890', '2026-04-15', '2026-04-14', 0, 5, 'B1-002', 'ABC', 'Merek B1', '1cm', 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 100000, '2026-04-13 11:51:38', '2026-04-13 11:51:38', NULL, 1), (7, 7, 'Eko', 'Jln Testing 5', '081234567890', '2026-04-17', '2026-04-20', 0, 5, 'B1-002', 'ABC', 'Merek B1', '1cm', 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 100000, '2026-04-13 11:56:04', '2026-04-19 16:35:47', NULL, 1), (8, 19, 'Foni', 'Jln Testing 6', '081234567890', '2026-04-17', NULL, 0, 4, 'B1-001', 'ABC', 'Merek B1', '2cm', 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 50000, '2026-04-18 18:44:32', '2026-04-18 18:44:32', NULL, 1); CREATE TABLE preorder_softlense_minus ( id INT PRIMARY KEY AUTO_INCREMENT, id_transaksi_header INT NOT NULL, nama_customer VARCHAR(255) NOT NULL, alamat_customer VARCHAR(255) NULL, no_telepon_customer VARCHAR(20) NULL, tanggal_selesai_pesanan DATE NULL, tanggal_pengambilan_pesanan DATE NULL, waktu_tiba_po DATETIME NULL, merek VARCHAR(255) NOT NULL, jenis_lensa VARCHAR(255) NOT NULL, warna VARCHAR(255) NOT NULL, sph VARCHAR(255) NULL, cyl VARCHAR(255) NULL, axis VARCHAR(255) NULL, `add` VARCHAR(255) NULL, uang_muka INT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_transaksi_preorder_softlense_minus_transaksi_header FOREIGN KEY (id_transaksi_header) REFERENCES transaksi_header (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `preorder_softlense_minus` (`id`, `id_transaksi_header`, `nama_customer`, `alamat_customer`, `no_telepon_customer`, `tanggal_selesai_pesanan`, `tanggal_pengambilan_pesanan`, `waktu_tiba_po`, `merek`, `jenis_lensa`, `warna`, `sph`, `cyl`, `axis`, `add`, `uang_muka`, `created_at`, `updated_at`, `deleted_at`, `updated_by`) VALUES (2, 12, 'Andi', 'Jln Testing 1', '081234567890', '2026-04-17', NULL, NULL, 'Louis Vuiton', 'Sharingan', 'Merah', '+100', NULL, NULL, NULL, 100000, '2026-04-15 17:47:08', '2026-04-15 17:47:08', NULL, 1); CREATE TABLE transaksi_softlense_biasa ( id INT PRIMARY KEY AUTO_INCREMENT, id_transaksi_header INT NOT NULL, id_softlense_biasa INT NOT NULL, quantity INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_transaksi_softlense_biasa_transaksi_header FOREIGN KEY (id_transaksi_header) REFERENCES transaksi_header (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_softlense_biasa_softlense_biasa FOREIGN KEY (id_softlense_biasa) REFERENCES softlense_biasa (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `transaksi_softlense_biasa` (`id`, `id_transaksi_header`, `id_softlense_biasa`, `quantity`, `created_at`, `updated_at`, `deleted_at`, `updated_by`) VALUES (3, 14, 1, 1, '2026-04-16 12:55:37', '2026-04-16 12:55:37', NULL, 1), (4, 14, 2, 2, '2026-04-16 12:55:37', '2026-04-16 12:55:37', NULL, 1), (5, 15, 11, 5, '2026-04-16 12:57:14', '2026-04-16 12:57:14', NULL, 1), (6, 16, 11, 2, '2026-04-16 12:58:13', '2026-04-16 12:58:13', NULL, 1); CREATE TABLE transaksi_produk_lainnya ( id INT PRIMARY KEY AUTO_INCREMENT, id_transaksi_header INT NOT NULL, id_produk_lainnya INT NOT NULL, is_hadiah BOOLEAN NOT NULL, quantity INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_transaksi_produk_lainnya_transaksi_header FOREIGN KEY (id_transaksi_header) REFERENCES transaksi_header (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_transaksi_produk_lainnya_produk_lainnya FOREIGN KEY (id_produk_lainnya) REFERENCES produk_lainnya (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `transaksi_produk_lainnya` (`id`, `id_transaksi_header`, `id_produk_lainnya`, `is_hadiah`, `quantity`, `created_at`, `updated_at`, `deleted_at`, `updated_by`) VALUES (1, 1, 2, 1, 1, '2026-04-13 18:25:10', '2026-04-13 18:25:10', NULL, 1), (2, 1, 3, 1, 2, '2026-04-13 18:25:10', '2026-04-13 18:25:10', NULL, 1), (3, 2, 2, 1, 1, '2026-04-13 18:34:03', '2026-04-13 18:34:03', NULL, 1), (4, 3, 2, 1, 1, '2026-04-13 18:39:41', '2026-04-13 18:39:41', NULL, 1), (5, 4, 2, 1, 1, '2026-04-13 18:45:50', '2026-04-13 18:45:50', NULL, 1), (6, 5, 2, 1, 1, '2026-04-13 18:50:08', '2026-04-13 18:50:08', NULL, 1), (7, 5, 3, 1, 2, '2026-04-13 18:50:08', '2026-04-13 18:50:08', NULL, 1), (10, 12, 2, 1, 1, '2026-04-15 17:47:08', '2026-04-15 17:47:08', NULL, 1), (11, 12, 3, 1, 3, '2026-04-15 17:47:08', '2026-04-15 17:47:08', NULL, 1), (12, 17, 1, 0, 1, '2026-04-16 14:26:43', '2026-04-16 14:26:43', NULL, 1), (13, 17, 2, 0, 2, '2026-04-16 14:26:43', '2026-04-16 14:26:43', NULL, 1), (14, 17, 3, 0, 3, '2026-04-16 14:26:43', '2026-04-16 14:26:43', NULL, 1); CREATE TABLE jenis_pembayaran ( id INT PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO jenis_pembayaran (id, nama, created_at, updated_at) VALUES (1, 'DP', NOW(), NOW()), (2, 'Pelunasan', NOW(), NOW()), (3, 'Full Payment', NOW(), NOW()); CREATE TABLE metode_pembayaran ( id INT PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO metode_pembayaran (id, nama, created_at, updated_at) VALUES (1, 'Tunai', NOW(), NOW()), (2, 'Transfer Bank', NOW(), NOW()); CREATE TABLE pembayaran ( id INT PRIMARY KEY AUTO_INCREMENT, id_transaksi_header INT NOT NULL, id_jenis_pembayaran INT NOT NULL, id_metode_pembayaran INT NOT NULL, total_pembayaran INT NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_pembayaran_transaksi_header FOREIGN KEY (id_transaksi_header) REFERENCES transaksi_header (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pembayaran_jenis_pembayaran FOREIGN KEY (id_jenis_pembayaran) REFERENCES jenis_pembayaran (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pembayaran_metode_pembayaran FOREIGN KEY (id_metode_pembayaran) REFERENCES metode_pembayaran (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO `pembayaran` (`id`, `id_transaksi_header`, `id_jenis_pembayaran`, `id_metode_pembayaran`, `total_pembayaran`, `created_at`, `updated_at`, `deleted_at`, `updated_by`) VALUES (1, 1, 3, 2, 100000, '2026-04-13 18:25:10', '2026-04-13 18:25:10', NULL, 1), (2, 2, 3, 1, 100000, '2026-04-13 18:34:03', '2026-04-13 18:34:03', NULL, 1), (3, 3, 1, 2, 30000, '2026-04-13 18:39:41', '2026-04-13 18:39:41', NULL, 1), (4, 4, 1, 1, 50000, '2026-04-13 18:45:50', '2026-04-13 18:45:50', NULL, 1), (5, 5, 3, 2, 100000, '2026-04-13 18:50:08', '2026-04-13 18:50:08', NULL, 1), (6, 6, 3, 2, 100000, '2026-04-13 18:51:38', '2026-04-13 18:51:38', NULL, 1), (7, 7, 3, 2, 100000, '2026-04-13 18:56:04', '2026-04-13 18:56:04', NULL, 1), (9, 12, 3, 2, 100000, '2026-04-15 17:47:08', '2026-04-15 17:47:08', NULL, 1), (10, 14, 3, 1, 100000, '2026-04-16 12:55:37', '2026-04-16 12:55:37', NULL, 1), (11, 15, 3, 2, 500000, '2026-04-16 12:57:14', '2026-04-16 12:57:14', NULL, 1), (12, 16, 3, 2, 200000, '2026-04-16 12:58:13', '2026-04-16 12:58:13', NULL, 1), (13, 17, 3, 1, 77777, '2026-04-16 14:26:43', '2026-04-16 14:26:43', NULL, 1), (14, 19, 1, 1, 50000, '2026-04-19 01:44:32', '2026-04-19 01:44:32', NULL, 1); CREATE TABLE pengajuan_transfer_stok ( id INT PRIMARY KEY AUTO_INCREMENT, id_toko_asal INT NOT NULL, id_toko_tujuan INT NOT NULL, is_selesai BOOLEAN NOT NULL, tanggal_transfer_stok DATE NOT NULL, notes_asal VARCHAR(1000) NULL, notes_tujuan VARCHAR(1000) NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_pengajuan_transfer_stok_toko_asal FOREIGN KEY (id_toko_asal) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pengajuan_transfer_stok_toko_tujuan FOREIGN KEY (id_toko_tujuan) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); CREATE TABLE kategori ( id INT PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO kategori (id, nama, created_at, updated_at) VALUES (1, 'Gagang', NOW(), NOW()), (2, 'Lensa', NOW(), NOW()), (3, 'Softlense Biasa', NOW(), NOW()), (4, 'Produk Lainnya', NOW(), NOW()); CREATE TABLE pengajuan_transfer_stok_gagang_detail ( id INT PRIMARY KEY AUTO_INCREMENT, id_pengajuan_transfer_stok INT NOT NULL, id_kategori INT NOT NULL, id_gagang_asal INT NOT NULL, nama_merek_gagang VARCHAR(255) NULL, serial VARCHAR(255) NULL, quantity_pengajuan_transfer INT NOT NULL, quantity_ditransfer INT NOT NULL, id_gagang_tujuan INT NULL, is_not_registered_on_tujuan BOOLEAN NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_pengajuan_transfer_stok_gagang_detail_header FOREIGN KEY (id_pengajuan_transfer_stok) REFERENCES pengajuan_transfer_stok (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pengajuan_transfer_stok_gagang_detail_kategori FOREIGN KEY (id_kategori) REFERENCES kategori (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pengajuan_transfer_stok_gagang_detail_gagang_asal FOREIGN KEY (id_gagang_asal) REFERENCES gagang (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pengajuan_transfer_stok_gagang_detail_gagang_tujuan FOREIGN KEY (id_gagang_tujuan) REFERENCES gagang (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); CREATE TABLE pengajuan_transfer_stok_lensa_detail ( id INT PRIMARY KEY AUTO_INCREMENT, id_pengajuan_transfer_stok INT NOT NULL, id_kategori INT NOT NULL, id_lensa_asal INT NOT NULL, id_tipe_jenis_lensa INT NULL, nama_tipe_jenis_lensa VARCHAR(255) NULL, nama_jenis_lensa VARCHAR(255) NULL, sph VARCHAR(255) NULL, `add` VARCHAR(255) NULL, quantity_pengajuan_transfer INT NOT NULL, quantity_ditransfer INT NOT NULL, id_lensa_tujuan INT NULL, is_not_registered_on_tujuan BOOLEAN NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT fk_pengajuan_transfer_stok_lensa_header FOREIGN KEY (id_pengajuan_transfer_stok) REFERENCES pengajuan_transfer_stok (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pengajuan_transfer_stok_lensa_detail_kategori FOREIGN KEY (id_kategori) REFERENCES kategori (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pengajuan_transfer_stok_lensa_asal FOREIGN KEY (id_lensa_asal) REFERENCES lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_pengajuan_transfer_stok_lensa_tujuan FOREIGN KEY (id_lensa_tujuan) REFERENCES lensa (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); CREATE TABLE pengajuan_transfer_stok_softlense_biasa_detail ( id INT PRIMARY KEY AUTO_INCREMENT, id_pengajuan_transfer_stok INT NOT NULL, id_kategori INT NOT NULL, id_softlense_biasa_asal INT NOT NULL, nama_merek_softlense_biasa VARCHAR(255) NULL, warna VARCHAR(255) NULL, diameter VARCHAR(255) NULL, quantity_pengajuan_transfer INT NOT NULL, quantity_ditransfer INT NOT NULL, id_softlense_biasa_tujuan INT NULL, is_not_registered_on_tujuan BOOLEAN NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT pengajuan_transfer_stok_softlense_biasa_header FOREIGN KEY (id_pengajuan_transfer_stok) REFERENCES pengajuan_transfer_stok (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT pengajuan_transfer_stok_softlense_biasa_kategori FOREIGN KEY (id_kategori) REFERENCES kategori (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT pengajuan_transfer_stok_softlense_biasa_asal FOREIGN KEY (id_softlense_biasa_asal) REFERENCES softlense_biasa (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT pengajuan_transfer_stok_softlense_biasa_tujuan FOREIGN KEY (id_softlense_biasa_tujuan) REFERENCES softlense_biasa (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); CREATE TABLE pengajuan_transfer_stok_produk_lainnya_detail ( id INT PRIMARY KEY AUTO_INCREMENT, id_pengajuan_transfer_stok INT NOT NULL, id_kategori INT NOT NULL, id_produk_lainnya_asal INT NULL, nama_produk_lainnya VARCHAR(255) NULL, quantity_pengajuan_transfer INT NOT NULL, quantity_ditransfer INT NOT NULL, id_produk_lainnya_tujuan INT NULL, is_not_registered_on_tujuan BOOLEAN NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, updated_by INT NOT NULL, CONSTRAINT pengajuan_transfer_stok_produk_lainnya_header FOREIGN KEY (id_pengajuan_transfer_stok) REFERENCES pengajuan_transfer_stok (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT pengajuan_transfer_stok_produk_lainnya_kategori FOREIGN KEY (id_kategori) REFERENCES kategori (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT pengajuan_transfer_stok_produk_lainnya_asal FOREIGN KEY (id_produk_lainnya_asal) REFERENCES produk_lainnya (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT pengajuan_transfer_stok_produk_lainnya_tujuan FOREIGN KEY (id_produk_lainnya_tujuan) REFERENCES produk_lainnya (id) ON DELETE RESTRICT ON UPDATE RESTRICT ); CREATE TABLE biaya_lain_lain ( id INT PRIMARY KEY AUTO_INCREMENT, id_toko INT NOT NULL, id_metode_pembayaran INT NOT NULL, keterangan VARCHAR(255) NOT NULL, nominal INT NOT NULL, tanggal DATE NOT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_biaya_lain_lain_toko FOREIGN KEY (id_toko) REFERENCES toko (id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_biaya_lain_lain_metode_pembayaran FOREIGN KEY (id_metode_pembayaran) REFERENCES metode_pembayaran (id) ON DELETE RESTRICT ON UPDATE RESTRICT );