-- ============================================================
-- Hammerhead Workspace - Schema Extension
-- Adds tables needed for: Workspace Home, Super Admin Panel,
-- Notifications, Activity/Audit Log, Project Dashboards config.
-- Run AFTER hammerhead_original.sql
-- ============================================================

-- 1. NOTIFICATIONS (Workspace Home > Notifications / Pending Items)
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_type` enum('superadmin','manager','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `message` varchar(500) DEFAULT '',
  `link` varchar(300) DEFAULT '',
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_notif_user` (`user_type`,`user_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. ACTIVITY LOG (Workspace Home > Recent Activity, and audit trail)
CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_type` enum('superadmin','manager','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(200) DEFAULT '',
  `department_id` int(10) UNSIGNED DEFAULT NULL,
  `project_id` int(10) UNSIGNED DEFAULT NULL,
  `action` varchar(100) NOT NULL,          -- e.g. 'task_created', 'user_updated', 'login'
  `description` varchar(500) DEFAULT '',
  `ip` varchar(45) DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_activity_user` (`user_type`,`user_id`),
  KEY `idx_activity_dept` (`department_id`),
  KEY `idx_activity_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. AUDIT LOG (Super Admin > Reports & Audit Logs - sensitive admin actions)
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_type` enum('superadmin','manager','employee') NOT NULL,
  `actor_id` int(11) NOT NULL,
  `actor_name` varchar(200) DEFAULT '',
  `entity_type` varchar(60) NOT NULL,      -- 'user','role','department','project','permission'
  `entity_id` varchar(60) DEFAULT '',
  `action` enum('create','update','delete','enable','disable','role_change','permission_change','password_reset','login','logout','login_failed') NOT NULL,
  `before_data` longtext DEFAULT NULL,     -- JSON snapshot
  `after_data` longtext DEFAULT NULL,      -- JSON snapshot
  `ip` varchar(45) DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_actor` (`actor_type`,`actor_id`),
  KEY `idx_audit_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. PROJECT DASHBOARD WIDGETS (config-driven, so dashboards are not hardcoded per project)
CREATE TABLE IF NOT EXISTS `dashboard_widgets` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` int(10) UNSIGNED NOT NULL,
  `widget_type` enum('stat_card','pie_chart','bar_chart','line_chart','recent_activity','quick_actions','table') NOT NULL,
  `title` varchar(150) NOT NULL,
  `config` longtext DEFAULT NULL,          -- JSON: data source query key, colors, columns etc.
  `sort_order` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_widget_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. PROJECT FORMS (generic form builder reference per project)
CREATE TABLE IF NOT EXISTS `project_forms` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `schema` longtext DEFAULT NULL,          -- JSON field definitions
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_project_slug` (`project_id`,`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. PROJECT REPORTS (saved/report definitions per project)
CREATE TABLE IF NOT EXISTS `project_reports` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` varchar(400) DEFAULT '',
  `query_config` longtext DEFAULT NULL,    -- JSON: filters, columns, grouping
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. UNIFIED SESSIONS (for "One Login" across superadmin/manager/employee)
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` varchar(64) NOT NULL,               -- session token (hashed)
  `user_type` enum('superadmin','manager','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `ip` varchar(45) DEFAULT '',
  `user_agent` varchar(300) DEFAULT '',
  `last_activity` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sess_user` (`user_type`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8a. Widen user_type enums to include 'employee' — original schema only allowed
--     ('manager','superadmin'), which would silently block employee access assignment.
ALTER TABLE `user_roles`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_departments`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_projects`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_permission_overrides`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

-- 8b. Add missing columns to employees for login (spec says employees can also log in)
ALTER TABLE `employees`
  ADD COLUMN IF NOT EXISTS `username` varchar(100) DEFAULT NULL AFTER `name`,
  ADD COLUMN IF NOT EXISTS `email` varchar(200) DEFAULT NULL AFTER `username`,
  ADD COLUMN IF NOT EXISTS `password_hash` varchar(255) DEFAULT NULL AFTER `email`,
  ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) DEFAULT 1 AFTER `password_hash`,
  ADD COLUMN IF NOT EXISTS `reset_token` varchar(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reset_expires` datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `last_login` datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reports_to_type` enum('manager','superadmin') DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reports_to_id` int(11) DEFAULT NULL;

ALTER TABLE `managers`
  ADD COLUMN IF NOT EXISTS `reports_to_type` enum('manager','superadmin') DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reports_to_id` int(11) DEFAULT NULL;

-- ============================================================
-- NOTE: roles, modules, permissions, role_permissions, and
-- departments are already fully seeded in your existing database
-- (roles: super_admin/CEO, department_head, manager, team_lead,
-- employee — modules 1-9 — permissions 1-72). No re-seeding needed
-- or wanted here; doing so would conflict with your existing IDs.
-- ============================================================