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

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