-- ============================================================
-- Project Time Tracking System - Database Schema
-- Engine: InnoDB | Charset: utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS `project_tracker`
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `project_tracker`;

-- ------------------------------------------------------------
-- Roles (role based access control)
-- ------------------------------------------------------------
CREATE TABLE `roles` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE,        -- employee, manager, admin
    `description` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `roles` (`name`, `description`) VALUES
    ('admin',    'Full system access, manages users and roles'),
    ('manager',  'Can view all employee reports and manage projects'),
    ('employee', 'Can log time entries against projects');

-- ------------------------------------------------------------
-- Users
-- ------------------------------------------------------------
CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120) NOT NULL,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role_id` INT UNSIGNED NOT NULL,
    `manager_id` INT UNSIGNED DEFAULT NULL,     -- optional reporting line
    `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `failed_login_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `locked_until` DATETIME DEFAULT NULL,
    `last_login_at` DATETIME DEFAULT NULL,
    `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`),
    CONSTRAINT `fk_users_manager` FOREIGN KEY (`manager_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_users_role` (`role_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Projects
-- ------------------------------------------------------------
CREATE TABLE `projects` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `code` VARCHAR(30) DEFAULT NULL UNIQUE,     -- optional short code
    `description` VARCHAR(500) DEFAULT NULL,
    `created_by` INT UNSIGNED NOT NULL,
    `status` ENUM('active','archived') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_projects_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`),
    UNIQUE KEY `uniq_project_name` (`name`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Time Entries (the daily work log per user per project)
-- ------------------------------------------------------------
CREATE TABLE `time_entries` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `project_id` INT UNSIGNED NOT NULL,
    `entry_date` DATE NOT NULL,
    `from_time` TIME NOT NULL,
    `to_time` TIME NOT NULL,
    `duration_minutes` INT UNSIGNED GENERATED ALWAYS AS
        (TIME_TO_SEC(TIMEDIFF(`to_time`, `from_time`)) / 60) STORED,
    `status` ENUM('completed','pending','on_hold','others') NOT NULL DEFAULT 'pending',
    `remark` VARCHAR(1000) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_entries_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_entries_project` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    CONSTRAINT `chk_time_order` CHECK (`to_time` > `from_time`),
    INDEX `idx_entries_user_date` (`user_id`, `entry_date`),
    INDEX `idx_entries_project_date` (`project_id`, `entry_date`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Audit log (security requirement: trace sensitive actions)
-- ------------------------------------------------------------
CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,           -- e.g. LOGIN_SUCCESS, LOGIN_FAILED, ENTRY_CREATE
    `entity_type` VARCHAR(50) DEFAULT NULL,
    `entity_id` INT UNSIGNED DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `meta` JSON DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_audit_user` (`user_id`),
    INDEX `idx_audit_action` (`action`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Persistent login / API tokens (for "remember me" & API auth)
-- ------------------------------------------------------------
CREATE TABLE `auth_tokens` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `token_hash` VARCHAR(255) NOT NULL,
    `expires_at` DATETIME NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_tokens_user` (`user_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Seed an initial admin user
-- Password: Admin@123  (CHANGE IMMEDIATELY AFTER FIRST LOGIN)
-- Hash generated with PHP password_hash() using PASSWORD_BCRYPT
-- ------------------------------------------------------------
INSERT INTO `users` (`name`, `email`, `password_hash`, `role_id`, `status`)
VALUES (
    'System Admin',
    'admin@example.com',
    '$2y$12$yrAqQnIxtR.gdeEYWVboFOwPS02d5BYVECcK5Vht0OvEqswRerRDe', -- Admin@123
    (SELECT id FROM roles WHERE name = 'admin'),
    'active'
);