-- Скрипт инициализации БД для ПМ-11
CREATE SCHEMA IF NOT EXISTS gaukov_dp;

CREATE TABLE IF NOT EXISTS gaukov_dp.users (
    login VARCHAR(50) PRIMARY KEY,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS gaukov_dp.groups (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    year INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS gaukov_dp.students (
    record_book_id VARCHAR(20) PRIMARY KEY,
    last_name VARCHAR(100) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    group_id INTEGER REFERENCES gaukov_dp.groups(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS gaukov_dp.teachers (
    id SERIAL PRIMARY KEY,
    last_name VARCHAR(100) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS gaukov_dp.subjects (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL UNIQUE,
    hours INTEGER
);

CREATE TABLE IF NOT EXISTS gaukov_dp.grades (
    id SERIAL PRIMARY KEY,
    record_book_id VARCHAR(20) REFERENCES gaukov_dp.students(record_book_id) ON DELETE CASCADE,
    subject_id INTEGER REFERENCES gaukov_dp.subjects(id) ON DELETE CASCADE,
    grade INTEGER NOT NULL CHECK (grade BETWEEN 2 AND 5),
    date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE IF NOT EXISTS gaukov_dp.login_logs (
    id SERIAL PRIMARY KEY,
    user_role VARCHAR(20),
    status VARCHAR(50),
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO gaukov_dp.users (login, password, role) VALUES 
('admin', 'admin123', 'admin'),
('teacher1', 'teacher123', 'teacher'),
('student1', 'student123', 'student')
ON CONFLICT (login) DO NOTHING;