CREATE DATABASE IF NOT EXISTS szkola;
USE szkola;

CREATE TABLE IF NOT EXISTS classes (
    id INT PRIMARY KEY,
    class_name VARCHAR(50) NOT NULL,
    file_path VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    class_id INT NOT NULL,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

CREATE TABLE IF NOT EXISTS roles (
    id INT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS criteria (
    id INT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE IF NOT EXISTS role_criteria (
    role_id INT NOT NULL,
    criteria_id INT NOT NULL,
    PRIMARY KEY (role_id, criteria_id),
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (criteria_id) REFERENCES criteria(id)
);

CREATE TABLE IF NOT EXISTS behavior_records (
    id INT PRIMARY KEY,
    student_id INT NOT NULL,
    criteria_id INT NOT NULL,
    behavior_date DATE NOT NULL,
    is_checked BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (criteria_id) REFERENCES criteria(id)
);

CREATE TABLE IF NOT EXISTS points (
    criteria_id INT NOT NULL,
    points_value INT NOT NULL DEFAULT 0,
    PRIMARY KEY (criteria_id),
    FOREIGN KEY (criteria_id) REFERENCES criteria(id)
);

INSERT INTO classes (id, class_name, file_path) VALUES
(1, 'Class 1A', 'class_1a.json'),
(2, 'Class 2B', 'class_2b.json');

INSERT INTO students (id, first_name, last_name, class_id) VALUES
(1, 'Jan', 'Kowalski', 1),
(2, 'Anna', 'Nowak', 1),
(3, 'Piotr', 'Wiśniewski', 2),
(4, 'Maria', 'Kowalczyk', 2);

INSERT INTO roles (id, role_name) VALUES
(1, 'teacher'),
(2, 'student');

INSERT INTO categories (id, name) VALUES
(1, 'Attendance'),
(2, 'Behavior');

INSERT INTO criteria (id, category_id, name) VALUES
(1, 1, 'Presence'),
(2, 2, 'Good Behavior'),
(3, 2, 'Punctuality');

INSERT INTO role_criteria (role_id, criteria_id) VALUES
(1, 1),
(1, 2), 
(2, 2),
(2, 3);

INSERT INTO behavior_records (id, student_id, criteria_id, behavior_date, is_checked) VALUES
(1, 1, 1, '2024-10-21', TRUE),
(2, 2, 2, '2024-10-21', FALSE),
(3, 3, 1, '2024-10-21', TRUE),
(4, 4, 3, '2024-10-21', TRUE);

INSERT INTO points (criteria_id, points_value) VALUES
(1, 10),
(2, 5),
(3, 3);

SELECT s.first_name, s.last_name, c.class_name, br.behavior_date, cr.name AS criteria_name, br.is_checked
FROM students s
JOIN classes c ON s.class_id = c.id
JOIN behavior_records br ON s.id = br.student_id
JOIN criteria cr ON br.criteria_id = cr.id;