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');

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