-- ============================================================
-- Team Task Management System - Database Schema
-- MySQL 8.x | InnoDB | utf8mb4
-- ============================================================

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

SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- ROLES & PERMISSIONS
-- ------------------------------------------------------------
CREATE TABLE roles (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(50) NOT NULL UNIQUE,        -- admin, team_leader, team_member
    display_name  VARCHAR(100) NOT NULL,
    description   VARCHAR(255) DEFAULT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL UNIQUE,       -- e.g. tasks.create, users.manage
    module        VARCHAR(50) NOT NULL,
    description   VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id        INT UNSIGNED NOT NULL,
    permission_id  INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- DEPARTMENTS / TEAMS / USERS
-- ------------------------------------------------------------
CREATE TABLE departments (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    description   VARCHAR(255) DEFAULT NULL,
    is_active     TINYINT(1) NOT NULL DEFAULT 1,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE teams (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    department_id  INT UNSIGNED NOT NULL,
    leader_id      INT UNSIGNED DEFAULT NULL,   -- FK added after users table
    description    VARCHAR(255) DEFAULT NULL,
    is_active      TINYINT(1) NOT NULL DEFAULT 1,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT,
    INDEX idx_teams_department (department_id)
) ENGINE=InnoDB;

CREATE TABLE users (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(100) NOT NULL,
    username          VARCHAR(50) NOT NULL UNIQUE,
    email             VARCHAR(150) NOT NULL UNIQUE,
    password_hash     VARCHAR(255) NOT NULL,
    mobile            VARCHAR(30) DEFAULT NULL,
    position           VARCHAR(100) DEFAULT NULL,
    photo             VARCHAR(255) DEFAULT NULL,
    role_id           INT UNSIGNED NOT NULL,
    department_id     INT UNSIGNED DEFAULT NULL,
    team_id           INT UNSIGNED DEFAULT NULL,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    failed_login_count INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until      DATETIME DEFAULT NULL,
    last_login_at     DATETIME DEFAULT NULL,
    created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL,
    INDEX idx_users_role (role_id),
    INDEX idx_users_team (team_id)
) ENGINE=InnoDB;

ALTER TABLE teams
    ADD CONSTRAINT fk_teams_leader FOREIGN KEY (leader_id) REFERENCES users(id) ON DELETE SET NULL;

CREATE TABLE team_members (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_id    INT UNSIGNED NOT NULL,
    user_id    INT UNSIGNED NOT NULL,
    joined_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_team_user (team_id, user_id),
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PROJECTS / CATEGORIES
-- ------------------------------------------------------------
CREATE TABLE projects (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(150) NOT NULL,
    description  TEXT,
    team_id      INT UNSIGNED DEFAULT NULL,
    manager_id   INT UNSIGNED DEFAULT NULL,
    start_date   DATE DEFAULT NULL,
    end_date     DATE DEFAULT NULL,
    status       ENUM('planning','active','on_hold','completed','cancelled') NOT NULL DEFAULT 'planning',
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL,
    FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_projects_status (status)
) ENGINE=InnoDB;

CREATE TABLE task_categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE,
    color       VARCHAR(20) DEFAULT '#6c757d',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TASKS
-- ------------------------------------------------------------
CREATE TABLE tasks (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_number        VARCHAR(20) NOT NULL UNIQUE,   -- e.g. TSK-000123
    title              VARCHAR(200) NOT NULL,
    description        TEXT,
    project_id         INT UNSIGNED DEFAULT NULL,
    category_id        INT UNSIGNED DEFAULT NULL,
    team_id            INT UNSIGNED NOT NULL,
    assigned_by        INT UNSIGNED NOT NULL,
    assigned_to        INT UNSIGNED DEFAULT NULL,
    priority           ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
    status             ENUM('new','assigned','accepted','in_progress','waiting','on_hold','completed','cancelled','closed')
                          NOT NULL DEFAULT 'new',
    start_date         DATE DEFAULT NULL,
    due_date           DATE DEFAULT NULL,
    completion_date    DATETIME DEFAULT NULL,
    estimated_hours    DECIMAL(6,2) DEFAULT NULL,
    actual_hours       DECIMAL(6,2) DEFAULT NULL,
    progress           TINYINT UNSIGNED NOT NULL DEFAULT 0, -- 0-100
    created_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES task_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_tasks_status (status),
    INDEX idx_tasks_priority (priority),
    INDEX idx_tasks_team (team_id),
    INDEX idx_tasks_assigned_to (assigned_to),
    INDEX idx_tasks_due_date (due_date),
    CONSTRAINT chk_progress CHECK (progress BETWEEN 0 AND 100)
) ENGINE=InnoDB;

-- Reassignment history (separate from progress history for clarity)
CREATE TABLE task_assignments (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id       INT UNSIGNED NOT NULL,
    assigned_from INT UNSIGNED DEFAULT NULL,
    assigned_to   INT UNSIGNED NOT NULL,
    assigned_by   INT UNSIGNED NOT NULL,
    reason        VARCHAR(255) DEFAULT NULL,
    assigned_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_from) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_ta_task (task_id)
) ENGINE=InnoDB;

-- Progress / status change history (timeline)
CREATE TABLE task_progress (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id     INT UNSIGNED NOT NULL,
    user_id     INT UNSIGNED NOT NULL,
    status      VARCHAR(30) DEFAULT NULL,
    progress    TINYINT UNSIGNED DEFAULT NULL,
    notes       TEXT,
    logged_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_tp_task (task_id),
    INDEX idx_tp_logged_at (logged_at)
) ENGINE=InnoDB;

CREATE TABLE task_comments (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id       INT UNSIGNED NOT NULL,
    user_id       INT UNSIGNED NOT NULL,
    parent_id     INT UNSIGNED DEFAULT NULL,  -- for threaded replies
    comment       TEXT NOT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES task_comments(id) ON DELETE CASCADE,
    INDEX idx_tc_task (task_id)
) ENGINE=InnoDB;

CREATE TABLE task_attachments (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id        INT UNSIGNED NOT NULL,
    comment_id     INT UNSIGNED DEFAULT NULL,  -- optional: attached to a comment
    uploaded_by    INT UNSIGNED NOT NULL,
    original_name  VARCHAR(255) NOT NULL,
    stored_name    VARCHAR(255) NOT NULL,      -- random/hashed filename on disk
    file_path      VARCHAR(500) NOT NULL,
    file_size      INT UNSIGNED NOT NULL,      -- bytes
    mime_type      VARCHAR(100) DEFAULT NULL,
    uploaded_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (comment_id) REFERENCES task_comments(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_att_task (task_id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- NOTIFICATIONS
-- ------------------------------------------------------------
CREATE TABLE notifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,          -- recipient
    task_id     INT UNSIGNED DEFAULT NULL,
    type        VARCHAR(50) NOT NULL,           -- new_assignment, due_today, overdue, ...
    message     VARCHAR(255) NOT NULL,
    is_read     TINYINT(1) NOT NULL DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    INDEX idx_notif_user_unread (user_id, is_read)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ACTIVITY / AUDIT LOGS
-- ------------------------------------------------------------
CREATE TABLE activity_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED DEFAULT NULL,
    action      VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED DEFAULT NULL,
    action      VARCHAR(50) NOT NULL,        -- create, update, delete, login, logout
    table_name  VARCHAR(100) NOT NULL,
    record_id   INT UNSIGNED DEFAULT NULL,
    old_value   JSON DEFAULT NULL,
    new_value   JSON DEFAULT NULL,
    ip_address  VARCHAR(45) DEFAULT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audit_table_record (table_name, record_id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SETTINGS (shared app state pattern)
-- ------------------------------------------------------------
CREATE TABLE settings (
    setting_key    VARCHAR(100) NOT NULL PRIMARY KEY,
    setting_value  TEXT,
    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- SEED DATA
-- ============================================================
INSERT INTO roles (name, display_name, description) VALUES
('admin', 'System Administrator', 'Full system access'),
('team_leader', 'Team Leader', 'Manages own team and tasks'),
('team_member', 'Team Member', 'Works on assigned tasks');

INSERT INTO permissions (name, module, description) VALUES
('users.manage', 'users', 'Create/edit/delete users'),
('teams.manage', 'teams', 'Create/edit/delete teams'),
('departments.manage', 'departments', 'Create/edit/delete departments'),
('projects.manage', 'projects', 'Create/edit/delete projects'),
('tasks.create', 'tasks', 'Create tasks'),
('tasks.assign', 'tasks', 'Assign/reassign tasks'),
('tasks.update_own', 'tasks', 'Update tasks assigned to self'),
('tasks.approve', 'tasks', 'Approve task completion'),
('reports.view', 'reports', 'View and export reports'),
('audit.view', 'audit', 'View audit logs'),
('settings.manage', 'settings', 'Manage system settings');

-- admin gets everything
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

-- team_leader
INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE name IN
('tasks.create','tasks.assign','tasks.update_own','tasks.approve','reports.view');

-- team_member
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE name IN ('tasks.update_own');

INSERT INTO settings (setting_key, setting_value) VALUES
('app_name', 'Team Task Management System'),
('company_name', 'IT Operations'),
('default_theme', 'light'),
('max_upload_mb', '20'),
('task_number_prefix', 'TSK'),
('default_language', 'en');

INSERT INTO departments (name, description) VALUES
('IT Operations', 'Infrastructure and operations team'),
('Network Infrastructure', 'Network engineering and support');

-- Default admin user - password: Admin@12345 (CHANGE IMMEDIATELY AFTER FIRST LOGIN)
-- Hash verified with Python bcrypt (password_verify()-compatible): bcrypt.checkpw() = True
INSERT INTO users (name, username, email, password_hash, role_id, department_id, is_active)
VALUES ('System Administrator', 'admin', 'admin@example.com',
'$2b$10$HgQs4yjD9CUJ1Jc3wTJ.DemTUXMyLCB82ID0Nmu6JjEQgc1tg0QMO', 1, 1, 1);

INSERT INTO task_categories (name, color) VALUES
('Server Maintenance', '#0d6efd'),
('Network Configuration', '#198754'),
('Security', '#dc3545'),
('General', '#6c757d');