-- SQL dump for projekat_mef with prefixed DB name
CREATE DATABASE IF NOT EXISTS `projip_projekat_mef` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `projip_projekat_mef`;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(30) NULL,
    birth_date DATE NULL,
    role ENUM('administrator', 'rukovodilac', 'izvrsilac') DEFAULT 'izvrsilac',
    active TINYINT(1) DEFAULT 0,
    activation_token VARCHAR(255) NULL,
    reset_token VARCHAR(255) NULL,
    reset_expires DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Example admin (password: admin123)
INSERT INTO users (username, password, name, email, role, active)
VALUES ('admin', 
        '$2y$10$O6H7E2FhXzJTr3mBPR9cGuXOM1nD7S8oDz0t1M0Ezn/Ydn8r4lMTy', 
        'Admin Sistem', 
        'admin@projekat-mef.site', 
        'administrator', 
        1);

CREATE TABLE task_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(191) NOT NULL,
    description TEXT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(191) NOT NULL,
    description TEXT,
    group_id INT,
    manager_id INT NOT NULL,
    due_date DATETIME,
    priority INT DEFAULT 5,
    status ENUM('aktivan', 'zavrsen', 'otkazan') DEFAULT 'aktivan',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES task_groups(id) ON DELETE SET NULL,
    FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE task_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('aktivan', 'zavrsen') DEFAULT 'aktivan',
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE VIEW task_overview AS
SELECT 
    t.id AS task_id,
    t.title,
    t.priority,
    t.status,
    t.due_date,
    g.name AS group_name,
    u.name AS manager_name,
    COUNT(a.user_id) AS num_executors
FROM tasks t
LEFT JOIN task_groups g ON t.group_id = g.id
LEFT JOIN users u ON t.manager_id = u.id
LEFT JOIN task_assignments a ON t.id = a.task_id
GROUP BY t.id;

CREATE INDEX idx_task_due_date ON tasks(due_date);
CREATE INDEX idx_task_priority ON tasks(priority);
CREATE INDEX idx_assignment_user ON task_assignments(user_id);
CREATE INDEX idx_comment_task ON comments(task_id);
