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 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');
