-- Table for users
CREATE TABLE pdta_pengguna (
    id_pengguna INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for item sources
CREATE TABLE pdta_asal_usul (
    id_asal INT AUTO_INCREMENT PRIMARY KEY,
    sumber_asal VARCHAR(100) NOT NULL
);

-- Table for item types
CREATE TABLE pdta_jenis_barang (
    id_jenis INT AUTO_INCREMENT PRIMARY KEY,
    nama_jenis VARCHAR(50) NOT NULL
);

-- Table for locations
CREATE TABLE pdta_lokasi (
    id_lokasi INT AUTO_INCREMENT PRIMARY KEY,
    nama_lokasi VARCHAR(100) NOT NULL
);

-- Table for inventory items
CREATE TABLE pdta_inventaris_barang (
    id_barang INT AUTO_INCREMENT PRIMARY KEY,
    kode_barang VARCHAR(50) NOT NULL UNIQUE,
    nama_barang VARCHAR(100) NOT NULL,
    deskripsi TEXT,
    merk VARCHAR(50),
    type VARCHAR(50),
    ukuran VARCHAR(50),
    bahan VARCHAR(50),
    jumlah INT NOT NULL,
    tahun_pembelian YEAR,
    kondisi ENUM('Baik', 'Rusak Ringan', 'Rusak Berat') DEFAULT 'Baik',
    keterangan TEXT,
    foto VARCHAR(255),
    
    -- Relations
    id_lokasi INT NOT NULL,
    id_jenis INT NOT NULL,
    id_asal INT, -- Optional, allows NULL
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign key constraints with correct table names
    FOREIGN KEY (id_lokasi) REFERENCES pdta_lokasi(id_lokasi) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (id_jenis) REFERENCES pdta_jenis_barang(id_jenis) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (id_asal) REFERENCES pdta_asal_usul(id_asal) ON DELETE SET NULL ON UPDATE CASCADE
);