USE project;

CREATE TABLE user (
    user_id VARCHAR(50) PRIMARY KEY,
    fname VARCHAR(50) NOT NULL,
    mname VARCHAR(50),
    lname VARCHAR(50) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,
    school VARCHAR(100) NOT NULL,
    branch VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL
);


CREATE TABLE login (
    username VARCHAR(50) PRIMARY KEY,
    password VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL,
    FOREIGN KEY (username) REFERENCES user(username)
);


CREATE TABLE courses (
    c_id INT PRIMARY KEY,
    c_name VARCHAR(100) NOT NULL,
    school VARCHAR(100) NOT NULL,
    sem VARCHAR(10) NOT NULL,
    description TEXT
);


CREATE TABLE faculty (
    t_id VARCHAR(50),
    c_id INT NOT NULL,
    slot VARCHAR(10) NOT NULL,
    seats INT NOT NULL,
    waiting INT NOT NULL,
    PRIMARY KEY (t_id),
    FOREIGN KEY (c_id) REFERENCES courses(c_id)
);


CREATE TABLE registered (
    user VARCHAR(50) NOT NULL,
    c_id INT NOT NULL,
    t_id VARCHAR(50) NOT NULL,
    slot VARCHAR(10) NOT NULL,
    status VARCHAR(20) NOT NULL,
    PRIMARY KEY (user, c_id, t_id, slot),
    FOREIGN KEY (user) REFERENCES user(username),
    FOREIGN KEY (c_id) REFERENCES courses(c_id),
    FOREIGN KEY (t_id) REFERENCES faculty(t_id)
);



























INSERT INTO user (user_id, fname, mname, lname, username, phone, email, school, branch, type)
VALUES
('abul01', 'Abul', '', 'Hossain', 'abul01', '+1234567890', 'abul@email.com', 'RUET', 'Rajshahi', 'Student'),
('babul02', 'Babul', '', 'Hossain', 'babul02', '+9876543210', 'babul@email.com', 'RUET', 'Rajshahi', 'Student'),
('babu03', 'Babu', '', 'Mia', 'babu03', '+1122334455', 'babu@email.com', 'RUET', 'Rajshahi', 'Student'),
('ali04', 'Khokon', '', 'Ali', 'ali04', '+4433221100', 'ali@email.com', 'RUET', 'Rajshahi', 'Student'),
('gias05', 'Prof. Gias', '', 'Uddin', 'gias05', '+5556667777', 'gias@email.com', 'RUET', 'Rajshahi', 'Faculty');


INSERT INTO login (username, password, type)
VALUES
('abul01', 'hashed_password_1', 'Student'),
('babul02', 'hashed_password_2', 'Student'),
('babu03', 'hashed_password_3', 'Student'),
('ali04', 'hashed_password_4', 'Student'),
('gias05', 'hashed_password_5', 'Faculty');


INSERT INTO courses (c_id, c_name, school, sem, description)
VALUES
(101, 'Computer Networks', 'RUET', '2022 Odd', 'Database Systems'),
(102, 'Operating System', 'RUET', '2022 Odd', 'Core concepts of computer operating systems.'),
(103, 'Data Communication', 'RUET', '2022 Odd', 'Core concepts of computer operating systems.'),
(104, 'Numerical Methods', 'RUET', '2022 Odd', 'Computational techniques for solving mathematical problems.'),
(105, 'Database Systems', 'RUET', '2022 Odd', 'Database management, SQL, and data modeling.');


INSERT INTO faculty (t_id, c_id, slot, seats, waiting)
VALUES
('gias05', 101, 'A1', 50, 10);


INSERT INTO registered (user, c_id, t_id, slot, status)
VALUES
('abul01', 101, 'gias05', 'A1', 'Enrolled'),
('babu03', 102, 'gias05', 'B2', 'Enrolled'),
('babul02', 103, 'gias05', 'C3', 'Enrolled'),
('ali04', 104, 'gias05', 'D1', 'Enrolled'),
('abul01', 105, 'gias05', 'E2', 'Enrolled');