Untitled

CREATE DATABASE sistem_lelang;
USE sistem_lelang;

CREATE TABLE tb_level (
    id_level INT(11) NOT NULL AUTO_INCREMENT,
    level ENUM('administrator', 'petugas') NOT NULL,
    PRIMARY KEY (id_level)
);

CREATE TABLE tb_petugas (
    id_petugas INT(11) NOT NULL AUTO_INCREMENT,
    nama_petugas VARCHAR(25) NOT NULL,
    username VARCHAR(25) NOT NULL UNIQUE,
    password VARCHAR(25) NOT NULL,
    id_level INT(11) NOT NULL,
    PRIMARY KEY (id_petugas),
    FOREIGN KEY (id_level) REFERENCES tb_level(id_level) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

CREATE TABLE tb_masyarakat (
    id_user INT(11) NOT NULL AUTO_INCREMENT,
    nama_lengkap VARCHAR(25) NOT NULL,
    username VARCHAR(25) NOT NULL UNIQUE,
    password VARCHAR(25) NOT NULL,
    telp VARCHAR(25) NOT NULL,
    PRIMARY KEY (id_user)
);

CREATE TABLE tb_barang (
    id_barang INT(11) NOT NULL AUTO_INCREMENT,
    nama_barang VARCHAR(25) NOT NULL,
    tgl DATE NOT NULL,
    harga_awal INT(20) NOT NULL,
    deskripsi_barang VARCHAR(100) NOT NULL,
    PRIMARY KEY (id_barang)
);

CREATE TABLE tb_lelang (
    id_lelang INT(11) NOT NULL AUTO_INCREMENT,
    id_barang INT(11) NOT NULL,
    tgl_lelang DATE NOT NULL,
    harga_akhir INT(20) DEFAULT 0,
    id_user INT(11),
    id_petugas INT(11) NOT NULL,
    status ENUM('dibuka', 'ditutup') NOT NULL DEFAULT 'dibuka',
    PRIMARY KEY (id_lelang),
    FOREIGN KEY (id_barang) REFERENCES tb_barang(id_barang) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    FOREIGN KEY (id_user) REFERENCES tb_masyarakat(id_user) 
        ON DELETE SET NULL 
        ON UPDATE CASCADE,
    FOREIGN KEY (id_petugas) REFERENCES tb_petugas(id_petugas) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

CREATE TABLE history_lelang (
    id_history INT(11) NOT NULL AUTO_INCREMENT,
    id_lelang INT(11) NOT NULL,
    id_barang INT(11) NOT NULL,
    id_user INT(11) NOT NULL,
    penawaran_harga INT(20) NOT NULL,
    waktu_penawaran TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_history),
    FOREIGN KEY (id_lelang) REFERENCES tb_lelang(id_lelang) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    FOREIGN KEY (id_barang) REFERENCES tb_barang(id_barang) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    FOREIGN KEY (id_user) REFERENCES tb_masyarakat(id_user) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

CREATE INDEX idx_petugas_username ON tb_petugas(username);
CREATE INDEX idx_masyarakat_username ON tb_masyarakat(username);
CREATE INDEX idx_lelang_status ON tb_lelang(status);
CREATE INDEX idx_lelang_tanggal ON tb_lelang(tgl_lelang);
CREATE INDEX idx_history_lelang ON history_lelang(id_lelang);
CREATE INDEX idx_history_user ON history_lelang(id_user);

INSERT INTO tb_level (id_level, level) VALUES 
(1, 'administrator'),
(2, 'petugas');

INSERT INTO tb_petugas (nama_petugas, username, password, id_level) VALUES 
('Super Admin', 'admin', 'admin123', 1),
('Petugas Satu', 'petugas1', 'petugas123', 2);