# TABEL #

-- tabel supplier
CREATE TABLE supplier(
    id INT NOT NULL AUTO_INCREMENT,
    nik VARCHAR(16) UNIQUE,
    npwp VARCHAR(20) UNIQUE,
    nama VARCHAR(255),
    telp VARCHAR(20),
    alamat text,

    CONSTRAINT pk_supplier_id PRIMARY KEY(id)
);

-- tabel supplier pengganti
-- 1 supplier punya n pengganti
-- 1 pengganti punya 1 supplier
CREATE TABLE p_supplier(
    id INT NOT NULL AUTO_INCREMENT,
    id_supplier INT,
    nik VARCHAR(16) UNIQUE,
    npwp VARCHAR(20) UNIQUE,
    nama VARCHAR(255),
    telp VARCHAR(20),
    alamat text,

    CONSTRAINT pk_p_supplier_id PRIMARY KEY(id),
    CONSTRAINT fk_p_supplier_id_supplier FOREIGN KEY(id_supplier) REFERENCES supplier(id)
);

-- tabel barang
CREATE TABLE barang(
    id INT NOT NULL AUTO_INCREMENT,
    kd_barang VARCHAR(25),
    nama VARCHAR(50),
    ket text,

    CONSTRAINT pk_barang_id PRIMARY KEY(id)
);

-- tabel transaksi pembelian
-- 1 supplier/pengganti n pembelian
CREATE TABLE pembelian(
    id INT NOT NULL AUTO_INCREMENT,
    invoice VARCHAR(16) UNIQUE, -- kombinasi PB-tgl-no_urut
    tgl DATE,
    id_supplier INT,
    id_p_supplier INT,
    jenis CHAR(1), -- payment term, cash/tunai: c, transfer: t
    STATUS CHAR(1), -- s: sukses, t: titipan
    pph DOUBLE(12,2),
    total DOUBLE(14,2),
    ket text,

    CONSTRAINT pk_pembelian_id PRIMARY KEY(id),
    CONSTRAINT fk_pembelian_id_supplier FOREIGN KEY(id_supplier) REFERENCES supplier(id),
    CONSTRAINT fk_pembelian_id_p_supplier FOREIGN KEY(id_p_supplier) REFERENCES p_supplier(id)
);

-- tabel detail pembelian
CREATE TABLE detail_pembelian(
    id INT NOT NULL AUTO_INCREMENT,
    id_pembelian INT,
    id_barang INT,
    colly SMALLINT,
    netto DOUBLE(10,2),
    harga DOUBLE(10,2),
    subtotal DOUBLE(14,2),

    CONSTRAINT pk_detail_pembelian_id PRIMARY KEY(id),
    CONSTRAINT fk_detail_pembelian_id_pembelian FOREIGN KEY(id_pembelian) REFERENCES pembelian(id),
    CONSTRAINT fk_detail_pembelian_id_barang FOREIGN KEY(id_barang) REFERENCES barang(id)
);


# ======================================================================== #

# PROCEDURE #

# PROCEDURE tambah p_supplier
CREATE PROCEDURE tambah_p_supplier(
    IN nik_supplier_param VARCHAR(16),
    IN nik_param VARCHAR(16),
    IN npwp_param VARCHAR(20),
    IN nama_param VARCHAR(255),
    IN telp_param VARCHAR(20),
    IN alamat_param text
)
BEGIN
    DECLARE id_supplier_param INT;

    -- get id supplier
    SELECT id INTO id_supplier_param FROM supplier WHERE nik=nik_supplier_param;

    -- insert supplier pengganti
    INSERT INTO p_supplier
        (id_supplier, nik, npwp, nama, telp, alamat)
    VALUES
        (id_supplier_param, nik_param, npwp_param, nama_param, telp_param, alamat_param);
END;

-- ====================================== --

# PROCEDURE tambah detail pembelian
CREATE PROCEDURE tambah_detail_pembelian(
    IN invoice_param VARCHAR(16),
    IN id_barang_param INT,
    IN colly_param SMALLINT,
    IN netto_param DOUBLE(10,2),
    IN harga_param DOUBLE(10,2),
    IN subtotal_param DOUBLE(14,2)
)
BEGIN
    DECLARE id_pembelian_param INT;

    -- get id pembelian
    SELECT id INTO id_pembelian_param FROM pembelian WHERE invoice=invoice_param;

    -- insert detail pembelian
    INSERT INTO detail_pembelian
        (id_pembelian, id_barang, colly, netto, harga, subtotal)
    VALUES
        (id_pembelian_param, id_barang_param, colly_param, netto_param, harga_param, subtotal_param);
END;

-- ====================================== --