-- ============================================================
--  Atlas – Database Migration
--  Run this once against your MySQL database.
-- ============================================================

-- ──────────────────────────────────────────────────────────
--  employees  (update existing table to support Atlas roles)
-- ──────────────────────────────────────────────────────────
-- Ensure employee_type supports the four Atlas roles.
-- Existing rows can be updated manually to one of:
--   'Project Manager' | 'Employee' | 'Team Lead' | 'Viewer'
ALTER TABLE `employees`
    MODIFY COLUMN `employee_type` VARCHAR(50) NOT NULL DEFAULT 'Employee',
    MODIFY COLUMN `status`        VARCHAR(20) NOT NULL DEFAULT 'Active';

-- Add project column to existing tasks tables (safe to run on existing DB)
ALTER TABLE `tasks`
    ADD COLUMN IF NOT EXISTS `project` VARCHAR(255) DEFAULT NULL AFTER `priority`;

-- ──────────────────────────────────────────────────────────
--  projects
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `projects` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id`   BIGINT UNSIGNED NOT NULL,
    `name`         VARCHAR(255)    NOT NULL,
    `description`  TEXT            DEFAULT NULL,
    `color`        VARCHAR(20)     NOT NULL DEFAULT '',
    `created_by`   BIGINT UNSIGNED NOT NULL,
    `status`       VARCHAR(20)     NOT NULL DEFAULT 'Active',
    `date_created` DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_project_company` (`company_id`),
    KEY `idx_project_name`    (`company_id`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add project_id column to tasks (links a task to a project)
ALTER TABLE `tasks`
    ADD COLUMN IF NOT EXISTS `project_id` BIGINT UNSIGNED DEFAULT NULL AFTER `team_id`;

-- ──────────────────────────────────────────────────────────
--  login_sessions
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `login_sessions` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id`       BIGINT UNSIGNED NOT NULL,
    `user_type`        VARCHAR(50)     NOT NULL DEFAULT 'Employee',
    `user_id`          BIGINT UNSIGNED NOT NULL,
    `device_id`        VARCHAR(255)    NOT NULL DEFAULT '',
    `device_name`      VARCHAR(255)    NOT NULL DEFAULT '',
    `token`            VARCHAR(500)    NOT NULL DEFAULT '',
    `user_ses_id`      VARCHAR(255)    NOT NULL,
    `mode`             VARCHAR(50)     NOT NULL DEFAULT 'app',
    `status`           VARCHAR(20)     NOT NULL DEFAULT 'active',
    `login_date_time`  DATETIME        NOT NULL,
    `logout_date_time` DATETIME                 DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ls_user`     (`user_id`, `company_id`),
    KEY `idx_ls_ses`      (`user_ses_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  tasks
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `tasks` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id`  BIGINT UNSIGNED NOT NULL,
    `title`       VARCHAR(255)    NOT NULL,
    `description` TEXT            DEFAULT NULL,
    `assigned_to` BIGINT UNSIGNED NOT NULL,              -- employee.id
    `assigned_by` BIGINT UNSIGNED NOT NULL,              -- employee.id (Project Manager)
    `due_date`    DATE            DEFAULT NULL,
    `priority`    VARCHAR(30)     NOT NULL DEFAULT 'Normal',
    `project`     VARCHAR(255)             DEFAULT NULL,
    `team_id`     BIGINT UNSIGNED          DEFAULT NULL,
    `status`      VARCHAR(30)     NOT NULL DEFAULT 'Upcoming',
    -- extra statuses: Due | Flagged
    -- statuses: Upcoming | In Progress | Pending Approval | Completed | Failed
    `approved_by` BIGINT UNSIGNED          DEFAULT NULL,
    `approved_at` DATETIME                 DEFAULT NULL,
    `marked_by`   BIGINT UNSIGNED          DEFAULT NULL,
    `marked_at`   DATETIME                 DEFAULT NULL,
    `mark_note`   TEXT            DEFAULT NULL,
    `date_created` DATETIME       NOT NULL,
    `updated_at`  DATETIME                 DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_task_company`     (`company_id`),
    KEY `idx_task_assigned_to` (`assigned_to`),
    KEY `idx_task_status`      (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  task_comments
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `task_comments` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `task_id`      BIGINT UNSIGNED NOT NULL,
    `employee_id`  BIGINT UNSIGNED NOT NULL,
    `company_id`   BIGINT UNSIGNED NOT NULL,
    `comment`      TEXT            NOT NULL,
    `date_created` DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_tc_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  task_approvals
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `task_approvals` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `task_id`      BIGINT UNSIGNED NOT NULL,
    `approved_by`  BIGINT UNSIGNED NOT NULL,             -- Team Lead employee.id
    `company_id`   BIGINT UNSIGNED NOT NULL,
    `status`       VARCHAR(30)     NOT NULL DEFAULT 'Approved',
    `note`         TEXT            DEFAULT NULL,
    `date_created` DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ta_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  notifications
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `notifications` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id`  BIGINT UNSIGNED NOT NULL,
    `company_id`   BIGINT UNSIGNED NOT NULL,
    `title`        VARCHAR(255)    NOT NULL,
    `message`      TEXT            NOT NULL,
    `type`         VARCHAR(50)     NOT NULL DEFAULT 'general',
    `type_id`      BIGINT UNSIGNED          DEFAULT NULL,
    `is_read`      ENUM('Yes','No') NOT NULL DEFAULT 'No',
    `read_at`      DATETIME                 DEFAULT NULL,
    `date_created` DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_notif_employee` (`employee_id`, `company_id`),
    KEY `idx_notif_unread`   (`employee_id`, `is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  employee_activity  (create if missing)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `employee_activity` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id`     BIGINT UNSIGNED NOT NULL,
    `company_id`      BIGINT UNSIGNED NOT NULL,
    `description`     TEXT            DEFAULT NULL,
    `sub_description` TEXT            DEFAULT NULL,
    `type`            VARCHAR(100)    DEFAULT NULL,
    `type_id`         BIGINT UNSIGNED DEFAULT NULL,
    `extras`          TEXT            DEFAULT NULL,
    `button_text`     VARCHAR(255)    DEFAULT NULL,
    `date_created`    DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ea_employee` (`employee_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ──────────────────────────────────────────────────────────
--  employee_successful_report  (create if missing)
-- ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `employee_successful_report` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_type`     VARCHAR(100)    DEFAULT NULL,
    `employee_id`     BIGINT UNSIGNED DEFAULT NULL,
    `transaction_id`  VARCHAR(255)    DEFAULT NULL,
    `server_request`  LONGTEXT        DEFAULT NULL,
    `server_response` LONGTEXT        DEFAULT NULL,
    `date_time`       DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_transaction` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;