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

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    code VARCHAR(50) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE monographs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    abstract TEXT NULL,
    keywords VARCHAR(255) NULL,
    subject_area VARCHAR(100) NULL,
    session_year YEAR NOT NULL,
    student_id INT NOT NULL,
    advisor_id INT NULL,
    status ENUM('pending','observed','approved','archived','rejected') DEFAULT 'pending',
    visibility ENUM('private','internal','authorized') DEFAULT 'private',
    download_policy ENUM('none','authorized_only','internal_roles') DEFAULT 'none',
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type VARCHAR(100) NOT NULL DEFAULT 'application/pdf',
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL DEFAULT NULL,
    approved_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_monographs_student FOREIGN KEY (student_id) REFERENCES users(id),
    CONSTRAINT fk_monographs_advisor FOREIGN KEY (advisor_id) REFERENCES users(id),
    CONSTRAINT fk_monographs_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
);

CREATE TABLE monograph_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    monograph_id INT NOT NULL,
    user_id INT NOT NULL,
    can_view TINYINT(1) DEFAULT 0,
    can_download TINYINT(1) DEFAULT 0,
    granted_by INT NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_perm_monograph FOREIGN KEY (monograph_id) REFERENCES monographs(id) ON DELETE CASCADE,
    CONSTRAINT fk_perm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_perm_granted_by FOREIGN KEY (granted_by) REFERENCES users(id),
    UNIQUE KEY uniq_monograph_user (monograph_id, user_id)
);

CREATE TABLE view_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    monograph_id INT NOT NULL,
    ip_address VARCHAR(45) NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_view_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_view_monograph FOREIGN KEY (monograph_id) REFERENCES monographs(id) ON DELETE CASCADE
);

CREATE TABLE download_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    monograph_id INT NOT NULL,
    ip_address VARCHAR(45) NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_download_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_download_monograph FOREIGN KEY (monograph_id) REFERENCES monographs(id) ON DELETE CASCADE
);

CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(100) NULL,
    record_id INT NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

INSERT INTO roles (name, description) VALUES
('admin', 'Administrador del sistema'),
('coordinador', 'Coordinador IB'),
('asesor', 'Asesor de monografías'),
('estudiante', 'Estudiante autor'),
('lector_autorizado', 'Usuario autorizado para consulta');

INSERT INTO users (role_id, first_name, last_name, email, password, code, status) VALUES
(1, 'Administrador', 'General', 'admin@colegio.edu', '$2y$12$HoGCMBAFvjGCR76m.2b9C.TUOIthCRmY4Ceg/h/vcZGiC34EH/GfC', 'ADMIN001', 'active');