PRAGMA foreign_keys = ON;

CREATE TABLE STUDENT (
    Student_ID INTEGER PRIMARY KEY,
    Student_Name TEXT NOT NULL,
    Email TEXT NOT NULL UNIQUE,
    Program TEXT NOT NULL,
    Year_Level INTEGER CHECK (Year_Level BETWEEN 1 AND 5),
    Status TEXT NOT NULL DEFAULT 'Active'
);

PRAGMA table_info(STUDENT);

CREATE TABLE CLUB (
    Club_ID INTEGER PRIMARY KEY,
    Club_Name TEXT NOT NULL UNIQUE,
    Category TEXT NOT NULL,
    Maximum_Members INTEGER NOT NULL CHECK (Maximum_Members > 0),
    Club_Status TEXT NOT NULL DEFAULT 'Open'
);

CREATE TABLE MEMBERSHIP (
    Membership_ID INTEGER PRIMARY KEY,
    Student_ID INTEGER NOT NULL,
    Club_ID INTEGER NOT NULL,
    Join_Date TEXT NOT NULL,
    Membership_Status TEXT NOT NULL DEFAULT 'Pending',
    UNIQUE (Student_ID, Club_ID),
    FOREIGN KEY (Student_ID) REFERENCES STUDENT(Student_ID),
    FOREIGN KEY (Club_ID) REFERENCES CLUB(Club_ID)
);

PRAGMA table_info(CLUB);
PRAGMA foreign_key_list(MEMBERSHIP);

INSERT INTO STUDENT
(Student_ID, Student_Name, Email, Program, Year_Level)
VALUES (101, 'Ana Cruz', 'ana.cruz@example.com', 'BSIT', 2);

INSERT INTO CLUB
(Club_ID, Club_Name, Category, Maximum_Members)
VALUES (10, 'Tech Innovators', 'Academic', 40);

INSERT INTO MEMBERSHIP
(Membership_ID, Student_ID, Club_ID, Join_Date)
VALUES (1, 101, 10, '2026-10-07');

SELECT * FROM STUDENT;
SELECT * FROM CLUB;
SELECT * FROM MEMBERSHIP;

ALTER TABLE STUDENT
ADD COLUMN Contact_Number TEXT;

ALTER TABLE CLUB
ADD COLUMN Adviser_Name TEXT NOT NULL DEFAULT 'To Be Assigned';

SELECT Club_ID, Club_Name, Adviser_Name FROM CLUB;

PRAGMA table_info(STUDENT);

CREATE TABLE TEMP_CLUB_NOTE (
    Note_ID INTEGER PRIMARY KEY,
    Note_Text TEXT
);

SELECT 'BEFORE DROP';
SELECT name FROM sqlite_master
WHERE type='table' AND name='TEMP_CLUB_NOTE';

DROP TABLE TEMP_CLUB_NOTE;

SELECT 'AFTER DROP';
SELECT name FROM sqlite_master
WHERE type='table' AND name='TEMP_CLUB_NOTE';

CREATE TABLE DEPARTMENT (
    Department_ID INTEGER PRIMARY KEY,
    Department_Name TEXT NOT NULL UNIQUE,
    Building TEXT NOT NULL,
    Office_Floor INTEGER CHECK (Office_Floor BETWEEN 1 AND 10),
    Department_Status TEXT NOT NULL DEFAULT 'Active'
);

ALTER TABLE DEPARTMENT
ADD COLUMN Contact_Email TEXT;

CREATE TABLE TEMP_DEPARTMENT_LOG (
    Log_ID INTEGER PRIMARY KEY,
    Remarks TEXT
);

DROP TABLE TEMP_DEPARTMENT_LOG;

PRAGMA table_info(DEPARTMENT);

select * from student ;
select * from membership;
select * from department;