-- ═══════════════════════════════════════════════════════════════
-- SHAMSI — PRODUCTION MIGRATION SQL
-- Generated: 2026-09-09 14:54:45
-- Database : shamciii_newsite
--
-- SIRF ADDITIVE hai: naye tables + naye columns.
-- Kisi MOJOODA column ko chhoota NAHI — is liye data zaya hone ka
-- khatra nahi. Har statement "IF NOT EXISTS" ya safe-guarded hai,
-- is liye dobara chalane par bhi masla nahi.
-- ═══════════════════════════════════════════════════════════════

-- ─────────────────────────────────────────────
-- HISSA 1: NAYE TABLES (9)
-- ─────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `application_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `career_id` bigint(20) unsigned NOT NULL,
  `status` varchar(30) NOT NULL,
  `interview_date` date DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  `changed_by` varchar(120) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `application_logs_career_id` (`career_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `csr_projects` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `location` varchar(200) DEFAULT NULL,
  `target_date` varchar(50) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` varchar(20) DEFAULT 'active',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `site_visits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ip_hash` varchar(32) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `referrer` varchar(255) DEFAULT NULL,
  `device` varchar(20) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `site_visits_created_at` (`createdAt`),
  KEY `site_visits_ip_hash` (`ip_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `rbac_roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(60) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(400) DEFAULT NULL,
  `is_system` tinyint(1) DEFAULT 0,
  `is_super_admin` tinyint(1) DEFAULT 0,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rbac_roles_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `rbac_role_permissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` bigint(20) unsigned NOT NULL,
  `permission_code` varchar(80) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rbac_role_perm_unique` (`role_id`,`permission_code`),
  KEY `rbac_role_perm_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `rbac_role_bindings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `group_id` bigint(20) unsigned DEFAULT NULL,
  `scope_type` varchar(20) NOT NULL DEFAULT 'all',
  `scope_value` varchar(120) NOT NULL DEFAULT '',
  `granted_by` bigint(20) unsigned DEFAULT NULL,
  `granted_by_name` varchar(120) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rbac_binding_user_unique` (`user_id`,`role_id`,`scope_type`,`scope_value`),
  UNIQUE KEY `rbac_binding_group_unique` (`group_id`,`role_id`,`scope_type`,`scope_value`),
  KEY `rbac_binding_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `rbac_groups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(60) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(400) DEFAULT NULL,
  `is_system` tinyint(1) DEFAULT 0,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rbac_groups_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `rbac_group_members` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `added_by` bigint(20) unsigned DEFAULT NULL,
  `added_by_name` varchar(120) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rbac_group_member_unique` (`group_id`,`user_id`),
  KEY `rbac_group_member_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `rbac_audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `actor_user_id` bigint(20) unsigned DEFAULT NULL,
  `actor_name` varchar(120) DEFAULT NULL,
  `actor_role` varchar(120) DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `outcome` varchar(20) NOT NULL DEFAULT 'success',
  `subject_type` varchar(40) DEFAULT NULL,
  `subject_id` varchar(60) DEFAULT NULL,
  `subject_label` varchar(200) DEFAULT NULL,
  `scope_type` varchar(20) DEFAULT NULL,
  `scope_value` varchar(120) DEFAULT NULL,
  `before_json` longtext DEFAULT NULL,
  `after_json` longtext DEFAULT NULL,
  `request_id` varchar(60) DEFAULT NULL,
  `ip` varchar(60) DEFAULT NULL,
  `message` varchar(500) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rbac_audit_created` (`createdAt`),
  KEY `rbac_audit_actor` (`actor_user_id`),
  KEY `rbac_audit_subject` (`subject_type`,`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ─────────────────────────────────────────────
-- HISSA 2: MOJOODA TABLES MEIN NAYE COLUMNS
--
-- MySQL 5.x mein "ADD COLUMN IF NOT EXISTS" nahi chalta, is liye
-- har column ke liye ek safe block hai jo pehle check karta hai.
-- Column pehle se ho to woh statement chup-chaap skip ho jata hai.
-- ─────────────────────────────────────────────

-- users.is_active
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='is_active') > 0,
  'SELECT 1',
  'ALTER TABLE `users` ADD COLUMN `is_active` tinyint(1) NOT NULL DEFAULT 1'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- users.auth_version
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='auth_version') > 0,
  'SELECT 1',
  'ALTER TABLE `users` ADD COLUMN `auth_version` int(11) NOT NULL DEFAULT 1'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- news.created_by
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='news' AND COLUMN_NAME='created_by') > 0,
  'SELECT 1',
  'ALTER TABLE `news` ADD COLUMN `created_by` bigint(20) unsigned DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- events.created_by
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='events' AND COLUMN_NAME='created_by') > 0,
  'SELECT 1',
  'ALTER TABLE `events` ADD COLUMN `created_by` bigint(20) unsigned DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- image_galleries.media_type
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='image_galleries' AND COLUMN_NAME='media_type') > 0,
  'SELECT 1',
  -- NOTE: yahan pehle ek extra quote-jorha tha (''''''image''''''), jis se column
  -- ka DEFAULT lafzi taur par 'image' (quotes samet) ban jata tha. Us default se
  -- banne wali har row gallery filter se chhup jati thi. Sahi shakl ''image'' hai.
  'ALTER TABLE `image_galleries` ADD COLUMN `media_type` varchar(10) DEFAULT ''image'''
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- image_galleries.video_url
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='image_galleries' AND COLUMN_NAME='video_url') > 0,
  'SELECT 1',
  'ALTER TABLE `image_galleries` ADD COLUMN `video_url` varchar(255) DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- image_galleries.created_by
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='image_galleries' AND COLUMN_NAME='created_by') > 0,
  'SELECT 1',
  'ALTER TABLE `image_galleries` ADD COLUMN `created_by` bigint(20) unsigned DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- glimpses.created_by
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='glimpses' AND COLUMN_NAME='created_by') > 0,
  'SELECT 1',
  'ALTER TABLE `glimpses` ADD COLUMN `created_by` bigint(20) unsigned DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- experience_certificates.designation
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='experience_certificates' AND COLUMN_NAME='designation') > 0,
  'SELECT 1',
  'ALTER TABLE `experience_certificates` ADD COLUMN `designation` varchar(200) DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- sspe_certificates.grade
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='sspe_certificates' AND COLUMN_NAME='grade') > 0,
  'SELECT 1',
  'ALTER TABLE `sspe_certificates` ADD COLUMN `grade` varchar(20) DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- disclaimers.image
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='disclaimers' AND COLUMN_NAME='image') > 0,
  'SELECT 1',
  'ALTER TABLE `disclaimers` ADD COLUMN `image` varchar(255) DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- careers.interview_date
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='careers' AND COLUMN_NAME='interview_date') > 0,
  'SELECT 1',
  'ALTER TABLE `careers` ADD COLUMN `interview_date` date DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- reports.title
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='reports' AND COLUMN_NAME='title') > 0,
  'SELECT 1',
  'ALTER TABLE `reports` ADD COLUMN `title` varchar(200) DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- reports.file
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='reports' AND COLUMN_NAME='file') > 0,
  'SELECT 1',
  'ALTER TABLE `reports` ADD COLUMN `file` varchar(255) DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- upcoming_programs.location_url
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='upcoming_programs' AND COLUMN_NAME='location_url') > 0,
  'SELECT 1',
  'ALTER TABLE `upcoming_programs` ADD COLUMN `location_url` varchar(500) DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- upcoming_programs.description
SET @s := (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='upcoming_programs' AND COLUMN_NAME='description') > 0,
  'SELECT 1',
  'ALTER TABLE `upcoming_programs` ADD COLUMN `description` text DEFAULT NULL'
));
PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ─────────────────────────────────────────────
-- HISSA 3: TASDEEQ — ye chala kar dekhein
-- ─────────────────────────────────────────────

SELECT TABLE_NAME AS 'naya table', TABLE_ROWS AS rows_ FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('application_logs','csr_projects','site_visits','rbac_roles','rbac_role_permissions','rbac_role_bindings','rbac_groups','rbac_group_members','rbac_audit_logs');
-- 9 rows aani chahiyen

SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=DATABASE() AND (TABLE_NAME, COLUMN_NAME) IN (('users','is_active'),('users','auth_version'),('news','created_by'),('events','created_by'),('image_galleries','media_type'),('image_galleries','video_url'),('image_galleries','created_by'),('glimpses','created_by'),('experience_certificates','designation'),('sspe_certificates','grade'),('disclaimers','image'),('careers','interview_date'),('reports','title'),('reports','file'),('upcoming_programs','location_url'),('upcoming_programs','description'))
ORDER BY TABLE_NAME, COLUMN_NAME;
-- 16 rows aani chahiyen

-- ---------------------------------------------------------------
-- Rename: Chairperson → Honorary GS, DPS → PS  (slug + menu URL)
-- Content safe rehta hai — sirf slug/url badalta hai.
-- Purane URLs frontend mein redirect ho jaate hain.
-- ---------------------------------------------------------------
UPDATE page_contents SET slug = 'honorary-gs',          title = 'Honorary GS'          WHERE slug = 'chairperson';
UPDATE page_contents SET slug = 'ps-community-history', title = 'PS Community History' WHERE slug = 'dps-community-history';
UPDATE menu_items SET url = '/about/honorary-gs'          WHERE url = '/about/chairperson';
UPDATE menu_items SET url = '/about/ps-community-history' WHERE url = '/about/dps-community-history';

-- GS / CEO page hata diya gaya (Honorary GS ke saath duplicate tha)
DELETE FROM page_contents WHERE slug = 'gs-ceo';
DELETE FROM menu_items    WHERE url  = '/about/gs-ceo';

-- ─────────────────────────────────────────────
--  AAKHRI TASDEEQ  (sab chalne ke BAAD ye chalayein)
-- ─────────────────────────────────────────────
-- 1) Naye tables mojood hain? — 8 rows aani chahiyen
SELECT TABLE_NAME AS 'naya table' FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME IN ('application_logs','csr_projects','site_visits',
                      'rbac_roles','rbac_role_permissions','rbac_role_bindings',
                      'rbac_groups','rbac_group_members','rbac_audit_logs');

-- 2) Rename lag gaya? — honorary-gs aur ps-community-history dikhne chahiyen,
--    chairperson / dps-community-history / gs-ceo bilkul nahi
SELECT slug, title FROM page_contents
 WHERE slug IN ('honorary-gs','ps-community-history','chairperson','dps-community-history','gs-ceo');

-- 3) Menu links theek hain?
SELECT id, label, url FROM menu_items WHERE url LIKE '/about/%gs%' OR url LIKE '%community-history%';

-- 4) users table par indexes 64 ki hadd se door hain? (10-15 normal hai)
SELECT COUNT(DISTINCT INDEX_NAME) AS users_indexes FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users';