-- =============================================================================
-- 99-full-schema-sync.sql
-- Team Tracker — full schema sync for production (cPanel / phpMyAdmin)
--
-- SAFE: additive only — no DROP TABLE, no DROP COLUMN, no DELETE data.
-- Creates missing tables, adds missing columns, adds FKs if missing,
-- inserts missing permissions, records migrations.
--
-- BEFORE YOU RUN:
--   1. Full database backup (structure + data)
--   2. phpMyAdmin → SQL tab
--   3. Set Delimiter to:  $$  (below the SQL box)
--   4. Paste THIS ENTIRE FILE → Go
--   5. Set Delimiter back to:  ;
--
-- RE-RUN SAFE: can import again anytime; existing data is not removed.
-- AFTER RUN: owner login once (seeds access_roles) then re-run section 6
--            backfill if members.access_role_id is still NULL.
-- =============================================================================

-- =============================================================================
-- SECTION 1 — Helper procedures (requires delimiter $$)
-- =============================================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS `cp_safe_exec`$$
CREATE PROCEDURE `cp_safe_exec`(IN p_sql TEXT)
BEGIN
  DECLARE CONTINUE HANDLER FOR 1060, 1005, 1022, 1061, 1826, 1832 BEGIN END;
  SET @cp_dynamic_sql = p_sql;
  PREPARE cp_stmt FROM @cp_dynamic_sql;
  EXECUTE cp_stmt;
  DEALLOCATE PREPARE cp_stmt;
END$$

DELIMITER ;

SELECT 'Section 1 complete — cp_safe_exec ready' AS status;

-- =============================================================================
-- SECTION 2 — Ensure migrations table exists
-- =============================================================================

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `timestamp` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =============================================================================
-- SECTION 3 — New tables (CREATE IF NOT EXISTS — never overwrites existing)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `access_roles` (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `owner_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(191) NOT NULL,
  `slug` varchar(191) NOT NULL,
  `description` varchar(191) NOT NULL DEFAULT 'NT/APL',
  `is_system` tinyint NOT NULL DEFAULT 0,
  `hierarchy` int NOT NULL DEFAULT 0,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `IDX_access_roles_owner_slug` (`owner_id`, `slug`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `role_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `permission_key` varchar(191) NOT NULL,
  `permission_value` varchar(191) NOT NULL,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `IDX_role_permissions_role_key` (`role_id`, `permission_key`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `_noticemembers` (
  `B` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `A` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  INDEX `IDX_notice_members_B` (`B`),
  INDEX `IDX_notice_members_A` (`A`),
  PRIMARY KEY (`B`, `A`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `attendance_requests` (
  `id` varchar(36) NOT NULL,
  `owner_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `member_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `request_type` enum('off_day_work','correction') NOT NULL,
  `request_date` datetime NOT NULL,
  `reason` varchar(191) NOT NULL DEFAULT 'NT/APL',
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `requested_payload` json NULL,
  `attendance_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `approver_type` varchar(191) NULL,
  `approver_owner_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `approver_member_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `approved_at` datetime NULL,
  `admin_note` varchar(191) NULL,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `IDX_attendance_requests_member_date_status` (`member_id`, `request_date`, `status`),
  INDEX `IDX_attendance_requests_owner_id` (`owner_id`),
  INDEX `IDX_attendance_requests_attendance_id` (`attendance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =============================================================================
-- SECTION 4 — Add missing columns on existing tables (re-run safe)
-- =============================================================================

CALL cp_safe_exec('ALTER TABLE `members` ADD `access_role_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL');

CALL cp_safe_exec('ALTER TABLE `working_schedule` ADD `timezone` VARCHAR(64) NOT NULL DEFAULT ''Asia/Dhaka''');

CALL cp_safe_exec('ALTER TABLE `working_schedule` ADD `monthly_correction_limit` INT NOT NULL DEFAULT 2');

CALL cp_safe_exec('ALTER TABLE `leaves` ADD `document` json NULL');

CALL cp_safe_exec('ALTER TABLE `attendances` ADD `checkout_note` varchar(191) NULL');

-- Extend leaves.leave_type enum for Day Off (additive — existing leave rows unchanged)
CALL cp_safe_exec('ALTER TABLE `leaves` MODIFY COLUMN `leave_type` enum (''annual'',''sick'',''casual'',''maternity'',''paternity'',''personal'',''compensatory'',''unpaid'',''day_off'') NOT NULL DEFAULT ''annual''');

-- =============================================================================
-- SECTION 5 — Foreign keys (re-run safe — ignores duplicates)
-- =============================================================================

CALL cp_safe_exec('ALTER TABLE `access_roles` ADD CONSTRAINT `FK_access_roles_owner` FOREIGN KEY (`owner_id`) REFERENCES `owner`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION');

CALL cp_safe_exec('ALTER TABLE `role_permissions` ADD CONSTRAINT `FK_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `access_roles`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION');

CALL cp_safe_exec('ALTER TABLE `members` ADD CONSTRAINT `FK_members_access_role` FOREIGN KEY (`access_role_id`) REFERENCES `access_roles`(`id`) ON DELETE SET NULL ON UPDATE NO ACTION');

CALL cp_safe_exec('ALTER TABLE `_noticemembers` ADD CONSTRAINT `FK_notice_members_notice` FOREIGN KEY (`B`) REFERENCES `notices`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');

CALL cp_safe_exec('ALTER TABLE `_noticemembers` ADD CONSTRAINT `FK_notice_members_member` FOREIGN KEY (`A`) REFERENCES `members`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION');

CALL cp_safe_exec('ALTER TABLE `attendance_requests` ADD CONSTRAINT `FK_attendance_requests_owner` FOREIGN KEY (`owner_id`) REFERENCES `owner`(`id`) ON DELETE RESTRICT');

CALL cp_safe_exec('ALTER TABLE `attendance_requests` ADD CONSTRAINT `FK_attendance_requests_member` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT');

CALL cp_safe_exec('ALTER TABLE `attendance_requests` ADD CONSTRAINT `FK_attendance_requests_attendance` FOREIGN KEY (`attendance_id`) REFERENCES `attendances`(`id`) ON DELETE SET NULL');

-- =============================================================================
-- SECTION 6 — Backfill member access roles (only rows still NULL)
-- Run AFTER owner login seeds access_roles, or run npm run seed:access-roles
-- =============================================================================

UPDATE `members` m
INNER JOIN `access_roles` ar
  ON ar.owner_id = m.owner_id
  AND ar.slug COLLATE utf8mb4_general_ci = m.role
SET m.access_role_id = ar.id
WHERE m.access_role_id IS NULL;

-- =============================================================================
-- SECTION 7 — Permission scope updates (data only, idempotent)
-- =============================================================================

UPDATE role_permissions rp
INNER JOIN access_roles ar ON ar.id = rp.role_id
SET rp.permission_value = 'own_and_all'
WHERE ar.slug IN ('owner', 'admin')
  AND rp.permission_value = 'all'
  AND rp.permission_key IN (
    'dashboard.view', 'members.view', 'members.update', 'members.delete',
    'attendance.view', 'leave.view', 'leave.create', 'leave.update', 'leave.delete',
    'reports.view', 'calendar.view',
    'holidays.view', 'holidays.create', 'holidays.update', 'holidays.delete',
    'notice.view', 'notice.create', 'notice.update', 'notice.delete'
  );

UPDATE role_permissions rp
INNER JOIN access_roles ar ON ar.id = rp.role_id
SET rp.permission_value = 'all'
WHERE ar.slug = 'owner'
  AND rp.permission_value IN ('own_and_all', 'own')
  AND rp.permission_key IN (
    'dashboard.view', 'members.view', 'members.update', 'members.delete',
    'attendance.view', 'leave.view', 'leave.create', 'leave.update', 'leave.delete',
    'reports.view', 'calendar.view',
    'holidays.view', 'holidays.create', 'holidays.update', 'holidays.delete',
    'notice.view', 'notice.create', 'notice.update', 'notice.delete'
  );

UPDATE role_permissions rp
INNER JOIN access_roles ar ON ar.id = rp.role_id
SET rp.permission_value = 'all'
WHERE ar.slug = 'owner'
  AND rp.permission_value = 'own_and_all'
  AND rp.permission_key IN (
    'dashboard.view', 'members.view', 'members.update', 'members.delete',
    'attendance.view', 'leave.view', 'leave.create', 'leave.update', 'leave.delete',
    'reports.view', 'calendar.view',
    'holidays.view', 'holidays.create', 'holidays.update', 'holidays.delete',
    'notice.view', 'notice.create', 'notice.update', 'notice.delete'
  );

-- =============================================================================
-- SECTION 8 — Attendance request permissions (insert if missing)
-- =============================================================================

INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, 'attendance.request.create', 'own'
FROM access_roles ar
WHERE ar.slug = 'member'
  AND NOT EXISTS (
    SELECT 1 FROM role_permissions rp
    WHERE rp.role_id = ar.id AND rp.permission_key = 'attendance.request.create'
  );

INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, 'attendance.request.view', 'own'
FROM access_roles ar
WHERE ar.slug = 'member'
  AND NOT EXISTS (
    SELECT 1 FROM role_permissions rp
    WHERE rp.role_id = ar.id AND rp.permission_key = 'attendance.request.view'
  );

INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, 'attendance.request.approve', 'false'
FROM access_roles ar
WHERE ar.slug = 'member'
  AND NOT EXISTS (
    SELECT 1 FROM role_permissions rp
    WHERE rp.role_id = ar.id AND rp.permission_key = 'attendance.request.approve'
  );

INSERT INTO role_permissions (id, role_id, permission_key, permission_value)
SELECT UUID(), ar.id, perm.permission_key, perm.permission_value
FROM access_roles ar
INNER JOIN (
  SELECT 'attendance.request.create' AS permission_key, 'all' AS permission_value
  UNION ALL SELECT 'attendance.request.view', 'all'
  UNION ALL SELECT 'attendance.request.approve', 'true'
) perm
WHERE ar.slug IN ('admin', 'owner')
  AND NOT EXISTS (
    SELECT 1 FROM role_permissions rp
    WHERE rp.role_id = ar.id AND rp.permission_key = perm.permission_key
  );

-- =============================================================================
-- SECTION 9 — Record all migrations (idempotent)
-- =============================================================================

INSERT INTO migrations (timestamp, name) SELECT 1780721550917, 'InitialSchema1780721550917' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'InitialSchema1780721550917');
INSERT INTO migrations (timestamp, name) SELECT 1780738512641, 'NewDatabaseConnection1780738512641' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'NewDatabaseConnection1780738512641');
INSERT INTO migrations (timestamp, name) SELECT 1780900000000, 'AccessControl1780900000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'AccessControl1780900000000');
INSERT INTO migrations (timestamp, name) SELECT 1780910000000, 'MemberAccessRole1780910000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'MemberAccessRole1780910000000');
INSERT INTO migrations (timestamp, name) SELECT 1780920000000, 'UpgradeLegacyScopeValues1780920000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'UpgradeLegacyScopeValues1780920000000');
INSERT INTO migrations (timestamp, name) SELECT 1780930000000, 'OwnerScopeValuesAll1780930000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'OwnerScopeValuesAll1780930000000');
INSERT INTO migrations (timestamp, name) SELECT 1780940000000, 'OwnerScopeUseAllMembers1780940000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'OwnerScopeUseAllMembers1780940000000');
INSERT INTO migrations (timestamp, name) SELECT 1780950000000, 'WorkScheduleTimezone1780950000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'WorkScheduleTimezone1780950000000');
INSERT INTO migrations (timestamp, name) SELECT 1780960000000, 'NoticeMembers1780960000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'NoticeMembers1780960000000');
INSERT INTO migrations (timestamp, name) SELECT 1780970000000, 'NoticeMembersForeignKeys1780970000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'NoticeMembersForeignKeys1780970000000');
INSERT INTO migrations (timestamp, name) SELECT 1780980000000, 'LeaveDocument1780980000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'LeaveDocument1780980000000');
INSERT INTO migrations (timestamp, name) SELECT 1780990000000, 'AttendanceCheckoutNote1780990000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'AttendanceCheckoutNote1780990000000');
INSERT INTO migrations (timestamp, name) SELECT 1781000000000, 'AttendanceRequests1781000000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'AttendanceRequests1781000000000');
INSERT INTO migrations (timestamp, name) SELECT 1781010000000, 'AttendanceRequestsForeignKeys1781010000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'AttendanceRequestsForeignKeys1781010000000');
INSERT INTO migrations (timestamp, name) SELECT 1781020000000, 'AttendanceRequestPermissions1781020000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'AttendanceRequestPermissions1781020000000');
INSERT INTO migrations (timestamp, name) SELECT 1781030000000, 'WorkScheduleCorrectionLimit1781030000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'WorkScheduleCorrectionLimit1781030000000');
INSERT INTO migrations (timestamp, name) SELECT 1781040000000, 'LeaveTypeDayOff1781040000000' WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE name = 'LeaveTypeDayOff1781040000000');

-- =============================================================================
-- SECTION 10 — Verification (compare row counts to your backup snapshot)
-- =============================================================================

SELECT COUNT(*) AS members_count FROM members;
SELECT COUNT(*) AS attendances_count FROM attendances;
SELECT COUNT(*) AS leaves_count FROM leaves;

SHOW COLUMNS FROM `working_schedule` LIKE 'timezone';
SHOW COLUMNS FROM `working_schedule` LIKE 'monthly_correction_limit';
SHOW COLUMNS FROM `members` LIKE 'access_role_id';
SHOW COLUMNS FROM `leaves` LIKE 'document';
SHOW COLUMNS FROM `leaves` LIKE 'leave_type';
SHOW COLUMNS FROM `attendances` LIKE 'checkout_note';
SHOW TABLES LIKE 'access_roles';
SHOW TABLES LIKE 'attendance_requests';

SELECT COUNT(*) AS members_missing_access_role_id
FROM members WHERE access_role_id IS NULL;

SELECT name FROM migrations ORDER BY timestamp;

SELECT '99-full-schema-sync.sql completed successfully' AS status;
