script.sql

-- ==========================================
-- 0. CLEANUP (DROP TABLES IF EXISTS)
-- ==========================================
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE STUDENT_VISITOR CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE TASK_SUPPLY CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE TASK_STAFF CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE MENU_SUPPLY CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE STUDENT_TRANSACTION CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE STAFF_TRANSACTION CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE ROOM_BILLING CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE MENU_BILLING CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE ROOM_ASSIGNMENT CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE STUDENT_PHONE CASCADE CONSTRAINTS';

   EXECUTE IMMEDIATE 'DROP TABLE DORM_TRANSACTION CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE TASK CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE VISITOR CASCADE CONSTRAINTS';

   EXECUTE IMMEDIATE 'DROP TABLE SUPPLY CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE STAFF CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE STUDENT CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE ROOM CASCADE CONSTRAINTS';
   EXECUTE IMMEDIATE 'DROP TABLE MENU CASCADE CONSTRAINTS';

EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/


-- ==========================================
-- 1. TABLE CREATIONS
-- ==========================================

-- 1. MENU TABLE
CREATE TABLE MENU (
    MenuID INT,         
    MealType VARCHAR2(50) NOT NULL,  
    Price DECIMAL(10, 2) NOT NULL,  
    DateServed DATE NOT NULL              
);

-- 2. ROOM TABLE
CREATE TABLE ROOM (
    RoomID NUMBER,
    Block VARCHAR2(5) NOT NULL, 
    FloorNumber NUMBER(2) NOT NULL,
    MaxCapacity NUMBER(2) NOT NULL,
    CurrentCapacity NUMBER(2) NOT NULL,
    OccupancyStatus VARCHAR2(20) NOT NULL,
    RoomType VARCHAR2(20) NOT NULL,
    Price NUMBER(10, 2) NOT NULL
);

-- 3. STAFF TABLE
CREATE TABLE STAFF (
    StaffID NUMBER,
    FirstName VARCHAR2(50) NOT NULL,
    MiddleName VARCHAR2(50),
    LastName VARCHAR2(50) NOT NULL,
    Role VARCHAR2(50),
    PhoneNumber VARCHAR2(20),
    Salary NUMBER(10, 2),
    HireDate DATE DEFAULT SYSDATE
);

-- 4. STUDENT TABLE (Age sütunu kaldırıldı)
CREATE TABLE STUDENT (
    StudentID NUMBER, 
    TCID NUMBER(11, 0) UNIQUE NOT NULL,
    FirstName VARCHAR2(20) NOT NULL,
    MiddleName VARCHAR2(20),
    LastName VARCHAR2(20) NOT NULL,
    Address VARCHAR2(100) NOT NULL,
    DateOfBirth DATE NOT NULL,
    RegistrationDate DATE NOT NULL
);

-- 5. STUDENT_PHONE TABLE
CREATE TABLE STUDENT_PHONE (
    StudentID NUMBER,
    PhoneNumber VARCHAR2(15)
);

-- 6. SUPPLY TABLE
CREATE TABLE SUPPLY (
    SupplyID      NUMBER,
    SupplyName    VARCHAR2(100) NOT NULL,
    StockAmount   NUMBER DEFAULT 0,
    Unit          VARCHAR2(20),
    PricePerUnit  NUMBER(10,2)
);

-- 7. TASK TABLE
CREATE TABLE TASK (
    TaskID NUMBER,
    TaskType VARCHAR2(50),
    ScheduledDate DATE DEFAULT SYSDATE,
    CompletionDate DATE,
    Description VARCHAR2(255),
    RoomID NUMBER
);

-- 8. DORM_TRANSACTION TABLE
CREATE TABLE DORM_TRANSACTION (
    TransactionID NUMBER, 
    TransactionDate DATE NOT NULL,
    Amount NUMBER(10, 2) NOT NULL,
    FlowType VARCHAR2(20) NOT NULL
);

-- 9. MENU_BILLING
CREATE TABLE MENU_BILLING(
    TransactionID NUMBER,
    MenuID NUMBER NOT NULL,
    PortionBought NUMBER NOT NULL
);

-- 10. MENU_SUPPLY
CREATE TABLE MENU_SUPPLY (
    MenuID       NUMBER,
    SupplyID     NUMBER,
    QuantityUsed NUMBER DEFAULT 1
);

-- 11. ROOM_ASSIGNMENT
CREATE TABLE ROOM_ASSIGNMENT (
    StudentID NUMBER,
    RoomID NUMBER,
    AssignmentDate DATE,
    AssignmentStatus VARCHAR2(20)
);

-- 12. ROOM_BILLING
CREATE TABLE ROOM_BILLING(
    TransactionID NUMBER,
    RoomID NUMBER NOT NULL,
    StudentID NUMBER NOT NULL,
    MonthsToStay NUMBER NOT NULL
);

-- 13. STAFF_TRANSACTION
CREATE TABLE STAFF_TRANSACTION (
    TransactionID NUMBER,
    StaffID NUMBER NOT NULL,
    PaymentMonth VARCHAR2(20) NOT NULL
);

-- 14. STUDENT_TRANSACTION
CREATE TABLE STUDENT_TRANSACTION (
    TransactionID NUMBER, 
    StudentID NUMBER NOT NULL,
    PaymentMethod VARCHAR2(20) NOT NULL
);

-- 15. TASK_STAFF
CREATE TABLE TASK_STAFF (
    TaskID  NUMBER,
    StaffID NUMBER,
    AssignedDate DATE DEFAULT SYSDATE
);

-- 16. TASK_SUPPLY
CREATE TABLE TASK_SUPPLY (
    TaskID       NUMBER,
    SupplyID     NUMBER,
    QuantityUsed NUMBER DEFAULT 1
);

-- VISITOR TABLE
CREATE TABLE VISITOR (
    VisitorID NUMBER,
    FirstName VARCHAR2(50) NOT NULL,
    MiddleName VARCHAR2(50),
    LastName VARCHAR2(50) NOT NULL,
    EntryDate DATE DEFAULT SYSDATE,
    ExitDate DATE
);

-- STUDENT_VISITOR (BRIDGE TABLE)
CREATE TABLE STUDENT_VISITOR (
    VisitorID NUMBER,
    StudentID NUMBER
);

-- ==========================================
-- 2. PRIMARY KEYS
-- ==========================================

ALTER TABLE MENU ADD CONSTRAINT pk_menu PRIMARY KEY (MenuID);
ALTER TABLE ROOM ADD CONSTRAINT pk_room PRIMARY KEY (RoomID);
ALTER TABLE STUDENT ADD CONSTRAINT pk_student PRIMARY KEY(StudentID);
ALTER TABLE STAFF ADD CONSTRAINT pk_staff PRIMARY KEY(StaffID);
ALTER TABLE TASK ADD CONSTRAINT pk_task PRIMARY KEY(TaskID);
ALTER TABLE SUPPLY ADD CONSTRAINT pk_supply PRIMARY KEY (SupplyID);
ALTER TABLE DORM_TRANSACTION ADD CONSTRAINT pk_transaction PRIMARY KEY(TransactionID);

ALTER TABLE VISITOR ADD CONSTRAINT pk_visitor PRIMARY KEY (VisitorID);
ALTER TABLE STUDENT_VISITOR ADD CONSTRAINT pk_student_visitor PRIMARY KEY (VisitorID);

ALTER TABLE STUDENT_PHONE ADD CONSTRAINT pk_student_phone PRIMARY KEY (StudentID, PhoneNumber);
ALTER TABLE ROOM_ASSIGNMENT ADD CONSTRAINT pk_room_assignment PRIMARY KEY (StudentID, AssignmentDate);
ALTER TABLE TASK_STAFF ADD CONSTRAINT pk_task_staff PRIMARY KEY (TaskID, StaffID);
ALTER TABLE MENU_SUPPLY ADD CONSTRAINT pk_menu_supply PRIMARY KEY (MenuID, SupplyID);
ALTER TABLE TASK_SUPPLY ADD CONSTRAINT pk_task_supply PRIMARY KEY (TaskID, SupplyID);

-- Sub-Table PKs
ALTER TABLE ROOM_BILLING ADD CONSTRAINT pk_room_billing PRIMARY KEY(TransactionID);
ALTER TABLE MENU_BILLING ADD CONSTRAINT pk_menu_billing PRIMARY KEY(TransactionID);
ALTER TABLE STAFF_TRANSACTION ADD CONSTRAINT pk_staff_transaction PRIMARY KEY (TransactionID);
ALTER TABLE STUDENT_TRANSACTION ADD CONSTRAINT pk_student_transaction PRIMARY KEY (TransactionID);


-- ==========================================
-- 3. FOREIGN KEYS
-- ==========================================

-- DORM_TRANSACTION Relations
ALTER TABLE ROOM_BILLING ADD CONSTRAINT fk_roombill_trans FOREIGN KEY (TransactionID) REFERENCES DORM_TRANSACTION(TransactionID);
ALTER TABLE MENU_BILLING ADD CONSTRAINT fk_menubill_trans FOREIGN KEY (TransactionID) REFERENCES DORM_TRANSACTION(TransactionID);
ALTER TABLE STAFF_TRANSACTION ADD CONSTRAINT fk_stafftrans_trans FOREIGN KEY (TransactionID) REFERENCES DORM_TRANSACTION(TransactionID);
ALTER TABLE STUDENT_TRANSACTION ADD CONSTRAINT fk_studenttrans_trans FOREIGN KEY (TransactionID) REFERENCES DORM_TRANSACTION(TransactionID) ON DELETE CASCADE;

-- Other Relationships
ALTER TABLE ROOM_BILLING ADD CONSTRAINT fk_roombill_room FOREIGN KEY (RoomID) REFERENCES ROOM(RoomID) ON DELETE CASCADE;
ALTER TABLE MENU_BILLING ADD CONSTRAINT fk_menubill_menu FOREIGN KEY (MenuID) REFERENCES MENU(MenuID) ON DELETE CASCADE;
ALTER TABLE STAFF_TRANSACTION ADD CONSTRAINT fk_stafftrans_staff FOREIGN KEY(StaffID) REFERENCES STAFF(StaffID) ON DELETE CASCADE;
ALTER TABLE STUDENT_TRANSACTION ADD CONSTRAINT fk_studenttrans_student FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID);
ALTER TABLE STUDENT_PHONE ADD CONSTRAINT fk_phone_student FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID);

ALTER TABLE STUDENT_VISITOR ADD CONSTRAINT fk_sv_visitor FOREIGN KEY (VisitorID) REFERENCES VISITOR(VisitorID) ON DELETE CASCADE;
ALTER TABLE STUDENT_VISITOR ADD CONSTRAINT fk_sv_student FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID) ON DELETE CASCADE;

-- TASK Relations
ALTER TABLE TASK ADD CONSTRAINT fk_task_room FOREIGN KEY (RoomID) REFERENCES ROOM(RoomID) ON DELETE CASCADE;

ALTER TABLE TASK_STAFF ADD (
    CONSTRAINT fk_taskstaff_task FOREIGN KEY (TaskID) REFERENCES TASK(TaskID) ON DELETE CASCADE,
    CONSTRAINT fk_taskstaff_staff FOREIGN KEY (StaffID) REFERENCES STAFF(StaffID) ON DELETE CASCADE
);

-- SUPPLY Relations
ALTER TABLE MENU_SUPPLY ADD(
    CONSTRAINT fk_menusupply_menu FOREIGN KEY (MenuID) REFERENCES MENU(MenuID) ON DELETE CASCADE,
    CONSTRAINT fk_menusupply_supply FOREIGN KEY (SupplyID) REFERENCES SUPPLY(SupplyID) ON DELETE CASCADE
);

ALTER TABLE TASK_SUPPLY ADD (
    CONSTRAINT fk_tasksupply_task FOREIGN KEY (TaskID) REFERENCES TASK(TaskID) ON DELETE CASCADE,
    CONSTRAINT fk_tasksupply_supply FOREIGN KEY (SupplyID) REFERENCES SUPPLY(SupplyID) ON DELETE CASCADE
);

-- ==========================================
-- 4. CHECK CONSTRAINTS
-- ==========================================

ALTER TABLE DORM_TRANSACTION ADD CONSTRAINT check_transaction_flow CHECK (FlowType IN ('Income', 'Expense'));
ALTER TABLE ROOM_BILLING ADD CONSTRAINT check_months_stayed CHECK (MonthsToStay BETWEEN 1 AND 12);
ALTER TABLE MENU_BILLING ADD CONSTRAINT chk_portion_limit CHECK (PortionBought BETWEEN 1 AND 2);
ALTER TABLE STAFF_TRANSACTION ADD CONSTRAINT chk_payment_month CHECK (PaymentMonth IN ('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'));
ALTER TABLE STUDENT_TRANSACTION ADD CONSTRAINT chk_payment_method CHECK (PaymentMethod IN ('Cash', 'Credit Card','Bank Transfer'));
ALTER TABLE ROOM_ASSIGNMENT ADD CONSTRAINT check_assignment_status CHECK (AssignmentStatus IN ('Current', 'Past'));
ALTER TABLE STAFF ADD CONSTRAINT chk_staff_role CHECK (Role IN ('Cleaner', 'Maintenance', 'Security', 'Manager', 'Kitchen Staff'));
ALTER TABLE STAFF ADD CONSTRAINT chk_staff_salary CHECK (Salary > 0);
ALTER TABLE TASK ADD CONSTRAINT chk_task_type CHECK (TaskType IN ('Cleaning', 'Repair', 'Plumbing', 'Electric', 'Maintenance', 'Kitchen'));
ALTER TABLE ROOM ADD (
    CONSTRAINT check_room_type CHECK (RoomType IN ('Standard', 'Accessible')),
    CONSTRAINT check_occupancy_status CHECK (OccupancyStatus IN ('Available', 'Full'))
);
ALTER TABLE SUPPLY ADD (
    CONSTRAINT check_supply_amount CHECK (StockAmount >= 0),
    CONSTRAINT check_supply_price CHECK (PricePerUnit >= 0)
);
ALTER TABLE MENU_SUPPLY ADD CONSTRAINT check_ms_quantity_used CHECK (QuantityUsed > 0);
ALTER TABLE TASK_SUPPLY ADD CONSTRAINT check_ts_quantity_used CHECK (QuantityUsed > 0);
ALTER TABLE MENU ADD CONSTRAINT check_menu_price CHECK (Price >= 0);
ALTER TABLE MENU ADD CONSTRAINT check_meal_type CHECK (MealType IN ('Breakfast', 'Lunch', 'Dinner'));

ALTER TABLE VISITOR ADD CONSTRAINT check_visitor_dates CHECK (ExitDate >= EntryDate);
-- ==========================================
-- 5. INSERT DATA
-- ==========================================

-- 1. MENU TABLE
INSERT INTO MENU VALUES (1, 'Breakfast', 45.00, TO_DATE('2025-10-01', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (2, 'Lunch', 85.50, TO_DATE('2025-10-01', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (3, 'Dinner', 60.00, TO_DATE('2025-10-01', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (4, 'Lunch', 110.00, TO_DATE('2025-10-02', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (5, 'Dinner', 120.00, TO_DATE('2025-10-02', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (6, 'Lunch', 30.00, TO_DATE('2025-10-03', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (7, 'Dinner', 55.00, TO_DATE('2025-10-03', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (8, 'Breakfast', 40.00, TO_DATE('2025-10-04', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (9, 'Lunch', 50.00, TO_DATE('2025-10-04', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (10, 'Dinner', 75.00, TO_DATE('2025-10-05', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (11, 'Lunch', 70.00, TO_DATE('2025-10-05', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (12, 'Dinner', 130.00, TO_DATE('2025-10-06', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (13, 'Lunch', 90.00, TO_DATE('2025-10-06', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (14, 'Breakfast', 55.00, TO_DATE('2025-10-07', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (15, 'Dinner', 80.00, TO_DATE('2025-10-07', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (16, 'Lunch', 95.00, TO_DATE('2025-10-08', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (17, 'Dinner', 45.00, TO_DATE('2025-10-08', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (18, 'Dinner', 85.00, TO_DATE('2025-10-09', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (19, 'Dinner', 150.00, TO_DATE('2025-10-09', 'YYYY-MM-DD'));
INSERT INTO MENU VALUES (20, 'Lunch', 80.00, TO_DATE('2025-10-10', 'YYYY-MM-DD'));

-- 2. ROOM TABLE
INSERT INTO ROOM VALUES (101, 'A', 1, 2, 0, 'Available', 'Standard', 2500.00);
INSERT INTO ROOM VALUES (102, 'A', 1, 2, 2, 'Full', 'Standard', 2500.00);
INSERT INTO ROOM VALUES (103, 'A', 1, 1, 0, 'Available', 'Accessible', 3000.00);
INSERT INTO ROOM VALUES (201, 'A', 2, 4, 3, 'Available', 'Standard', 2000.00);
INSERT INTO ROOM VALUES (202, 'A', 2, 4, 4, 'Full', 'Standard', 2000.00);
INSERT INTO ROOM VALUES (301, 'B', 1, 2, 1, 'Available', 'Standard', 2500.00);
INSERT INTO ROOM VALUES (302, 'B', 1, 2, 0, 'Available', 'Standard', 2500.00);
INSERT INTO ROOM VALUES (303, 'B', 1, 1, 1, 'Full', 'Accessible', 3000.00);
INSERT INTO ROOM VALUES (401, 'B', 2, 3, 2, 'Available', 'Standard', 2200.00);
INSERT INTO ROOM VALUES (402, 'B', 2, 3, 3, 'Full', 'Standard', 2200.00);
INSERT INTO ROOM VALUES (501, 'C', 1, 2, 0, 'Available', 'Standard', 2600.00);
INSERT INTO ROOM VALUES (502, 'C', 1, 2, 2, 'Full', 'Standard', 2600.00);
INSERT INTO ROOM VALUES (601, 'C', 2, 4, 1, 'Available', 'Standard', 2100.00);
INSERT INTO ROOM VALUES (602, 'C', 2, 4, 4, 'Full', 'Standard', 2100.00);
INSERT INTO ROOM VALUES (701, 'D', 1, 1, 0, 'Available', 'Accessible', 3200.00);
INSERT INTO ROOM VALUES (702, 'D', 1, 2, 1, 'Available', 'Standard', 2600.00);
INSERT INTO ROOM VALUES (801, 'D', 2, 3, 0, 'Available', 'Standard', 2300.00);
INSERT INTO ROOM VALUES (802, 'D', 2, 3, 3, 'Full', 'Standard', 2300.00);
INSERT INTO ROOM VALUES (901, 'E', 1, 2, 0, 'Available', 'Standard', 2500.00);
INSERT INTO ROOM VALUES (902, 'E', 1, 2, 1, 'Available', 'Standard', 2500.00);

-- 3. STAFF TABLE
INSERT INTO STAFF VALUES (1, 'John', 'Michael', 'Smith', 'Manager', '5551112233', 35000, SYSDATE-365);
INSERT INTO STAFF VALUES (2, 'Emily', NULL, 'Davis', 'Cleaner', '5552223344', 17002, SYSDATE-200);
INSERT INTO STAFF VALUES (3, 'Robert', 'James', 'Wilson', 'Security', '5553334455', 22000, SYSDATE-150);
INSERT INTO STAFF VALUES (4, 'Sarah', NULL, 'Taylor', 'Kitchen Staff', '5554445566', 18000, SYSDATE-100);
INSERT INTO STAFF VALUES (5, 'David', NULL, 'Anderson', 'Maintenance', '5555556677', 25000, SYSDATE-50);
INSERT INTO STAFF VALUES (6, 'Jennifer', NULL, 'Thomas', 'Cleaner', '5556667788', 17002, SYSDATE-300);
INSERT INTO STAFF VALUES (7, 'Daniel', NULL, 'Jackson', 'Security', '5557778899', 22000, SYSDATE-180);
INSERT INTO STAFF VALUES (8, 'Jessica', 'Ann', 'White', 'Kitchen Staff', '5558889900', 18000, SYSDATE-90);
INSERT INTO STAFF VALUES (9, 'Christopher', NULL, 'Harris', 'Maintenance', '5559990011', 25000, SYSDATE-60);
INSERT INTO STAFF VALUES (10, 'Linda', NULL, 'Martin', 'Manager', '5550001122', 32000, SYSDATE-330);
INSERT INTO STAFF VALUES (11, 'Paul', NULL, 'Thompson', 'Cleaner', '5551113344', 17002, SYSDATE-120);
INSERT INTO STAFF VALUES (12, 'Karen', NULL, 'Garcia', 'Security', '5552224455', 22000, SYSDATE-240);
INSERT INTO STAFF VALUES (13, 'Kevin', NULL, 'Martinez', 'Kitchen Staff', '5553335566', 18000, SYSDATE-80);
INSERT INTO STAFF VALUES (14, 'Lisa', NULL, 'Robinson', 'Maintenance', '5554446677', 25000, SYSDATE-40);
INSERT INTO STAFF VALUES (15, 'Mark', NULL, 'Clark', 'Cleaner', '5555557788', 17002, SYSDATE-250);
INSERT INTO STAFF VALUES (16, 'Nancy', NULL, 'Rodriguez', 'Security', '5556668899', 22000, SYSDATE-190);
INSERT INTO STAFF VALUES (17, 'Steven', NULL, 'Lewis', 'Kitchen Staff', '5557779900', 18000, SYSDATE-70);
INSERT INTO STAFF VALUES (18, 'Sandra', NULL, 'Lee', 'Manager', '5558880011', 33000, SYSDATE-310);
INSERT INTO STAFF VALUES (19, 'George', NULL, 'Walker', 'Cleaner', '5559991122', 17002, SYSDATE-140);
INSERT INTO STAFF VALUES (20, 'Betty', NULL, 'Hall', 'Maintenance', '5550002233', 25000, SYSDATE-30);

-- 4. STUDENT TABLE
INSERT INTO STUDENT VALUES (1001, 11111111110, 'Emma', 'Rose', 'Johnson', 'New York', TO_DATE('2002-05-15', 'YYYY-MM-DD'), TO_DATE('2024-09-01', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1002, 11111111112, 'Olivia', 'Grace', 'Williams', 'London', TO_DATE('2003-08-20', 'YYYY-MM-DD'), TO_DATE('2024-09-01', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1003, 11111111114, 'Ava', 'Marie', 'Brown', 'Paris', TO_DATE('2001-02-10', 'YYYY-MM-DD'), TO_DATE('2023-09-01', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1004, 11111111116, 'Isabella', 'Jane', 'Jones', 'Berlin', TO_DATE('2002-11-05', 'YYYY-MM-DD'), TO_DATE('2024-09-02', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1005, 11111111118, 'Sophia', 'Lynn', 'Garcia', 'Madrid', TO_DATE('2003-04-12', 'YYYY-MM-DD'), TO_DATE('2024-09-03', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1006, 11111111120, 'Mia', 'Elizabeth', 'Miller', 'Rome', TO_DATE('2002-09-25', 'YYYY-MM-DD'), TO_DATE('2024-09-04', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1007, 11111111122, 'Charlotte', 'Ann', 'Davis', 'Toronto', TO_DATE('2001-12-30', 'YYYY-MM-DD'), TO_DATE('2023-09-05', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1008, 11111111124, 'Amelia', 'Kate', 'Rodriguez', 'Sydney', TO_DATE('2003-06-18', 'YYYY-MM-DD'), TO_DATE('2024-09-05', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1009, 11111111126, 'Evelyn', 'Claire', 'Martinez', 'Tokyo', TO_DATE('2002-01-22', 'YYYY-MM-DD'), TO_DATE('2024-09-06', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1010, 11111111128, 'Abigail', 'Faith', 'Hernandez', 'Dublin', TO_DATE('2003-10-08', 'YYYY-MM-DD'), TO_DATE('2024-09-07', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1011, 11111111130, 'Harper', 'Sue', 'Lopez', 'Amsterdam', TO_DATE('2001-07-14', 'YYYY-MM-DD'), TO_DATE('2023-09-08', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1012, 11111111132, 'Emily', 'Rose', 'Gonzalez', 'Vienna', TO_DATE('2002-03-03', 'YYYY-MM-DD'), TO_DATE('2024-09-09', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1013, 11111111134, 'Elizabeth', 'Hope', 'Wilson', 'Prague', TO_DATE('2003-11-28', 'YYYY-MM-DD'), TO_DATE('2024-09-10', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1014, 11111111136, 'Sofia', 'Grace', 'Anderson', 'Lisbon', TO_DATE('2002-05-09', 'YYYY-MM-DD'), TO_DATE('2024-09-11', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1015, 11111111138, 'Avery', 'May', 'Thomas', 'Brussels', TO_DATE('2001-08-16', 'YYYY-MM-DD'), TO_DATE('2023-09-12', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1016, 11111111140, 'Ella', 'Joy', 'Taylor', 'Oslo', TO_DATE('2003-02-24', 'YYYY-MM-DD'), TO_DATE('2024-09-13', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1017, 11111111142, 'Madison', 'Lee', 'Moore', 'Stockholm', TO_DATE('2002-12-01', 'YYYY-MM-DD'), TO_DATE('2024-09-14', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1018, 11111111144, 'Scarlett', 'Rae', 'Jackson', 'Helsinki', TO_DATE('2003-09-19', 'YYYY-MM-DD'), TO_DATE('2024-09-15', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1019, 11111111146, 'Victoria', 'Skye', 'Martin', 'Copenhagen', TO_DATE('2001-04-27', 'YYYY-MM-DD'), TO_DATE('2023-09-16', 'YYYY-MM-DD'));
INSERT INTO STUDENT VALUES (1020, 11111111148, 'Luna', 'Belle', 'Lee', 'Athens', TO_DATE('2002-10-11', 'YYYY-MM-DD'), TO_DATE('2024-09-17', 'YYYY-MM-DD'));

-- 5. SUPPLY TABLE
INSERT INTO SUPPLY VALUES (1, 'Liquid Soap', 100, 'Liter', 50.00);
INSERT INTO SUPPLY VALUES (2, 'Toilet Paper', 500, 'Roll', 15.00);
INSERT INTO SUPPLY VALUES (3, 'Bleach', 50, 'Liter', 40.00);
INSERT INTO SUPPLY VALUES (4, 'Surface Cleaner', 60, 'Liter', 35.00);
INSERT INTO SUPPLY VALUES (5, 'Light Bulb', 100, 'Piece', 25.00);
INSERT INTO SUPPLY VALUES (6, 'Faucet Gasket', 200, 'Piece', 5.00);
INSERT INTO SUPPLY VALUES (7, 'Rice', 50, 'Kg', 45.00);
INSERT INTO SUPPLY VALUES (8, 'Lentil', 40, 'Kg', 35.00);
INSERT INTO SUPPLY VALUES (9, 'Pasta', 100, 'Packet', 12.00);
INSERT INTO SUPPLY VALUES (10, 'Tomato Paste', 30, 'Jar', 60.00);
INSERT INTO SUPPLY VALUES (11, 'Cooking Oil', 50, 'Liter', 80.00);
INSERT INTO SUPPLY VALUES (12, 'Flour', 40, 'Kg', 20.00);
INSERT INTO SUPPLY VALUES (13, 'Sugar', 30, 'Kg', 25.00);
INSERT INTO SUPPLY VALUES (14, 'Tea', 20, 'Kg', 150.00);
INSERT INTO SUPPLY VALUES (15, 'Salt', 50, 'Packet', 10.00);
INSERT INTO SUPPLY VALUES (16, 'Dish Soap', 40, 'Liter', 45.00);
INSERT INTO SUPPLY VALUES (17, 'Sponge', 100, 'Piece', 5.00);
INSERT INTO SUPPLY VALUES (18, 'Trash Bag', 200, 'Roll', 20.00);
INSERT INTO SUPPLY VALUES (19, 'Gloves', 100, 'Box', 70.00);
INSERT INTO SUPPLY VALUES (20, 'Mop Head', 30, 'Piece', 40.00);

-- 6. TASK TABLE
INSERT INTO Task VALUES (1, 'Cleaning', SYSDATE-5, SYSDATE-5, 'Room cleaning completed', 101);
INSERT INTO Task VALUES (2, 'Repair', SYSDATE-10, SYSDATE-9, 'Faucet repair', 102);
INSERT INTO Task VALUES (3, 'Maintenance', SYSDATE-2, NULL, 'AC maintenance', 103);
INSERT INTO Task VALUES (4, 'Cleaning', SYSDATE-1, SYSDATE-1, 'General cleaning', 201);
INSERT INTO Task VALUES (5, 'Electric', SYSDATE-15, SYSDATE-14, 'Socket replacement', 202);
INSERT INTO Task VALUES (6, 'Plumbing', SYSDATE-20, SYSDATE-19, 'Drain unblocking', 301);
INSERT INTO Task VALUES (7, 'Cleaning', SYSDATE-3, SYSDATE-3, 'Periodic cleaning', 302);
INSERT INTO Task VALUES (8, 'Repair', SYSDATE-7, NULL, 'Cabinet door repair', 303);
INSERT INTO Task VALUES (9, 'Maintenance', SYSDATE-12, SYSDATE-11, 'Paint check', 401);
INSERT INTO Task VALUES (10, 'Cleaning', SYSDATE-4, SYSDATE-4, 'Floor cleaning', 402);
INSERT INTO Task VALUES (11, 'Electric', SYSDATE-8, SYSDATE-8, 'Light bulb replacement', 501);
INSERT INTO Task VALUES (12, 'Plumbing', SYSDATE-6, NULL, 'Water leak check', 502);
INSERT INTO Task VALUES (13, 'Cleaning', SYSDATE-2, SYSDATE-2, 'Window cleaning', 601);
INSERT INTO Task VALUES (14, 'Repair', SYSDATE-18, SYSDATE-17, 'Headboard repair', 602);
INSERT INTO Task VALUES (15, 'Maintenance', SYSDATE-25, SYSDATE-24, 'Radiator cleaning', 701);
INSERT INTO Task VALUES (16, 'Cleaning', SYSDATE-1, SYSDATE-1, 'Bathroom cleaning', 702);
INSERT INTO Task VALUES (17, 'Electric', SYSDATE-9, SYSDATE-9, 'Fuse check', 801);
INSERT INTO Task VALUES (18, 'Plumbing', SYSDATE-13, SYSDATE-12, 'Toilet repair', 802);
INSERT INTO Task VALUES (19, 'Cleaning', SYSDATE-5, SYSDATE-5, 'Carpet washing', 901);
INSERT INTO Task VALUES (20, 'Repair', SYSDATE-3, NULL, 'Door handle replacement', 902);

-- 7. ROOM_ASSIGNMENT
INSERT INTO ROOM_ASSIGNMENT VALUES (1001, 101, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1002, 102, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1003, 103, TO_DATE('2023-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1004, 201, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1005, 202, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1006, 301, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1007, 302, TO_DATE('2023-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1008, 303, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1009, 401, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1010, 402, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1011, 501, TO_DATE('2023-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1012, 502, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1013, 601, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1014, 602, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1015, 701, TO_DATE('2023-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1016, 702, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1017, 801, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1018, 802, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1019, 901, TO_DATE('2023-09-15', 'YYYY-MM-DD'), 'Current');
INSERT INTO ROOM_ASSIGNMENT VALUES (1020, 902, TO_DATE('2024-09-15', 'YYYY-MM-DD'), 'Current');

-- 8. STUDENT_PHONE TABLE
INSERT INTO STUDENT_PHONE VALUES (1001, '5321112233');
INSERT INTO STUDENT_PHONE VALUES (1002, '5322223344');
INSERT INTO STUDENT_PHONE VALUES (1003, '5323334455');
INSERT INTO STUDENT_PHONE VALUES (1004, '5324445566');
INSERT INTO STUDENT_PHONE VALUES (1005, '5325556677');
INSERT INTO STUDENT_PHONE VALUES (1006, '5326667788');
INSERT INTO STUDENT_PHONE VALUES (1007, '5327778899');
INSERT INTO STUDENT_PHONE VALUES (1008, '5328889900');
INSERT INTO STUDENT_PHONE VALUES (1009, '5329990011');
INSERT INTO STUDENT_PHONE VALUES (1010, '5320001122');
INSERT INTO STUDENT_PHONE VALUES (1011, '5331112233');
INSERT INTO STUDENT_PHONE VALUES (1012, '5332223344');
INSERT INTO STUDENT_PHONE VALUES (1013, '5333334455');
INSERT INTO STUDENT_PHONE VALUES (1014, '5334445566');
INSERT INTO STUDENT_PHONE VALUES (1015, '5335556677');
INSERT INTO STUDENT_PHONE VALUES (1016, '5336667788');
INSERT INTO STUDENT_PHONE VALUES (1017, '5337778899');
INSERT INTO STUDENT_PHONE VALUES (1018, '5338889900');
INSERT INTO STUDENT_PHONE VALUES (1019, '5339990011');
INSERT INTO STUDENT_PHONE VALUES (1020, '5330001122');


-- 9. DORM_TRANSACTION TABLE
INSERT INTO DORM_TRANSACTION VALUES (101, SYSDATE-5, 2500.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (102, SYSDATE-5, 2500.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (103, SYSDATE-5, 3000.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (104, SYSDATE-5, 2000.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (105, SYSDATE-5, 2000.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (106, SYSDATE-5, 2500.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (107, SYSDATE-5, 2500.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (108, SYSDATE-5, 3000.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (109, SYSDATE-5, 2200.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (110, SYSDATE-5, 2200.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (111, SYSDATE-5, 2600.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (112, SYSDATE-5, 2600.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (113, SYSDATE-5, 2100.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (114, SYSDATE-5, 2100.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (115, SYSDATE-5, 3200.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (116, SYSDATE-5, 2600.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (117, SYSDATE-5, 2300.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (118, SYSDATE-5, 2300.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (119, SYSDATE-5, 2500.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (120, SYSDATE-5, 2500.00, 'Income');

-- Food Payments
INSERT INTO DORM_TRANSACTION VALUES (201, SYSDATE, 45.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (202, SYSDATE, 85.50, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (203, SYSDATE, 60.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (204, SYSDATE, 110.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (205, SYSDATE, 120.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (206, SYSDATE, 30.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (207, SYSDATE, 55.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (208, SYSDATE, 40.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (209, SYSDATE, 50.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (210, SYSDATE, 75.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (211, SYSDATE, 70.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (212, SYSDATE, 130.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (213, SYSDATE, 90.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (214, SYSDATE, 55.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (215, SYSDATE, 80.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (216, SYSDATE, 95.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (217, SYSDATE, 45.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (218, SYSDATE, 85.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (219, SYSDATE, 150.00, 'Income');
INSERT INTO DORM_TRANSACTION VALUES (220, SYSDATE, 80.00, 'Income');

-- Staff Salary Payments
INSERT INTO DORM_TRANSACTION VALUES (301, SYSDATE, 35000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (302, SYSDATE, 17002, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (303, SYSDATE, 22000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (304, SYSDATE, 18000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (305, SYSDATE, 25000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (306, SYSDATE, 17002, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (307, SYSDATE, 22000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (308, SYSDATE, 18000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (309, SYSDATE, 25000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (310, SYSDATE, 32000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (311, SYSDATE, 17002, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (312, SYSDATE, 22000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (313, SYSDATE, 18000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (314, SYSDATE, 25000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (315, SYSDATE, 17002, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (316, SYSDATE, 22000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (317, SYSDATE, 18000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (318, SYSDATE, 33000, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (319, SYSDATE, 17002, 'Expense');
INSERT INTO DORM_TRANSACTION VALUES (320, SYSDATE, 25000, 'Expense');


-- 10. ROOM_BILLING
INSERT INTO ROOM_BILLING VALUES (101, 101, 1001, 1);
INSERT INTO ROOM_BILLING VALUES (102, 102, 1002, 1);
INSERT INTO ROOM_BILLING VALUES (103, 103, 1003, 1);
INSERT INTO ROOM_BILLING VALUES (104, 201, 1004, 1);
INSERT INTO ROOM_BILLING VALUES (105, 202, 1005, 1);
INSERT INTO ROOM_BILLING VALUES (106, 301, 1006, 1);
INSERT INTO ROOM_BILLING VALUES (107, 302, 1007, 1);
INSERT INTO ROOM_BILLING VALUES (108, 303, 1008, 1);
INSERT INTO ROOM_BILLING VALUES (109, 401, 1009, 1);
INSERT INTO ROOM_BILLING VALUES (110, 402, 1010, 1);
INSERT INTO ROOM_BILLING VALUES (111, 501, 1011, 1);
INSERT INTO ROOM_BILLING VALUES (112, 502, 1012, 1);
INSERT INTO ROOM_BILLING VALUES (113, 601, 1013, 1);
INSERT INTO ROOM_BILLING VALUES (114, 602, 1014, 1);
INSERT INTO ROOM_BILLING VALUES (115, 701, 1015, 1);
INSERT INTO ROOM_BILLING VALUES (116, 702, 1016, 1);
INSERT INTO ROOM_BILLING VALUES (117, 801, 1017, 1);
INSERT INTO ROOM_BILLING VALUES (118, 802, 1018, 1);
INSERT INTO ROOM_BILLING VALUES (119, 901, 1019, 1);
INSERT INTO ROOM_BILLING VALUES (120, 902, 1020, 1);


-- 11. MENU_BILLING
INSERT INTO MENU_BILLING VALUES (201, 1, 1);
INSERT INTO MENU_BILLING VALUES (202, 2, 1);
INSERT INTO MENU_BILLING VALUES (203, 3, 1);
INSERT INTO MENU_BILLING VALUES (204, 4, 1);
INSERT INTO MENU_BILLING VALUES (205, 5, 1);
INSERT INTO MENU_BILLING VALUES (206, 6, 1);
INSERT INTO MENU_BILLING VALUES (207, 7, 1);
INSERT INTO MENU_BILLING VALUES (208, 8, 1);
INSERT INTO MENU_BILLING VALUES (209, 9, 1);
INSERT INTO MENU_BILLING VALUES (210, 10, 1);
INSERT INTO MENU_BILLING VALUES (211, 11, 1);
INSERT INTO MENU_BILLING VALUES (212, 12, 1);
INSERT INTO MENU_BILLING VALUES (213, 13, 1);
INSERT INTO MENU_BILLING VALUES (214, 14, 1);
INSERT INTO MENU_BILLING VALUES (215, 15, 1);
INSERT INTO MENU_BILLING VALUES (216, 16, 1);
INSERT INTO MENU_BILLING VALUES (217, 17, 1);
INSERT INTO MENU_BILLING VALUES (218, 18, 1);
INSERT INTO MENU_BILLING VALUES (219, 19, 1);
INSERT INTO MENU_BILLING VALUES (220, 20, 1);

-- 12. STAFF_TRANSACTION
INSERT INTO STAFF_TRANSACTION VALUES (301, 1, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (302, 2, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (303, 3, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (304, 4, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (305, 5, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (306, 6, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (307, 7, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (308, 8, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (309, 9, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (310, 10, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (311, 11, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (312, 12, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (313, 13, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (314, 14, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (315, 15, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (316, 16, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (317, 17, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (318, 18, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (319, 19, 'October');
INSERT INTO STAFF_TRANSACTION VALUES (320, 20, 'October');


-- 13. STUDENT_TRANSACTION
INSERT INTO STUDENT_TRANSACTION VALUES (101, 1001, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (102, 1002, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (103, 1003, 'Bank Transfer');
INSERT INTO STUDENT_TRANSACTION VALUES (104, 1004, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (105, 1005, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (106, 1006, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (107, 1007, 'Bank Transfer');
INSERT INTO STUDENT_TRANSACTION VALUES (108, 1008, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (109, 1009, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (110, 1010, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (111, 1011, 'Bank Transfer');
INSERT INTO STUDENT_TRANSACTION VALUES (112, 1012, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (113, 1013, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (114, 1014, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (115, 1015, 'Bank Transfer');
INSERT INTO STUDENT_TRANSACTION VALUES (116, 1016, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (117, 1017, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (118, 1018, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (119, 1019, 'Bank Transfer');
INSERT INTO STUDENT_TRANSACTION VALUES (120, 1020, 'Credit Card');

INSERT INTO STUDENT_TRANSACTION VALUES (201, 1001, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (202, 1002, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (203, 1003, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (204, 1004, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (205, 1005, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (206, 1006, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (207, 1007, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (208, 1008, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (209, 1009, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (210, 1010, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (211, 1011, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (212, 1012, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (213, 1013, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (214, 1014, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (215, 1015, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (216, 1016, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (217, 1017, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (218, 1018, 'Credit Card');
INSERT INTO STUDENT_TRANSACTION VALUES (219, 1019, 'Cash');
INSERT INTO STUDENT_TRANSACTION VALUES (220, 1020, 'Credit Card');


-- 14. MENU_SUPPLY
INSERT INTO MENU_SUPPLY VALUES (1, 14, 2); 
INSERT INTO MENU_SUPPLY VALUES (1, 13, 1); 
INSERT INTO MENU_SUPPLY VALUES (2, 11, 1); 
INSERT INTO MENU_SUPPLY VALUES (3, 7, 2); 
INSERT INTO MENU_SUPPLY VALUES (4, 11, 1); 
INSERT INTO MENU_SUPPLY VALUES (5, 11, 1);
INSERT INTO MENU_SUPPLY VALUES (6, 8, 2); 
INSERT INTO MENU_SUPPLY VALUES (7, 9, 1); 
INSERT INTO MENU_SUPPLY VALUES (8, 15, 1); 
INSERT INTO MENU_SUPPLY VALUES (9, 12, 2); 
INSERT INTO MENU_SUPPLY VALUES (10, 10, 1);
INSERT INTO MENU_SUPPLY VALUES (11, 11, 1);
INSERT INTO MENU_SUPPLY VALUES (12, 11, 1);
INSERT INTO MENU_SUPPLY VALUES (13, 12, 2); 
INSERT INTO MENU_SUPPLY VALUES (14, 11, 1); 
INSERT INTO MENU_SUPPLY VALUES (15, 12, 1); 
INSERT INTO MENU_SUPPLY VALUES (16, 12, 1);
INSERT INTO MENU_SUPPLY VALUES (17, 11, 1); 
INSERT INTO MENU_SUPPLY VALUES (18, 11, 2);
INSERT INTO MENU_SUPPLY VALUES (19, 15, 1);
INSERT INTO MENU_SUPPLY VALUES (20, 7, 1);

-- 15. TASK_STAFF
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (1, 2, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (2, 5, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (3, 9, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (4, 6, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (5, 5, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (6, 14, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (7, 11, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (8, 9, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (9, 14, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (10, 15, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (11, 5, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (12, 20, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (13, 19, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (14, 14, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (15, 20, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (16, 2, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (17, 5, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (18, 9, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (19, 6, SYSDATE);
INSERT INTO TASK_STAFF (TaskID, StaffID, AssignedDate) VALUES (20, 20, SYSDATE);

-- 16. TASK_SUPPLY
INSERT INTO TASK_SUPPLY VALUES (1, 1, 1); 
INSERT INTO TASK_SUPPLY VALUES (2, 6, 1); 
INSERT INTO TASK_SUPPLY VALUES (4, 3, 2); 
INSERT INTO TASK_SUPPLY VALUES (5, 5, 1); 
INSERT INTO TASK_SUPPLY VALUES (6, 19, 2);
INSERT INTO TASK_SUPPLY VALUES (7, 4, 1); 
INSERT INTO TASK_SUPPLY VALUES (10, 20, 1);
INSERT INTO TASK_SUPPLY VALUES (11, 5, 2); 
INSERT INTO TASK_SUPPLY VALUES (13, 17, 2);
INSERT INTO TASK_SUPPLY VALUES (15, 19, 1);
INSERT INTO TASK_SUPPLY VALUES (16, 3, 1); 
INSERT INTO TASK_SUPPLY VALUES (17, 5, 1); 
INSERT INTO TASK_SUPPLY VALUES (18, 6, 1); 
INSERT INTO TASK_SUPPLY VALUES (19, 4, 2); 
INSERT INTO TASK_SUPPLY VALUES (1, 18, 1); 
INSERT INTO TASK_SUPPLY VALUES (4, 18, 1);
INSERT INTO TASK_SUPPLY VALUES (7, 18, 1);
INSERT INTO TASK_SUPPLY VALUES (10, 18, 1);
INSERT INTO TASK_SUPPLY VALUES (13, 18, 1);
INSERT INTO TASK_SUPPLY VALUES (16, 18, 1);

-- ==========================================
-- MISSING INSERTS (PERSON 4: VISITOR MANAGEMENT)
-- ==========================================

-- 17. VISITOR TABLE
INSERT INTO VISITOR VALUES (1, 'Michael', 'John', 'Johnson', TO_DATE('2025-10-01 10:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-01 14:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (2, 'Susan', NULL, 'Williams', TO_DATE('2025-10-02 09:30', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-02 11:30', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (3, 'James', NULL, 'Brown', TO_DATE('2025-10-02 15:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-02 18:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (4, 'Linda', 'May', 'Jones', TO_DATE('2025-10-03 12:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-03 16:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (5, 'David', NULL, 'Garcia', TO_DATE('2025-10-04 10:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-04 12:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (6, 'Patricia', NULL, 'Miller', TO_DATE('2025-10-05 14:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-05 17:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (7, 'John', 'Robert', 'Davis', TO_DATE('2025-10-06 09:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-06 10:30', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (8, 'Jennifer', NULL, 'Rodriguez', TO_DATE('2025-10-06 16:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-06 19:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (9, 'William', NULL, 'Martinez', TO_DATE('2025-10-07 11:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-07 13:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (10, 'Elizabeth', NULL, 'Hernandez', TO_DATE('2025-10-08 13:30', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-08 15:30', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (11, 'Richard', NULL, 'Lopez', TO_DATE('2025-10-09 10:00', 'YYYY-MM-DD HH24:MI'), NULL); 
INSERT INTO VISITOR VALUES (12, 'Barbara', NULL, 'Gonzalez', TO_DATE('2025-10-09 14:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-09 16:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (13, 'Joseph', 'Lee', 'Wilson', TO_DATE('2025-10-10 09:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-10 11:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (14, 'Margaret', NULL, 'Anderson', TO_DATE('2025-10-10 15:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-10 17:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (15, 'Thomas', NULL, 'Thomas', TO_DATE('2025-10-11 12:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-11 14:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (16, 'Nancy', 'Jean', 'Taylor', TO_DATE('2025-10-12 10:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-12 18:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (17, 'Charles', NULL, 'Moore', TO_DATE('2025-10-13 09:30', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-13 10:30', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (18, 'Lisa', NULL, 'Jackson', TO_DATE('2025-10-14 14:00', 'YYYY-MM-DD HH24:MI'), NULL); 
INSERT INTO VISITOR VALUES (19, 'Christopher', 'Ray', 'Martin', TO_DATE('2025-10-15 11:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-15 13:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO VISITOR VALUES (20, 'Karen', NULL, 'Lee', TO_DATE('2025-10-16 16:00', 'YYYY-MM-DD HH24:MI'), TO_DATE('2025-10-16 17:30', 'YYYY-MM-DD HH24:MI'));

-- 18. STUDENT_VISITOR TABLE
INSERT INTO STUDENT_VISITOR VALUES (1, 1001); 
INSERT INTO STUDENT_VISITOR VALUES (2, 1002); 
INSERT INTO STUDENT_VISITOR VALUES (3, 1003); 
INSERT INTO STUDENT_VISITOR VALUES (4, 1004); 
INSERT INTO STUDENT_VISITOR VALUES (5, 1005); 
INSERT INTO STUDENT_VISITOR VALUES (6, 1006); 
INSERT INTO STUDENT_VISITOR VALUES (7, 1007); 
INSERT INTO STUDENT_VISITOR VALUES (8, 1008); 
INSERT INTO STUDENT_VISITOR VALUES (9, 1009); 
INSERT INTO STUDENT_VISITOR VALUES (10, 1010);
INSERT INTO STUDENT_VISITOR VALUES (11, 1011);
INSERT INTO STUDENT_VISITOR VALUES (12, 1012);
INSERT INTO STUDENT_VISITOR VALUES (13, 1013);
INSERT INTO STUDENT_VISITOR VALUES (14, 1014);
INSERT INTO STUDENT_VISITOR VALUES (15, 1015);
INSERT INTO STUDENT_VISITOR VALUES (16, 1016);
INSERT INTO STUDENT_VISITOR VALUES (17, 1017);
INSERT INTO STUDENT_VISITOR VALUES (18, 1018);
INSERT INTO STUDENT_VISITOR VALUES (19, 1019);
INSERT INTO STUDENT_VISITOR VALUES (20, 1020);

-- ==========================================
-- 6. TRIGGERS (ADD AT THE VERY END)
-- ==========================================

-- Trigger 1: Checks if the room is available before assignment
CREATE OR REPLACE TRIGGER trg_room_assignment_validator
BEFORE INSERT OR UPDATE OF RoomID, AssignmentStatus ON ROOM_ASSIGNMENT
FOR EACH ROW
DECLARE
    v_curr NUMBER;
    v_max  NUMBER;
BEGIN
    -- Only check capacity if the student is currently occupying the room
    IF :NEW.AssignmentStatus = 'Current' THEN
        -- Check capacity if it's a new assignment or the student is moving to a different room
        IF INSERTING OR (:NEW.RoomID <> NVL(:OLD.RoomID, -1)) THEN
            SELECT CurrentCapacity, MaxCapacity 
            INTO v_curr, v_max
            FROM ROOM 
            WHERE RoomID = :NEW.RoomID;

            IF v_curr >= v_max THEN
                RAISE_APPLICATION_ERROR(-20001, 'Capacity Error: Room ' || :NEW.RoomID || ' is full.');
            END IF;
        END IF;
    END IF;
END;
/

-- Trigger 2: Handles room capacity and occupancy status sync
CREATE OR REPLACE TRIGGER trg_room_assignment_sync
AFTER INSERT OR UPDATE OF RoomID, AssignmentStatus ON ROOM_ASSIGNMENT
FOR EACH ROW
BEGIN
    -- If the student was in a room and is now leaving or moving
    IF UPDATING AND :OLD.AssignmentStatus = 'Current' THEN
        IF :NEW.AssignmentStatus = 'Past' OR :NEW.RoomID <> :OLD.RoomID THEN
            UPDATE ROOM 
            SET CurrentCapacity = CurrentCapacity - 1,
                OccupancyStatus = 'Available'
            WHERE RoomID = :OLD.RoomID;
        END IF;
    END IF;

    -- If the student is joining a room
    IF :NEW.AssignmentStatus = 'Current' THEN
        IF INSERTING OR (UPDATING AND :NEW.RoomID <> NVL(:OLD.RoomID, -1)) THEN
            -- Increase capacity
            UPDATE ROOM 
            SET CurrentCapacity = CurrentCapacity + 1
            WHERE RoomID = :NEW.RoomID;

            -- Update status to Full if max capacity reached
            UPDATE ROOM 
            SET OccupancyStatus = 'Full'
            WHERE RoomID = :NEW.RoomID 
            AND CurrentCapacity >= MaxCapacity;
        END IF;
    END IF;
END;
/

-- Trigger 3:-- Automatically deducts stock when a supply is linked to a menu
CREATE OR REPLACE TRIGGER trg_update_stock_menu
AFTER INSERT ON MENU_SUPPLY
FOR EACH ROW
BEGIN
    UPDATE SUPPLY
    SET StockAmount = StockAmount - :NEW.QuantityUsed
    WHERE SupplyID = :NEW.SupplyID;
END;
/

-- Trigger 4:-- Automatically deducts stock when a supply is used for a task
CREATE OR REPLACE TRIGGER trg_update_stock_task
AFTER INSERT ON TASK_SUPPLY
FOR EACH ROW
BEGIN
    UPDATE SUPPLY
    SET StockAmount = StockAmount - :NEW.QuantityUsed
    WHERE SupplyID = :NEW.SupplyID;
END;
/

-- Trigger 5: Prevents stock from dropping below zero
CREATE OR REPLACE TRIGGER trg_supply_inventory_check
BEFORE UPDATE OF StockAmount ON SUPPLY
FOR EACH ROW
BEGIN
    IF :NEW.StockAmount < 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Not enough stock for ' || :OLD.SupplyName);
    END IF;
END;
/