-- Membuat Tabel Users
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) CHECK(role IN ('pelanggan', 'admin')) NOT NULL
);

-- Membuat Tabel Spare Parts
CREATE TABLE spare_parts (
    id SERIAL PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    harga DECIMAL(10, 2) NOT NULL,
    stok INTEGER NOT NULL,
    deskripsi TEXT
);

-- Membuat Tabel Orders
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    spare_part_id INTEGER REFERENCES spare_parts(id) ON DELETE CASCADE,
    quantity INTEGER NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) CHECK(status IN ('pending', 'proses', 'selesai')) NOT NULL
);

-- Membuat Tabel Transactions
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method VARCHAR(20) CHECK(payment_method IN ('tunai', 'digital')) NOT NULL,
    status VARCHAR(20) CHECK(status IN ('berhasil', 'gagal', 'pending')) NOT NULL
);