database.sql

-- Hapus tabel Detail_transaksi jika ada
DROP TABLE IF EXISTS Detail_transaksi;

-- Hapus tabel Detail_kulakan jika ada
DROP TABLE IF EXISTS Detail_kulakan;

-- Hapus tabel Apoteker_obat_khusus jika ada
DROP TABLE IF EXISTS Apoteker_obat_khusus;

-- Hapus tabel Transaksi jika ada
DROP TABLE IF EXISTS Transaksi;

-- Hapus tabel Kulakan jika ada
DROP TABLE IF EXISTS Kulakan;

-- Hapus tabel Obat jika ada
DROP TABLE IF EXISTS Obat;

-- Hapus tabel Supplier_obat jika ada
DROP TABLE IF EXISTS Supplier_obat;

-- Hapus tabel Apoteker jika ada
DROP TABLE IF EXISTS Apoteker;

-- Hapus tabel Pelanggan jika ada
DROP TABLE IF EXISTS Pelanggan;

-- Menggunakan database apotek
USE Apotek

-- Membuat tabel pelanggan
CREATE TABLE Pelanggan(
	id_pelanggan VARCHAR(10) PRIMARY KEY,
	nama_pelanggan VARCHAR(255) NOT NULL,	
	alamat_pelanggan VARCHAR(255),
	no_telp_pelanggan VARCHAR(13),
	jenis_kelamin_pelanggan VARCHAR(1) NOT NULL,
	email_pelanggan VARCHAR(255)
);

-- Menambahkan data pelanggan
INSERT INTO Pelanggan (
    id_pelanggan, 
    nama_pelanggan, 
    alamat_pelanggan, 
    no_telp_pelanggan, 
    jenis_kelamin_pelanggan, 
    email_pelanggan
) VALUES
	('PEL01', 'Andi Saputra', 'Jl. Merdeka No. 10, Jakarta', '6281234567890', 'L', 'andi.saputra01@gmail.com'),
	('PEL02', 'Budi Santoso', 'Jl. Sudirman No. 5, Bandung', '6281234567891', 'L', 'budi.santoso23@gmail.com'),
	('PEL03', 'Citra Dewi', 'Jl. Mangga Dua No. 3, Surabaya', '6281234567892', 'P', 'citra.dewi88@gmail.com'),
	('PEL04', 'Dedi Kurniawan', 'Jl. Diponegoro No. 7, Medan', '6281234567893', 'L', 'dedi.kurniawan@gmail.com'),
	('PEL05', 'Eka Putri', 'Jl. Ahmad Yani No. 12, Semarang', '6281234567894', 'P', 'eka.putri99@gmail.com'),
	('PEL06', 'Fajar Pratama', 'Jl. Gatot Subroto No. 20, Makassar', '6281234567895', 'L', 'fajar.pratama12@gmail.com'),
	('PEL07', 'Gina Larasati', 'Jl. Imam Bonjol No. 8, Yogyakarta', '6281234567896', 'P', 'gina.larasati45@gmail.com'),
	('PEL08', 'Hariyanto', 'Jl. Raden Saleh No. 6, Bali', '6281234567897', 'L', 'hariyanto89@gmail.com'),
	('PEL09', 'Indah Permatasari', 'Jl. Panjaitan No. 22, Malang', '6281234567898', 'P', 'indah.permatasari77@gmail.com'),
	('PEL10', 'Joko Supriyanto', 'Jl. Pemuda No. 15, Solo', '6281234567899', 'L', 'joko.supriyanto31@gmail.com'),
	('PEL11', 'Kiki Amalia', 'Jl. Sisingamangaraja No. 11, Palembang', '6281234567900', 'P', 'kiki.amalia56@gmail.com'),
	('PEL12', 'Lina Wijaya', 'Jl. Jenderal Sudirman No. 18, Pontianak', '6281234567901', 'P', 'lina.wijaya24@gmail.com'),
	('PEL13', 'Maya Sari', 'Jl. Asia Afrika No. 9, Jakarta', '6281234567902', 'P', 'maya.sari02@gmail.com'),
	('PEL14', 'Nina Oktaviani', 'Jl. Tamansari No. 14, Bandung', '6281234567903', 'P', 'nina.oktaviani66@gmail.com'),
	('PEL15', 'Omar Syahputra', 'Jl. Hasanuddin No. 30, Surabaya', '6281234567904', 'P', 'omar.syahputra11@gmail.com'),
	('PEL16', 'Idul FItri', 'Jl. Ir Sutami no 36, jebres  Surakarta', '08123456789', 'P', 'id_ft@gmail.com'),
	('PEL17', 'Idul Adha', 'Jl. Ir Tentara pelajar no 36, Mojosongo, Surakarta', '081133335555', 'L', ' id_ad@gmail.com');

-- Melihat tabel pelanggan
-- SELECT * FROM pelanggan;

-- Membuat tabel apoteker
CREATE TABLE Apoteker(
	id_apoteker VARCHAR(10) PRIMARY KEY,
	nama_apoteker VARCHAR(255) NOT NULL,
	lisensi_apoteker VARCHAR(10) NOT NULL,
	no_telp_apoteker VARCHAR(13) NOT NULL,
	shift_apoteker VARCHAR(10),
	jenis_kelamin_apoteker VARCHAR(1) NOT NULL,
	tgl_lahir_apoteker DATE NOT NULL,
	alamat_apoteker VARCHAR(255),
	tgl_diterima_kerja DATE NOT NULL,
	password_hash VARCHAR(255)
);

-- Menambahkan data ke apoteker
INSERT INTO Apoteker (
    id_apoteker, 
    nama_apoteker, 
    lisensi_apoteker, 
    no_telp_apoteker, 
    shift_apoteker, 
    jenis_kelamin_apoteker, 
    tgl_lahir_apoteker, 
    alamat_apoteker, 
    tgl_diterima_kerja,
    password_hash
) VALUES
	('APT01', 'Agus Ramadhan', 'L-98345', '6281345670001', 'Pagi', 'L', '1985-03-12', 'Jl. Merdeka No.1, Jakarta', '2013-03-20', 'password_hash_1'),
	('APT02', 'Bella Nuraini', 'L-78346', '6281345670002', 'Siang', 'P', '1990-07-25', 'Jl. Sudirman No.2, Bandung', '2010-01-10', 'password_hash_2'),
	('APT03', 'Cindy Safira', 'L-65347', '6281345670003', 'Malam', 'P', '1988-11-14', 'Jl. Diponegoro No.3, Surabaya', '2015-04-18', 'password_hash_3'),
	('APT04', 'Dewi Kartika', 'L-54348', '6281345670004', 'Pagi', 'P', '1992-02-10', 'Jl. Gajah Mada No.4, Medan', '2011-05-15', 'password_hash_4'),
	('APT05', 'Edo Suryana', 'L-42349', '6281345670005', 'Siang', 'L', '1986-09-18', 'Jl. Kartini No.5, Semarang', '2014-10-12', 'password_hash_5'),
	('APT06', 'Farhan Hakim', 'L-37350', '6281345670006', 'Malam', 'L', '1991-05-21', 'Jl. Pemuda No.6, Yogyakarta', '2016-08-25', 'password_hash_6'),
	('APT07', 'Gilang Pratama', 'L-52351', '6281345670007', 'Pagi', 'L', '1987-06-30', 'Jl. Ahmad Yani No.7, Makassar', '2012-07-01', 'password_hash_7'),
	('APT08', 'Hana Widjaja', 'L-62352', '6281345670008', 'Siang', 'P', '1993-03-05', 'Jl. Pattimura No.8, Bali', '2017-02-10', 'password_hash_8'),
	('APT09', 'Indra Susilo', 'L-72353', '6281345670009', 'Malam', 'L', '1999-12-28', 'Jl. Pahlawan No.9, Palembang', '2018-11-05', 'password_hash_9'),
	('APT10', 'Jihan Maharani', 'L-82354', '6281345670010', 'Pagi', 'P', '1990-10-17', 'Jl. Hasanuddin No.10, Malang', '2019-09-18', 'password_hash_10'),
	('APT11', 'Kurniawan Aditya', 'L-92355', '6281345670011', 'Siang', 'L', '1985-04-19', 'Jl. Sisingamangaraja No.11, Aceh', '2020-06-30', 'password_hash_11'),
	('APT12', 'Laras Permata', 'L-33356', '6281345670012', 'Malam', 'P', '1992-08-23', 'Jl. HOS Cokroaminoto No.12, Lombok', '2021-12-22', 'password_hash_12'),
	('APT13', 'Miko Ardiansyah', 'L-54357', '6281345670013', 'Pagi', 'L', '1999-01-07', 'Jl. Basuki Rahmat No.13, Lampung', '2022-05-13', 'password_hash_13'),
	('APT14', 'Novi Amalia', 'L-64358', '6281345670014', 'Siang', 'P', '1995-11-15', 'Jl. Bung Karno No.14, Maluku', '2010-03-07', 'password_hash_14'),
	('APT15', 'Oscar Ferbianto', 'L-75359', '6281345670015', 'Malam', 'P', '2000-12-12', 'Jl. Budi Utomo No.15, Papua', '2023-07-25', 'password_hash_15');

-- Melihat isi tabel apoteker
-- SELECT * FROM apoteker;

-- Membuat tabel obat
CREATE TABLE Obat(
	id_obat VARCHAR(10) PRIMARY KEY,
	nama_obat VARCHAR(255) NOT NULL,
	harga_obat MONEY NOT NULL,
	stok_obat INT NOT NULL,
	kategori_obat VARCHAR(50),
	tgl_kedaluarsa DATE NOT NULL,
	kemasan VARCHAR(50) NOT NULL,
	id_supplier VARCHAR(10),

	CHECK (tgl_kedaluarsa > GETDATE())
);

-- Menambahkan data obat dengan nama lengkap (satuan) dan jenis kemasan
INSERT INTO Obat (id_obat, nama_obat, harga_obat, stok_obat, kategori_obat, tgl_kedaluarsa, kemasan, id_supplier) VALUES 
	('OBT01', 'Paracetamol 500 mg', 10000, 100, 'Analgesik', CAST('2025-08-01' AS DATE), 'Kaplet', 'SUP01'),
	('OBT02', 'Amoxicillin 250 mg', 25000, 50, 'Antibiotik', CAST('2025-12-15' AS DATE), 'Kapsul', 'SUP02'),
	('OBT03', 'Cetirizine 10 mg', 15000, 70, 'Antihistamin', CAST('2025-05-20' AS DATE), 'Tablet', 'SUP03'),
	('OBT04', 'Ibuprofen 400 mg', 18000, 80, 'Analgesik', CAST('2025-11-10' AS DATE), 'Kaplet', 'SUP04'),
	('OBT05', 'Omeprazole 20 mg', 30000, 40, 'Antasida', CAST('2025-07-01' AS DATE), 'Kapsul', 'SUP05'),
	('OBT06', 'Metformin 500 mg', 50000, 60, 'Antidiabetik', CAST('2025-09-30' AS DATE), 'Tablet', 'SUP06'),
	('OBT07', 'Loperamide 2 mg', 12000, 100, 'Antidiare', CAST('2025-10-25' AS DATE), 'Tablet', 'SUP07'),
	('OBT08', 'Diazepam 5 mg', 45000, 30, 'Sedatif', CAST('2025-01-01' AS DATE), 'Tablet', 'SUP08'),
	('OBT09', 'Ranitidine 50 mg/2 ml', 22000, 90, 'Antasida', CAST('2025-12-01' AS DATE), 'Ampul', 'SUP09'),
	('OBT10', 'Amlodipine 10 mg', 20000, 85, 'Antihipertensi', CAST('2025-08-10' AS DATE), 'Tablet', 'SUP10'),
	('OBT11', 'Salbutamol 100 mcg/dosis', 35000, 35, 'Bronkodilator', CAST('2025-09-20' AS DATE), 'Inhaler', 'SUP11'),
	('OBT12', 'Simvastatin 20 mg', 28000, 120, 'Hipolipidemik', CAST('2025-07-15' AS DATE), 'Tablet', 'SUP12'),
	('OBT13', 'Clopidogrel 75 mg', 40000, 45, 'Antiplatelet', CAST('2025-04-05' AS DATE), 'Tablet', 'SUP13'),
	('OBT14', 'Captopril 25 mg', 17000, 60, 'Antihipertensi', CAST('2025-11-18' AS DATE), 'Tablet', 'SUP14'),
	('OBT15', 'Dexamethasone 0.5 mg', 60000, 150, 'Kortikosteroid', CAST('2025-03-30' AS DATE), 'Tablet', 'SUP15'),
	('OBT16', 'Sanmol 500 mg', 12000, 50, 'Analgesik', CAST('2025-08-15' AS DATE), 'Kaplet', 'SUP01'),
	('OBT17', 'Zink Tablet 1 blister', 18000, 40, 'Suplemen', CAST('2025-10-05' AS DATE), 'Blister', 'SUP02'),
	('OBT18', 'Vitamin D 1 blister', 25000, 60, 'Vitamin', CAST('2025-09-01' AS DATE), 'Blister', 'SUP03'),
	('OBT19', 'Obat Maag 1 blister', 22000, 70, 'Antasida', CAST('2025-11-30' AS DATE), 'Blister', 'SUP04');

-- Melihat isi tabel obat
SELECT * FROM Obat;

-- Membuat tabel supplier_obat
CREATE TABLE Supplier_obat(
	id_supplier VARCHAR(10) PRIMARY KEY,
	nama_supplier VARCHAR(255) NOT NULL,
	alamat_supplier VARCHAR(255) NOT NULL,
	no_telp_supplier VARCHAR(13) NOT NULL
);

-- Menambahkan data ke supplier_obat
INSERT INTO Supplier_obat (id_supplier, nama_supplier, alamat_supplier, no_telp_supplier) VALUES
	('SUP01', 'PT. Obat Sehat', 'Jl. Kesehatan No. 10', '62211234567'),
	('SUP02', 'CV. Herbal Sejahtera', 'Jl. Tanaman No. 20', '62217654321'),
	('SUP03', 'UD. Sumber Alami', 'Jl. Flora No. 5', '62219876543'),
	('SUP04', 'Toko Obat Jaya', 'Jl. Pengobatan No. 2', '62211239874'),
	('SUP05', 'Apotek Maju Sejahtera', 'Jl. Obat Tradisional No. 7', '62214567890'),
	('SUP06', 'Apotek Nusantara', 'Jl. Merdeka No. 15', '62213456789'),
	('SUP07', 'CV. Sumber Sehat', 'Jl. Sumber Sehat No. 12', '62215432167'),
	('SUP08', 'Toko Herbal Alami', 'Jl. Tanaman Obat No. 30', '62216543210'),
	('SUP09', 'PT. Farmasi Nusantara', 'Jl. Pahlawan No. 9', '62217654321'),
	('SUP10', 'UD. Obat Makmur', 'Jl. Sejahtera No. 5', '62218765432'),
	('SUP11', 'Apotek Sumber Jaya', 'Jl. Harmoni No. 3', '62218765432'),
	('SUP12', 'Toko Obat Sentosa', 'Jl. Sentosa No. 14', '62212345678'),
	('SUP13', 'CV. Farmasi Mandiri', 'Jl. Kemandirian No. 25', '62219876543'),
	('SUP14', 'PT. Herbal Kesehatan', 'Jl. Sehat Alami No. 18', '62215674321'),
	('SUP15', 'Apotek Keluarga Sehat', 'Jl. Harmoni Sejahtera No. 8', '62218765432');

-- Menambahkan constraint obat.id_supplier 
ALTER TABLE Obat
ADD CONSTRAINT fk_id_supplier 
	FOREIGN KEY (id_supplier)
	REFERENCES Supplier_obat (id_supplier) ON UPDATE CASCADE;

-- Melihat tabel supplier
SELECT * FROM supplier_obat;

-- Membuat tabel Apoteker_obat_khusus untuk menyimpan obat yang dapat ditangani oleh apoteker
CREATE TABLE Apoteker_obat_khusus (
    id_apoteker VARCHAR(10) NOT NULL,
    id_obat VARCHAR(10) NOT NULL,
    tanggal_ditambahkan DATETIME DEFAULT GETDATE(),
    CONSTRAINT PK_Apoteker_obat_khusus PRIMARY KEY (id_apoteker, id_obat),
    CONSTRAINT FK_Apoteker_obat_khusus_Apoteker FOREIGN KEY (id_apoteker) REFERENCES Apoteker(id_apoteker),
    CONSTRAINT FK_Apoteker_obat_khusus_Obat FOREIGN KEY (id_obat) REFERENCES Obat(id_obat)
);

-- Menambahkan data contoh untuk Apoteker_obat_khusus
INSERT INTO Apoteker_obat_khusus (id_apoteker, id_obat) VALUES
    ('APT01', 'OBT01'), -- Agus Ramadhan menangani Paracetamol
    ('APT01', 'OBT02'), -- Agus Ramadhan menangani Amoxicillin
    ('APT02', 'OBT03'), -- Bella Nuraini menangani Omeprazole
    ('APT02', 'OBT04'), -- Bella Nuraini menangani Ibuprofen
    ('APT03', 'OBT05'), -- Cindy Safira menangani Cetirizine
    ('APT03', 'OBT06'), -- Cindy Safira menangani Metformin
    ('APT04', 'OBT07'), -- Dewi Kartika menangani Simvastatin
    ('APT04', 'OBT08'), -- Dewi Kartika menangani Amlodipine
    ('APT05', 'OBT09'), -- Edo Suryana menangani Losartan
    ('APT05', 'OBT10'); -- Edo Suryana menangani Aspirin

-- Membuat tabel transaksi
CREATE TABLE Transaksi(
	id_transaksi VARCHAR(10) PRIMARY KEY,
	id_pelanggan VARCHAR(10) NOT NULL,
	id_apoteker VARCHAR(10) NOT NULL,
	waktu_transaksi DATETIME NOT NULL,
	total_harga MONEY NOT NULL
);

-- Menambahkan data ke tabel transaksi-- Menambahkan data ke tabel transaksi (diperbaiki)
INSERT INTO Transaksi (id_transaksi, id_pelanggan, id_apoteker, total_harga, waktu_transaksi) VALUES
    ('IDT01', 'PEL01', 'APT01', 240000.00, '2025-04-23 09:15:00'),
    ('IDT02', 'PEL02', 'APT02', 170000.00, '2024-09-11 14:30:00'),
    ('IDT03', 'PEL03', 'APT03', 58000.00, '2024-08-29 18:00:00'),
    ('IDT04', 'PEL04', 'APT04', 160000.00, '2025-01-25 08:00:00'),
    ('IDT05', 'PEL05', 'APT05', 190000.00, '2025-03-17 13:15:00'),
    ('IDT06', 'PEL06', 'APT06', 235000.00, '2024-10-19 19:20:00'),
    ('IDT07', 'PEL07', 'APT07', 80000.00, '2024-12-30 09:20:00'),
    ('IDT08', 'PEL08', 'APT08', 75000.00, '2025-04-01 15:00:00'),
    ('IDT09', 'PEL09', 'APT09', 140000.00, '2024-11-11 20:30:00'),
    ('IDT10', 'PEL10', 'APT10', 170000.00, '2024-07-20 10:00:00'),
    ('IDT11', 'PEL11', 'APT11', 220000.00, '2025-05-20 16:15:00'),
    ('IDT12', 'PEL12', 'APT12', 69000.00, '2024-04-25 21:00:00'),
    ('IDT13', 'PEL13', 'APT13', 110000.00, '2025-06-02 11:15:00'),
    ('IDT14', 'PEL14', 'APT14', 87000.00, '2024-10-27 17:00:00'),
    ('IDT15', 'PEL15', 'APT15', 115000.00, '2024-12-11 18:45:00');

-- Menambahkan constraint FK ke transaksi - pelanggan
ALTER TABLE Transaksi
ADD CONSTRAINT fk_id_pelanggan
	FOREIGN KEY (id_pelanggan) 
	REFERENCES Pelanggan (id_pelanggan)  ON UPDATE CASCADE;

-- Menambahkan constraint FK ke transaksi - apoteker
ALTER TABLE Transaksi
ADD CONSTRAINT fk_id_apoteker
	FOREIGN KEY (id_apoteker) 
	REFERENCES Apoteker (id_apoteker);

-- Menampilkan data transaksi
-- SELECT * FROM transaksi;

-- Membuat tabel detail_transaksi
CREATE TABLE Detail_transaksi(
	id_transaksi VARCHAR(10) NOT NULL,
	id_obat VARCHAR(10) NOT NULL,
	kuantitas_obat INT NOT NULL,

	PRIMARY KEY(id_transaksi, id_obat)
);

-- Menambahkan data ke detail_transaksi
INSERT INTO Detail_transaksi (id_transaksi, id_obat, kuantitas_obat) VALUES
    ('IDT01', 'OBT01', 2),
    ('IDT01', 'OBT03', 1),
    ('IDT02', 'OBT02', 3),
    ('IDT02', 'OBT05', 2),
    ('IDT03', 'OBT03', 4),
    ('IDT03', 'OBT07', 2),
    ('IDT04', 'OBT04', 5),
    ('IDT04', 'OBT08', 1),
    ('IDT05', 'OBT05', 3),
    ('IDT05', 'OBT09', 2),
    ('IDT06', 'OBT06', 1),
    ('IDT06', 'OBT11', 3),
    ('IDT07', 'OBT07', 4),
    ('IDT07', 'OBT02', 2),
    ('IDT08', 'OBT08', 1),
    ('IDT08', 'OBT01', 2),
    ('IDT09', 'OBT09', 5),
    ('IDT09', 'OBT13', 2),
    ('IDT10', 'OBT10', 3),
    ('IDT10', 'OBT04', 1),
    ('IDT11', 'OBT11', 2),
    ('IDT11', 'OBT14', 3),
    ('IDT12', 'OBT12', 1),
    ('IDT12', 'OBT06', 4),
    ('IDT13', 'OBT13', 2),
    ('IDT13', 'OBT10', 3),
    ('IDT14', 'OBT14', 5),
    ('IDT14', 'OBT15', 2),
    ('IDT15', 'OBT15', 3),
    ('IDT15', 'OBT05', 1);

-- Menambahkan constraint detail_transaksi.id_transaksi ke tabel transaksi
ALTER TABLE Detail_transaksi
ADD CONSTRAINT fk_transaksi
	FOREIGN KEY (id_transaksi)
	REFERENCES transaksi (id_transaksi) ON UPDATE CASCADE;

-- Menambahkan constraint detail_transaksi.id_obat ke tabel obat
ALTER TABLE Detail_transaksi
ADD CONSTRAINT fk_obat
	FOREIGN KEY (id_obat)
	REFERENCES obat (id_obat);

-- Menampilkan data detail_transaksi
SELECT * FROM detail_transaksi;

-- Membuat tabel kulakan
CREATE TABLE Kulakan(
	id_kulakan VARCHAR(10) PRIMARY KEY,
	id_supplier_obat VARCHAR(10) NOT NULL,
	id_apoteker VARCHAR(10) NOT NULL,
	tgl_kulakan DATE NOT NULL,
	total_harga_kulakan MONEY NOT NULL
);

-- Menambahkan data ke kulakan
INSERT INTO Kulakan (id_kulakan, id_supplier_obat, id_apoteker, tgl_kulakan, total_harga_kulakan) VALUES
    ('KLK001', 'SUP01', 'APT01', '2022-01-12', 750000),
    ('KLK002', 'SUP02', 'APT02', '2022-03-25', 820000),
    ('KLK003', 'SUP03', 'APT03', '2022-01-12', 640000),
    ('KLK004', 'SUP04', 'APT04', '2022-07-14', 900000),
    ('KLK005', 'SUP05', 'APT05', '2022-08-19', 780000),
    ('KLK006', 'SUP06', 'APT06', '2022-09-03', 950000),
    ('KLK007', 'SUP07', 'APT07', '2022-10-17', 670000),
    ('KLK008', 'SUP08', 'APT08', '2022-11-28', 720000),
    ('KLK009', 'SUP09', 'APT09', '2022-12-15', 890000),
    ('KLK010', 'SUP10', 'APT10', '2023-02-07', 930000),
    ('KLK011', 'SUP11', 'APT11', '2023-03-18', 800000),
    ('KLK012', 'SUP12', 'APT12', '2023-05-23', 860000),
    ('KLK013', 'SUP13', 'APT13', '2023-06-30', 940000),
    ('KLK014', 'SUP14', 'APT14', '2023-08-11', 770000),
    ('KLK015', 'SUP15', 'APT15', '2023-10-02', 990000);

-- Menambahkan constraint kulakan.id_supplier_obat ke supplier_obat
ALTER TABLE Kulakan
ADD CONSTRAINT fk_supplier_obat
	FOREIGN KEY (id_supplier_obat)
	REFERENCES supplier_obat (id_supplier) ON UPDATE CASCADE;

-- Menambahkan constraint kulakan.id_apoteker ke apoteker
ALTER TABLE Kulakan
ADD CONSTRAINT fk_apoteker
	FOREIGN KEY (id_apoteker)
	REFERENCES Apoteker (id_apoteker);

-- Menampilkan tabel kulakan
-- SELECT * FROM kulakan;

-- Membuat tabel detail_kulakan
CREATE TABLE Detail_kulakan(
	id_kulakan VARCHAR(10) NOT NULL,
	id_obat VARCHAR(10) NOT NULL,
	kuantitas_obat INT NOT NULL,

	PRIMARY KEY(id_kulakan, id_obat)
);

-- Menambahkan data detail_kulakan
INSERT INTO Detail_kulakan (id_kulakan, id_obat, kuantitas_obat) VALUES
    -- Kulakan KLK001
    ('KLK001', 'OBT01', 100),
    ('KLK001', 'OBT02', 50),
    ('KLK001', 'OBT03', 70),

    -- Kulakan KLK002
    ('KLK002', 'OBT01', 150),
    ('KLK002', 'OBT04', 80),
    ('KLK002', 'OBT05', 40),

    -- Kulakan KLK003
    ('KLK003', 'OBT06', 60),
    ('KLK003', 'OBT02', 30),
    ('KLK003', 'OBT07', 100),

    -- Kulakan KLK004
    ('KLK004', 'OBT01', 90),
    ('KLK004', 'OBT03', 45),
    ('KLK004', 'OBT08', 30),

    -- Kulakan KLK005
    ('KLK005', 'OBT09', 120),
    ('KLK005', 'OBT10', 85),
    ('KLK005', 'OBT02', 40),

    -- Kulakan KLK006
    ('KLK006', 'OBT11', 35),
    ('KLK006', 'OBT05', 50),
    ('KLK006', 'OBT06', 65),

    -- Kulakan KLK007
    ('KLK007', 'OBT01', 80),
    ('KLK007', 'OBT12', 120),
    ('KLK007', 'OBT04', 60),

    -- Kulakan KLK008
    ('KLK008', 'OBT02', 70),
    ('KLK008', 'OBT13', 45),
    ('KLK008', 'OBT07', 90),

    -- Kulakan KLK009
    ('KLK009', 'OBT14', 60),
    ('KLK009', 'OBT06', 40),
    ('KLK009', 'OBT09', 110),

    -- Kulakan KLK010
    ('KLK010', 'OBT01', 75),
    ('KLK010', 'OBT03', 50),
    ('KLK010', 'OBT15', 100),

    -- Kulakan KLK011
    ('KLK011', 'OBT05', 30),
    ('KLK011', 'OBT02', 40),
    ('KLK011', 'OBT08', 25),

    -- Kulakan KLK012
    ('KLK012', 'OBT11', 40),
    ('KLK012', 'OBT04', 55),
    ('KLK012', 'OBT13', 35),

    -- Kulakan KLK013
    ('KLK013', 'OBT10', 70),
    ('KLK013', 'OBT02', 40),
    ('KLK013', 'OBT06', 65),

    -- Kulakan KLK014
    ('KLK014', 'OBT14', 90),
    ('KLK014', 'OBT01', 110),
    ('KLK014', 'OBT05', 45),

    -- Kulakan KLK015
    ('KLK015', 'OBT15', 75),
    ('KLK015', 'OBT02', 60),
    ('KLK015', 'OBT12', 100);


-- Menambahkan constraint detail_kulakan.id_kulakan ke kulakan
ALTER TABLE Detail_kulakan
ADD CONSTRAINT fk_kulakan
	FOREIGN KEY (id_kulakan)
	REFERENCES Kulakan (id_kulakan)  ON UPDATE CASCADE;

-- Menambahkan constraint detail_kulakan.id_kulakan ke kulakan
ALTER TABLE Detail_kulakan
ADD CONSTRAINT fk_dt_obat
	FOREIGN KEY (id_obat)
	REFERENCES Obat (id_obat);

-- Menampilkan tabel detail_kulakan
SELECT * FROM Detail_kulakan;

-- Buat idul adha
INSERT INTO Transaksi(id_transaksi, id_pelanggan, id_apoteker, waktu_transaksi, total_harga)
VALUES ('IDT16', 'PEL17', 'APT09', GETDATE(), 30000);

INSERT INTO Detail_transaksi (id_transaksi, id_obat, kuantitas_obat)
VALUES ('IDT16', 'OBT16', 1), ('IDT16', 'OBT17', 1);

-- Buat Idul fitri
INSERT INTO Transaksi(id_transaksi, id_pelanggan, id_apoteker, waktu_transaksi, total_harga)
VALUES ('IDT17', 'PEL16', 'APT08', GETDATE(), 47000);

INSERT INTO Detail_transaksi (id_transaksi, id_obat, kuantitas_obat)
VALUES ('IDT17', 'OBT18', 1), ('IDT17', 'OBT19', 1);

SELECT * FROM Detail_transaksi;