-- Nakhsha Central - Module 0 / Part 1
-- Authentication sessions, users, roles/permissions, project access and global audit trail.
-- Target: MySQL 5.7+ / PHP 8.1+
-- Run ONCE on the existing `nakhshahomes2_app` database after taking a backup.

START TRANSACTION;

-- ---------------------------------------------------------------------------
-- Roles: retain all existing role IDs/keys so the current app keeps working.
-- ---------------------------------------------------------------------------
ALTER TABLE `roles`
  ADD COLUMN `description` varchar(255) DEFAULT NULL AFTER `name`,
  ADD COLUMN `is_system` tinyint(1) NOT NULL DEFAULT '1' AFTER `description`,
  ADD COLUMN `is_active` tinyint(1) NOT NULL DEFAULT '1' AFTER `is_system`,
  ADD COLUMN `sort_order` int(10) UNSIGNED NOT NULL DEFAULT '100' AFTER `is_active`;

UPDATE `roles` SET `sort_order`=`id`*10, `is_system`=1 WHERE `id` IS NOT NULL;

INSERT IGNORE INTO `roles` (`role_key`,`name`,`description`,`is_system`,`is_active`,`sort_order`) VALUES
('senior_manager','Senior Manager','Senior operational management across assigned projects.',1,1,20),
('manager','Manager','General management role with project-level access.',1,1,30),
('accountant','Accountant','Finance, billing, payment and ledger operations.',1,1,40),
('sales_manager','Sales Manager','CRM, lead and quotation operations.',1,1,50),
('purchase_manager','Purchase Manager','Procurement, RFQ and purchase order operations.',1,1,60),
('warehouse_manager','Warehouse Manager','Warehouse and inventory operations.',1,1,70),
('supervisor','Supervisor','Site supervision and execution updates.',1,1,80),
('hr_associate','Associate HR','Staff, attendance, payroll and HR operations.',1,1,90),
('design_engineer','Design Engineer','Design, drawing and approval operations.',1,1,100),
('data_entry_operator','Data Entry Operator','Controlled operational data-entry role.',1,1,110),
('project_partner','Project Partner','Partner access limited to assigned projects.',1,1,120),
('subcontractor','Sub Contractor','External subcontractor access to assigned scope.',1,1,130),
('operator','Operator','Equipment/operator focused access.',1,1,140),
('viewer','Viewer','Read-only access to permitted modules/projects.',1,1,150);

-- ---------------------------------------------------------------------------
-- Users: enrich the existing user master without replacing existing records.
-- ---------------------------------------------------------------------------
ALTER TABLE `users`
  ADD COLUMN `employee_code` varchar(60) DEFAULT NULL AFTER `role_id`,
  ADD COLUMN `joining_date` date DEFAULT NULL AFTER `designation`,
  ADD COLUMN `reporting_manager_id` bigint(20) UNSIGNED DEFAULT NULL AFTER `joining_date`,
  ADD COLUMN `web_access` tinyint(1) NOT NULL DEFAULT '1' AFTER `reporting_manager_id`,
  ADD COLUMN `mobile_access` tinyint(1) NOT NULL DEFAULT '1' AFTER `web_access`,
  ADD COLUMN `invited_at` datetime DEFAULT NULL AFTER `mobile_access`,
  ADD COLUMN `created_by` bigint(20) UNSIGNED DEFAULT NULL AFTER `invited_at`,
  ADD UNIQUE KEY `uq_users_employee_code` (`employee_code`),
  ADD KEY `idx_users_reporting_manager` (`reporting_manager_id`),
  ADD KEY `idx_users_created_by` (`created_by`),
  ADD CONSTRAINT `fk_users_reporting_manager` FOREIGN KEY (`reporting_manager_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_users_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

-- ---------------------------------------------------------------------------
-- Login sessions: keep token_hash/expires_at for backwards compatibility,
-- while adding refresh-token rotation and multi-device session management.
-- ---------------------------------------------------------------------------
ALTER TABLE `api_tokens`
  ADD COLUMN `refresh_token_hash` char(64) DEFAULT NULL AFTER `token_hash`,
  ADD COLUMN `refresh_expires_at` datetime DEFAULT NULL AFTER `expires_at`,
  ADD COLUMN `device_id` varchar(120) DEFAULT NULL AFTER `refresh_expires_at`,
  ADD COLUMN `device_name` varchar(160) DEFAULT NULL AFTER `device_id`,
  ADD COLUMN `platform` enum('android','ios','web','unknown') NOT NULL DEFAULT 'unknown' AFTER `device_name`,
  ADD COLUMN `ip_address` varchar(64) DEFAULT NULL AFTER `platform`,
  ADD COLUMN `user_agent` varchar(500) DEFAULT NULL AFTER `ip_address`,
  ADD COLUMN `last_seen_at` datetime DEFAULT NULL AFTER `user_agent`,
  ADD COLUMN `revoked_at` datetime DEFAULT NULL AFTER `last_seen_at`,
  ADD UNIQUE KEY `uq_api_refresh_token_hash` (`refresh_token_hash`),
  ADD KEY `idx_api_tokens_user_active` (`user_id`,`revoked_at`,`expires_at`),
  ADD KEY `idx_api_tokens_refresh` (`refresh_token_hash`,`refresh_expires_at`,`revoked_at`);

-- ---------------------------------------------------------------------------
-- Permission catalogue and role permission matrix.
-- ---------------------------------------------------------------------------
CREATE TABLE `permissions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `permission_key` varchar(120) NOT NULL,
  `module_key` varchar(80) NOT NULL,
  `name` varchar(140) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int(10) UNSIGNED NOT NULL DEFAULT '100',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permissions_key` (`permission_key`),
  KEY `idx_permissions_module` (`module_key`,`is_active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `role_permissions` (
  `role_id` int(10) UNSIGNED NOT NULL,
  `permission_id` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_role_permissions_permission` (`permission_id`),
  CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `permissions` (`permission_key`,`module_key`,`name`,`description`,`sort_order`) VALUES
('core.dashboard.view','core','View Core Dashboard','View the Nakhsha Central operational landing area.',10),
('core.users.view','core','View Team Members','View team member profiles and status.',20),
('core.users.create','core','Create Team Members','Create new internal/external user accounts.',30),
('core.users.edit','core','Edit Team Members','Edit roles, access and account status.',40),
('core.users.reset_password','core','Reset User Passwords','Issue a temporary password and force password change.',50),
('core.roles.view','core','View Roles & Permissions','View role definitions and permission matrix.',60),
('core.roles.manage','core','Manage Roles & Permissions','Create custom roles and edit permission assignments.',70),
('core.project_access.view','core','View Project Access','View project membership for users.',80),
('core.project_access.manage','core','Manage Project Access','Assign and remove users from projects.',90),
('core.activity.view','core','View Activity Logs','View the company-wide audit/activity trail.',100),
('core.sessions.view','core','View Sessions','View own/managed active login sessions.',110),
('core.settings.view','core','View Settings','Open Module 0 configuration.',120),
('tickets.view','tickets','View Tickets','View tickets permitted by project access.',200),
('tickets.create','tickets','Create Tickets','Create customer/site tickets where allowed.',210),
('tickets.manage','tickets','Manage Tickets','Assign, update and resolve tickets.',220),
('feedback.view','feedback','View Feedback','View project/customer feedback.',230),
('feedback.create','feedback','Create Feedback','Submit project/customer feedback.',235),
('feedback.manage','feedback','Manage Feedback','Acknowledge, assign and close feedback.',240),
('stock.view','stock','View Stock','View site stock information.',250),
('stock.update','stock','Update Stock','Submit site stock updates.',260),
('notifications.view','notifications','View Notifications','View personal in-app notifications.',270),
('projects.view','projects','View Projects','View projects permitted by project access.',300);

-- Admin always receives every permission.
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.role_key='admin';

-- Sensible compatibility defaults for the roles already used by the app.
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r JOIN permissions p
WHERE r.role_key='project_manager'
  AND p.permission_key IN (
    'core.dashboard.view','core.users.view','core.project_access.view','core.activity.view',
    'tickets.view','tickets.manage','feedback.view','feedback.manage','stock.view',
    'notifications.view','projects.view'
  );

INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r JOIN permissions p
WHERE r.role_key='site_engineer'
  AND p.permission_key IN (
    'core.dashboard.view','tickets.view','tickets.manage','feedback.view','stock.view','stock.update',
    'notifications.view','projects.view'
  );

INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r JOIN permissions p
WHERE r.role_key='department_user'
  AND p.permission_key IN (
    'core.dashboard.view','tickets.view','tickets.manage','feedback.view','stock.view',
    'notifications.view','projects.view'
  );

INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r JOIN permissions p
WHERE r.role_key='customer'
  AND p.permission_key IN (
    'core.dashboard.view','tickets.view','tickets.create','feedback.view','feedback.create','notifications.view','projects.view'
  );

INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r JOIN permissions p
WHERE r.role_key IN ('senior_manager','manager')
  AND p.permission_key IN (
    'core.dashboard.view','core.users.view','core.roles.view','core.project_access.view','core.activity.view','core.settings.view',
    'tickets.view','tickets.manage','feedback.view','feedback.manage','stock.view','notifications.view','projects.view'
  );

INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r JOIN permissions p
WHERE r.role_key='viewer'
  AND p.permission_key IN ('core.dashboard.view','tickets.view','feedback.view','stock.view','notifications.view','projects.view');

-- Every signed-in user may review and revoke their own login sessions.
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id,p.id FROM roles r JOIN permissions p ON p.permission_key='core.sessions.view'
WHERE r.is_active=1;

-- ---------------------------------------------------------------------------
-- Project access. During Module 0, existing `sites` are the current project
-- records; Module 1 can evolve/rename the project master without changing the
-- user/project access concept.
-- ---------------------------------------------------------------------------
CREATE TABLE `project_access` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `site_id` bigint(20) UNSIGNED NOT NULL,
  `assignment_role` varchar(120) DEFAULT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT '0',
  `access_source` varchar(30) NOT NULL DEFAULT 'manual',
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_project_access_user_site` (`user_id`,`site_id`),
  KEY `idx_project_access_site` (`site_id`),
  KEY `idx_project_access_created_by` (`created_by`),
  CONSTRAINT `fk_project_access_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_project_access_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_project_access_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `project_access` (`user_id`,`site_id`,`assignment_role`,`is_primary`,`access_source`)
SELECT st.user_id,st.site_id,COALESCE(NULLIF(st.designation,''),'Team Member'),st.is_primary,'site_team'
FROM site_team st;

INSERT IGNORE INTO `project_access` (`user_id`,`site_id`,`assignment_role`,`is_primary`,`access_source`)
SELECT s.site_engineer_id,s.id,'Site Engineer',1,'legacy_site' FROM sites s WHERE s.site_engineer_id IS NOT NULL;

INSERT IGNORE INTO `project_access` (`user_id`,`site_id`,`assignment_role`,`is_primary`,`access_source`)
SELECT s.project_manager_id,s.id,'Project Manager',1,'legacy_site' FROM sites s WHERE s.project_manager_id IS NOT NULL;

INSERT IGNORE INTO `project_access` (`user_id`,`site_id`,`assignment_role`,`is_primary`,`access_source`)
SELECT s.customer_id,s.id,'Customer',1,'legacy_site' FROM sites s WHERE s.customer_id IS NOT NULL;

-- ---------------------------------------------------------------------------
-- Company-wide activity/audit trail. Existing admin_audit_log is preserved.
-- ---------------------------------------------------------------------------
CREATE TABLE `activity_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `project_id` bigint(20) UNSIGNED DEFAULT NULL,
  `module_key` varchar(80) NOT NULL,
  `action` varchar(80) NOT NULL,
  `entity_type` varchar(80) NOT NULL,
  `entity_id` bigint(20) UNSIGNED DEFAULT NULL,
  `summary` varchar(500) DEFAULT NULL,
  `old_values_json` mediumtext,
  `new_values_json` mediumtext,
  `metadata_json` mediumtext,
  `ip_address` varchar(64) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_activity_actor` (`actor_user_id`,`created_at`),
  KEY `idx_activity_project` (`project_id`,`created_at`),
  KEY `idx_activity_module` (`module_key`,`created_at`),
  KEY `idx_activity_entity` (`entity_type`,`entity_id`,`created_at`),
  CONSTRAINT `fk_activity_actor` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_activity_project` FOREIGN KEY (`project_id`) REFERENCES `sites` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `activity_logs`
(`actor_user_id`,`module_key`,`action`,`entity_type`,`entity_id`,`summary`,`new_values_json`,`created_at`)
SELECT `admin_user_id`,'legacy_admin',`action`,`entity_type`,`entity_id`,
       CONCAT('Legacy admin action: ',`action`),`details_json`,`created_at`
FROM `admin_audit_log`;

COMMIT;