-- ============================================================
-- BABA BHAIRABANANDA AUTONOMOUS MAHAVIDYALAYA
-- B.Voc Tourism & Hospitality Management - Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS bbam_college CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bbam_college;

-- -------------------------------------------------------
-- STUDENTS TABLE (Admission Register)
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS students (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    sl_no           INT NOT NULL,
    session_year    VARCHAR(10) NOT NULL DEFAULT '2023-24',
    roll_no         VARCHAR(20) NOT NULL UNIQUE,
    regd_no         VARCHAR(20) DEFAULT NULL,
    name            VARCHAR(150) NOT NULL,
    date_of_birth   DATE NOT NULL,
    gender          ENUM('Male','Female','Other') NOT NULL,
    category        VARCHAR(30) NOT NULL,
    religion        VARCHAR(50) NOT NULL,
    date_of_admission DATE NOT NULL,
    father_name     VARCHAR(150) NOT NULL,
    mother_name     VARCHAR(150) NOT NULL,
    address         TEXT NOT NULL,
    pin_code        VARCHAR(10) NOT NULL,
    district        VARCHAR(100) NOT NULL,
    aadhaar_no      VARCHAR(20) DEFAULT NULL,
    contact         VARCHAR(15) NOT NULL,
    email_id        VARCHAR(150) DEFAULT NULL,
    photo           VARCHAR(255) DEFAULT NULL,
    status          ENUM('Active','Inactive','Passed Out') DEFAULT 'Active',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- MARKS TABLE (All 6 semesters in one wide table)
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS marks (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    student_id  INT NOT NULL,
    -- 1st Semester
    s1_thm101_ms  TINYINT UNSIGNED DEFAULT 0,
    s1_thm101_es  TINYINT UNSIGNED DEFAULT 0,
    s1_thm102_ms  TINYINT UNSIGNED DEFAULT 0,
    s1_thm102_es  TINYINT UNSIGNED DEFAULT 0,
    s1_thm103_ms  TINYINT UNSIGNED DEFAULT 0,
    s1_thm103_es  TINYINT UNSIGNED DEFAULT 0,
    s1_nsqf4_th   SMALLINT UNSIGNED DEFAULT 0,
    s1_nsqf4_pr   SMALLINT UNSIGNED DEFAULT 0,
    -- 2nd Semester
    s2_thm201_ms  TINYINT UNSIGNED DEFAULT 0,
    s2_thm201_es  TINYINT UNSIGNED DEFAULT 0,
    s2_thm202_ms  TINYINT UNSIGNED DEFAULT 0,
    s2_thm202_es  TINYINT UNSIGNED DEFAULT 0,
    s2_thm203_ms  TINYINT UNSIGNED DEFAULT 0,
    s2_thm203_es  TINYINT UNSIGNED DEFAULT 0,
    s2_nsqf5_th   SMALLINT UNSIGNED DEFAULT 0,
    s2_nsqf5_pr   SMALLINT UNSIGNED DEFAULT 0,
    -- 3rd Semester
    s3_thm301_ms  TINYINT UNSIGNED DEFAULT 0,
    s3_thm301_es  TINYINT UNSIGNED DEFAULT 0,
    s3_thm302_ms  TINYINT UNSIGNED DEFAULT 0,
    s3_thm302_es  TINYINT UNSIGNED DEFAULT 0,
    s3_thm303_ms  TINYINT UNSIGNED DEFAULT 0,
    s3_thm303_es  TINYINT UNSIGNED DEFAULT 0,
    s3_nsqf55_th  SMALLINT UNSIGNED DEFAULT 0,
    s3_nsqf55_pr  SMALLINT UNSIGNED DEFAULT 0,
    -- 4th Semester
    s4_thm401_ms  TINYINT UNSIGNED DEFAULT 0,
    s4_thm401_es  TINYINT UNSIGNED DEFAULT 0,
    s4_thm402_ms  TINYINT UNSIGNED DEFAULT 0,
    s4_thm402_es  TINYINT UNSIGNED DEFAULT 0,
    s4_thm403_ms  TINYINT UNSIGNED DEFAULT 0,
    s4_thm403_es  TINYINT UNSIGNED DEFAULT 0,
    s4_nsqf55_project SMALLINT UNSIGNED DEFAULT 0,
    s4_nsqf55_viva    SMALLINT UNSIGNED DEFAULT 0,
    -- 5th Semester
    s5_thm501_ms  TINYINT UNSIGNED DEFAULT 0,
    s5_thm501_es  TINYINT UNSIGNED DEFAULT 0,
    s5_thm502_ms  TINYINT UNSIGNED DEFAULT 0,
    s5_thm502_es  TINYINT UNSIGNED DEFAULT 0,
    s5_thm503_ms  TINYINT UNSIGNED DEFAULT 0,
    s5_thm503_es  TINYINT UNSIGNED DEFAULT 0,
    s5_nsqf6_th   SMALLINT UNSIGNED DEFAULT 0,
    s5_nsqf6_pr   SMALLINT UNSIGNED DEFAULT 0,
    -- 6th Semester
    s6_thm601_ms  TINYINT UNSIGNED DEFAULT 0,
    s6_thm601_es  TINYINT UNSIGNED DEFAULT 0,
    s6_thm602_ms  TINYINT UNSIGNED DEFAULT 0,
    s6_thm602_es  TINYINT UNSIGNED DEFAULT 0,
    s6_thm603_ms  TINYINT UNSIGNED DEFAULT 0,
    s6_thm603_es  TINYINT UNSIGNED DEFAULT 0,
    s6_nsqf6_project SMALLINT UNSIGNED DEFAULT 0,
    s6_nsqf6_viva    SMALLINT UNSIGNED DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- -------------------------------------------------------
-- SAMPLE DATA
-- -------------------------------------------------------
INSERT INTO students (sl_no,session_year,roll_no,regd_no,name,date_of_birth,gender,category,religion,date_of_admission,father_name,mother_name,address,pin_code,district,aadhaar_no,contact,email_id,status) VALUES
(1,'2023-24','BV-T-23-01','6253/23','SAI MOHAN','2003-05-12','Male','General','Hindu','2023-07-10','MOHAN KUMAR','SITA DEVI','Vill- Chandikhole, PO- Chandikhole','755044','Jajpur','XXXX-XXXX-0001','9876500001','saimohan@example.com','Active'),
(2,'2023-24','BV-T-23-02','6252/23','RUDRA MOHANTY','2003-08-22','Male','OBC','Hindu','2023-07-10','BIMAL MOHANTY','PUJA MOHANTY','Vill- Kakatpur, PO- Kakatpur','754108','Puri','XXXX-XXXX-0002','9876500002','rudra@example.com','Active');

INSERT INTO marks (student_id,s1_thm101_ms,s1_thm101_es,s1_thm102_ms,s1_thm102_es,s1_thm103_ms,s1_thm103_es,s1_nsqf4_th,s1_nsqf4_pr,s2_thm201_ms,s2_thm201_es,s2_thm202_ms,s2_thm202_es,s2_thm203_ms,s2_thm203_es,s2_nsqf5_th,s2_nsqf5_pr,s3_thm301_ms,s3_thm301_es,s3_thm302_ms,s3_thm302_es,s3_thm303_ms,s3_thm303_es,s3_nsqf55_th,s3_nsqf55_pr,s4_thm401_ms,s4_thm401_es,s4_thm402_ms,s4_thm402_es,s4_thm403_ms,s4_thm403_es,s4_nsqf55_project,s4_nsqf55_viva,s5_thm501_ms,s5_thm501_es,s5_thm502_ms,s5_thm502_es,s5_thm503_ms,s5_thm503_es,s5_nsqf6_th,s5_nsqf6_pr,s6_thm601_ms,s6_thm601_es,s6_thm602_ms,s6_thm602_es,s6_thm603_ms,s6_thm603_es,s6_nsqf6_project,s6_nsqf6_viva) VALUES
(1, 7,36,6,30,6,34,89,247, 8,17,7,14,8,24,131,265, 6,29,7,31,7,20,75,200, 7,20,8,13,7,23,52,27, 8,27,7,15,7,15,123,156, 7,13,6,14,6,26,55,26),
(2, 8,33,7,20,7,21,65,261, 7,25,9,19,8,25,70,266, 7,21,7,18,6,22,58,202, 8,19,7,23,8,22,53,29, 7,30,8,25,8,24,102,162, 6,18,7,26,6,26,52,28);