CREATE DATABASE bystt_db;
USE bystt_db;

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(255) NOT NULL,
    description TEXT,
    duration VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    roll_number VARCHAR(50) UNIQUE NOT NULL,
    passport_number VARCHAR(50) UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE results (
    result_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    grade VARCHAR(10),
    marks DECIMAL(5,2),
    exam_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE exams (
    exam_id INT PRIMARY KEY AUTO_INCREMENT,
    course_id INT,
    exam_date DATE NOT NULL,
    exam_center VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE admit_cards (
    admit_card_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    exam_id INT,
    roll_number VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (exam_id) REFERENCES exams(exam_id)
);

CREATE TABLE certificates (
    certificate_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    certificate_number VARCHAR(50) UNIQUE NOT NULL,
    grade VARCHAR(10),
    issue_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO courses (course_name, description, duration) VALUES
('Diploma in Construction', 'Learn construction techniques and safety protocols.', '6 months'),
('Diploma in Electrical', 'Master electrical systems and wiring.', '6 months'),
('Hotel Management & Chef', 'Training in hospitality and culinary arts.', '1 year'),
('Diploma in Computer Science', 'Fundamentals of programming and IT.', '1 year');

INSERT INTO students (roll_number, full_name, email, phone) VALUES
('ROLL001', 'John Doe', 'john@example.com', '1234567890'),
('ROLL002', 'Jane Smith', 'jane@example.com', '0987654321');

INSERT INTO results (student_id, course_id, grade, marks, exam_date) VALUES
(1, 1, 'A', 85.5, '2025-06-01'),
(2, 2, 'B', 75.0, '2025-06-01');

INSERT INTO exams (course_id, exam_date, exam_center) VALUES
(1, '2025-07-15', 'BYSTT Chittagong Center'),
(2, '2025-07-20', 'BYSTT Dhaka Center');

INSERT INTO admit_cards (student_id, exam_id, roll_number) VALUES
(1, 1, 'ROLL001'),
(2, 2, 'ROLL002');

INSERT INTO certificates (student_id, course_id, certificate_number, grade, issue_date) VALUES
(1, 1, 'BYSTT-CERT-001', 'A', '2025-06-30'),
(2, 2, 'BYSTT-CERT-002', 'B', '2025-06-30');