-- =====================================================================
--  QA REPORT — DATA FIXES
--  16 Sep 2026 wali QA report ke woh masail jo sirf code se theek nahi hote.
--
--  Har statement dobara chalaya ja sakta hai (idempotent) — nuqsan nahi hoga.
--  phpMyAdmin → apna database → SQL tab → paste → Go
-- =====================================================================


-- ─────────────────────────────────────────────────────────────
--  1. NAYA TABLE: contact_messages            (QA P0-2)
--
--  `POST /api/contact` ka koi route hi nahi tha — form 404 karta tha aur UI
--  chup rehta tha. Launch ke baad se har enquiry zaya ho rahi thi. Ab contact
--  form AUR admission ka online registration dono yahan mehfooz hote hain.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type`          VARCHAR(20)  NOT NULL DEFAULT 'contact',   -- contact | admission
  `name`          VARCHAR(120) NOT NULL,
  `email`         VARCHAR(200) NOT NULL,
  `phone`         VARCHAR(40)  NULL,
  `subject`       VARCHAR(200) NULL,
  `message`       TEXT         NOT NULL,
  `student_name`  VARCHAR(120) NULL,
  `class_applied` VARCHAR(60)  NULL,
  `campus`        VARCHAR(80)  NULL,
  `status`        VARCHAR(20)  NOT NULL DEFAULT 'pending',   -- pending | read | archived
  `ip_hash`       VARCHAR(64)  NULL,                          -- poora IP kabhi mehfooz nahi hota
  `createdAt`     DATETIME     NOT NULL,
  `updatedAt`     DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `contact_messages_type_status` (`type`, `status`),
  KEY `contact_messages_created` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─────────────────────────────────────────────────────────────
--  2. GALLERY: media_type par quotes                (QA P0-3)
--
--  Purani PHP site se import ke waqt value quotes ke SAATH mehfooz ho gayi:
--  "'image'" (7 characters) — "image" nahi. Filter kabhi match nahi karta tha,
--  is liye 48 ke 48 records public gallery aur admin dono mein GHAYAB the.
-- ─────────────────────────────────────────────────────────────
UPDATE `image_galleries`
   SET `media_type` = TRIM(BOTH '''' FROM `media_type`)
 WHERE `media_type` LIKE '''%''';

UPDATE `image_galleries`
   SET `media_type` = LOWER(TRIM(`media_type`))
 WHERE `media_type` <> LOWER(TRIM(`media_type`));

-- Jo ab bhi khali ya ghair-mutawaqqa hain — image maan lo
UPDATE `image_galleries`
   SET `media_type` = 'image'
 WHERE `media_type` IS NULL OR `media_type` NOT IN ('image', 'video');

-- Aur column ka DEFAULT bhi theek karo.
--
-- Rows saaf kar dena kaafi nahi tha: column ka default khud lafzi 'image'
-- (quotes samet) tha, is liye koi bhi nayi row jo media_type na bheje wohi
-- kharab value le leti aur gallery se chhup jati. Wajah purani PHP site nahi
-- thi — pichli migration.sql mein quote-escaping ghalat thi.
SET @def := (SELECT COLUMN_DEFAULT FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'image_galleries'
                AND COLUMN_NAME = 'media_type');
SET @sql := IF(@def IS NULL OR @def NOT LIKE '%''%',
  "SELECT 'skip: media_type ka default pehle se theek hai' AS note",
  "ALTER TABLE `image_galleries` ALTER COLUMN `media_type` SET DEFAULT 'image'");
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────────────────────────────────────────────────────
--  3. GLIMPSES: legacy codes                        (QA P2-6)
--
--  file_type '1'/'2' rakhta hai, 'image'/'video' nahi.
--  (Purani rows par quotes bhi ho sakte hain — pehle woh hata dete hain.)
-- ─────────────────────────────────────────────────────────────
UPDATE `glimpses` SET `file_type` = TRIM(BOTH '''' FROM `file_type`) WHERE `file_type` LIKE '''%''';
UPDATE `glimpses` SET `campus`    = TRIM(BOTH '''' FROM `campus`)    WHERE `campus`    LIKE '''%''';

UPDATE `glimpses` SET `file_type` = 'image' WHERE TRIM(`file_type`) = '1';
UPDATE `glimpses` SET `file_type` = 'video' WHERE TRIM(`file_type`) = '2';


-- ─────────────────────────────────────────────────────────────
--  4. KHALI TITLES                                  (QA P1-10)
--
--  Purani site sarkhi `description` mein rakhti thi aur `title` khali chhor
--  deti thi — 52 mein se 51 news, 18 mein se 15 events, 4 mein se 3 jobs.
--  Nateeja: /news par bilkul khali cards aur khali <h1>.
--
--  Neeche description ki PEHLI LINE se title bhar dete hain (90 characters tak).
--  Code mein bhi fallback laga diya gaya hai, taake aage kabhi khali card na bane.
-- ─────────────────────────────────────────────────────────────
UPDATE `news`
   SET `title` = TRIM(LEFT(
         SUBSTRING_INDEX(REPLACE(REPLACE(`description`, '\r\n', '\n'), '\r', '\n'), '\n', 1), 90))
 WHERE (`title` IS NULL OR TRIM(`title`) = '')
   AND `description` IS NOT NULL AND TRIM(`description`) <> '';

UPDATE `events`
   SET `title` = TRIM(LEFT(
         SUBSTRING_INDEX(REPLACE(REPLACE(`description`, '\r\n', '\n'), '\r', '\n'), '\n', 1), 90))
 WHERE (`title` IS NULL OR TRIM(`title`) = '')
   AND `description` IS NOT NULL AND TRIM(`description`) <> '';

UPDATE `jobs`
   SET `title` = TRIM(LEFT(
         SUBSTRING_INDEX(REPLACE(REPLACE(`description`, '\r\n', '\n'), '\r', '\n'), '\n', 1), 90))
 WHERE (`title` IS NULL OR TRIM(`title`) = '')
   AND `description` IS NOT NULL AND TRIM(`description`) <> '';


-- ─────────────────────────────────────────────────────────────
--  5. FACULTY TYPE KA TYPO                          (QA P2-7)
-- ─────────────────────────────────────────────────────────────
-- Column ka naam faculty_type hai (kuch installs mein 'type'). Dono soorat
-- chal jaye aur na mile to chup-chaap skip ho — poori script na ruke.
SET @col := (SELECT COLUMN_NAME FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'faculties'
                AND COLUMN_NAME IN ('faculty_type', 'type') LIMIT 1);
SET @sql := IF(@col IS NULL,
  "SELECT 'skip: faculties mein type column nahi' AS note",
  CONCAT('UPDATE faculties SET ', @col, ' = ''viceprincipal'' WHERE ', @col, ' = ''wiseprincipal'''));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────────────────────────────────────────────────────
--  6. CAREERS ANNOUNCEMENT: file ka naam ghalat     (QA P2-6)
--
--  "125602199jpg" — extension se pehle nuqta hi nahi, is liye file kabhi
--  nahi khul sakti thi.
-- ─────────────────────────────────────────────────────────────
-- Table ka naam install ke mutabiq careers_announcements ya announcements ho
-- sakta hai — jo mile us par chalta hai, koi na mile to chup-chaap skip.
SET @tbl := (SELECT TABLE_NAME FROM information_schema.TABLES
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME IN ('careers_announcements', 'announcements') LIMIT 1);
SET @sql := IF(@tbl IS NULL,
  "SELECT 'skip: announcements table nahi mila' AS note",
  CONCAT('UPDATE ', @tbl, ' SET image = CONCAT(LEFT(image, CHAR_LENGTH(image) - 3), ''.'', RIGHT(image, 3))',
         ' WHERE image REGEXP ''^[0-9]+(jpg|png|gif)$'''));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────────────────────────────────────────────────────
--  7. NAYE PERMISSIONS ADMINISTRATOR ROLE KO DO
--
--  RBAC jaan-boojh kar naye catalog permissions khud se kisi role ko nahi
--  deta (taake permissions chupke se na barhein). Administrator ka matlab
--  "sab kuch" hai, is liye ye teen wahan explicit taur par add kar rahe hain.
--
--  Super Administrator ko zaroorat nahi — uske paas is_super_admin hai.
-- ─────────────────────────────────────────────────────────────
INSERT INTO `rbac_role_permissions` (`role_id`, `permission_code`, `createdAt`, `updatedAt`)
SELECT r.`id`, p.`code`, NOW(), NOW()
  FROM `rbac_roles` r
  JOIN (SELECT 'messages.read' AS code
        UNION ALL SELECT 'messages.manage'
        UNION ALL SELECT 'glimpses.update') p
 WHERE r.`key` = 'administrator'
   AND NOT EXISTS (
        SELECT 1 FROM `rbac_role_permissions` x
         WHERE x.`role_id` = r.`id` AND x.`permission_code` = p.`code`);


-- =====================================================================
--  TASDEEQ — sab chalne ke BAAD ye chalayein
-- =====================================================================

-- Gallery ab nazar aani chahiye: media_type sirf image/video, koi quotes nahi
SELECT `media_type`, COUNT(*) AS rows_ FROM `image_galleries` GROUP BY `media_type`;

-- Khali titles ab 0 hone chahiyen (jin rows mein description bhi khali thi, woh ruk sakti hain)
SELECT 'news'   AS tbl, COUNT(*) AS blank_titles FROM `news`   WHERE `title` IS NULL OR TRIM(`title`) = ''
UNION ALL
SELECT 'events', COUNT(*) FROM `events` WHERE `title` IS NULL OR TRIM(`title`) = ''
UNION ALL
SELECT 'jobs',   COUNT(*) FROM `jobs`   WHERE `title` IS NULL OR TRIM(`title`) = '';

-- Glimpses ke codes
SELECT `file_type`, COUNT(*) AS rows_ FROM `glimpses` GROUP BY `file_type`;

-- Administrator ko naye permissions mile?  (3 rows aani chahiyen)
SELECT rp.`permission_code`
  FROM `rbac_role_permissions` rp
  JOIN `rbac_roles` r ON r.`id` = rp.`role_id`
 WHERE r.`key` = 'administrator'
   AND rp.`permission_code` IN ('messages.read', 'messages.manage', 'glimpses.update');

-- contact_messages table mojood hai?
SELECT COUNT(*) AS contact_messages_table FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'contact_messages';


-- ─────────────────────────────────────────────────────────────
--  8. GOVERNING BODY — naya column + tarteeb shuru karo
--
--  AHEM: pehle ye column sirf sync({alter:true}) se banta tha, jo production
--  mein JAAN BOOJH kar band hai. Is liye wahan column banta hi nahi aur
--  tarteeb wali screen chalti hi nahi. Yahan explicit bana rahe hain.
-- ─────────────────────────────────────────────────────────────
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'governing_bodies' AND COLUMN_NAME = 'sort_order');
SET @sql := IF(@has > 0,
  "SELECT 'skip: governing_bodies.sort_order pehle se mojood' AS note",
  "ALTER TABLE `governing_bodies` ADD COLUMN `sort_order` INT NOT NULL DEFAULT 0");
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- Ab mojooda tarteeb (purana pehle) ko 1, 2, 3 … mein badlo
SET @needs_init := (SELECT COUNT(*) FROM `governing_bodies` WHERE `sort_order` > 0);
SET @i := 0;
SET @sql := IF(@needs_init > 0,
  "SELECT 'skip: governing_bodies ki tarteeb pehle se set hai' AS note",
  "UPDATE `governing_bodies` SET `sort_order` = (@i := @i + 1) ORDER BY `createdAt` ASC");
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────────────────────────────────────────────────────
--  9. SLIDER — duplicate / gaps saaf karo          (QA P2-4)
--
--  Live par do slides dono #6 par thin aur numbers 1, 5, 6, 7, 8 the
--  (delete ke baad khali jagah). Ab code MAX+1 deta hai aur delete par
--  dobara tarteeb laga deta hai — yahan mojooda data theek kar rahe hain.
-- ─────────────────────────────────────────────────────────────
SET @j := 0;
UPDATE `slider_images` SET `slide_number` = (@j := @j + 1) ORDER BY `slide_number` ASC, `id` ASC;


-- Tasdeeq
SELECT `sort_order`, COUNT(*) AS rows_ FROM `governing_bodies` GROUP BY `sort_order` HAVING COUNT(*) > 1;
-- ⬆ khali nateeja aana chahiye (koi duplicate tarteeb nahi)

SELECT id, slide_number FROM `slider_images` ORDER BY `slide_number`;
-- ⬆ 1, 2, 3 … lagatar hone chahiyen


-- ─────────────────────────────────────────────────────────────
--  10. DUPLICATE INDEXES SAAF KARO
--
--  Purana sync({alter:true}) har boot par unique columns ke liye ek NAYA
--  index bana deta tha — email, email_2, email_3 … email_64. MySQL ki hadd
--  64 keys per table hai; hadd poori hote hi sync phat jati thi aur poori
--  API 503 de deti thi. Site isi wajah se band hui thi.
--
--  Neeche SIRF wo indexes girte hain jinke naam <column>_<number> hain
--  (email_2, slug_7…). Har column ka ASLI index (email, slug) aur PRIMARY key
--  bilkul nahi chhoote. DATA ko haath nahi lagta. Pehle se saaf ho to kuch
--  nahi hota — is liye dobara chalana bhi mehfooz hai.
--
--  (Stored procedure jaan boojh kar istemal nahi kiya — DELIMITER phpMyAdmin
--   mein kabhi kabhi masla karta hai. Har table ke liye ek hi ALTER banta hai.)
-- ─────────────────────────────────────────────────────────────
SET SESSION group_concat_max_len = 200000;

-- users
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: users' AS note", CONCAT('ALTER TABLE `users` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- sspe_certificates
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sspe_certificates'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: sspe_certificates' AS note", CONCAT('ALTER TABLE `sspe_certificates` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- experience_certificates
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'experience_certificates'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: experience_certificates' AS note", CONCAT('ALTER TABLE `experience_certificates` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- page_contents
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'page_contents'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: page_contents' AS note", CONCAT('ALTER TABLE `page_contents` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- site_settings
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'site_settings'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: site_settings' AS note", CONCAT('ALTER TABLE `site_settings` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- image_galleries
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'image_galleries'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: image_galleries' AS note", CONCAT('ALTER TABLE `image_galleries` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- news
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'news'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: news' AS note", CONCAT('ALTER TABLE `news` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- events
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'events'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: events' AS note", CONCAT('ALTER TABLE `events` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- glimpses
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'glimpses'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: glimpses' AS note", CONCAT('ALTER TABLE `glimpses` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- faculties
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'faculties'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: faculties' AS note", CONCAT('ALTER TABLE `faculties` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- alumnis
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'alumnis'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: alumnis' AS note", CONCAT('ALTER TABLE `alumnis` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- careers
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'careers'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: careers' AS note", CONCAT('ALTER TABLE `careers` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- jobs
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'jobs'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: jobs' AS note", CONCAT('ALTER TABLE `jobs` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- menu_items
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'menu_items'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: menu_items' AS note", CONCAT('ALTER TABLE `menu_items` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- slider_images
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'slider_images'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: slider_images' AS note", CONCAT('ALTER TABLE `slider_images` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- reports
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'reports'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: reports' AS note", CONCAT('ALTER TABLE `reports` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- disclaimers
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'disclaimers'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: disclaimers' AS note", CONCAT('ALTER TABLE `disclaimers` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- upcoming_programs
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'upcoming_programs'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: upcoming_programs' AS note", CONCAT('ALTER TABLE `upcoming_programs` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- governing_bodies
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'governing_bodies'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: governing_bodies' AS note", CONCAT('ALTER TABLE `governing_bodies` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- tickers
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tickers'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: tickers' AS note", CONCAT('ALTER TABLE `tickers` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- announcements
SET @d := (SELECT GROUP_CONCAT(CONCAT('DROP INDEX `', INDEX_NAME, '`') SEPARATOR ', ')
             FROM (SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'announcements'
                      AND INDEX_NAME <> 'PRIMARY' AND INDEX_NAME REGEXP '_[0-9]+$') x);
SET @sql := IF(@d IS NULL, "SELECT 'skip: announcements' AS note", CONCAT('ALTER TABLE `announcements` ', @d));
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;


-- Tasdeeq: koi bhi table 10 se zyada indexes par nahi hona chahiye
SELECT TABLE_NAME, COUNT(DISTINCT INDEX_NAME) AS indexes_
  FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA = DATABASE()
 GROUP BY TABLE_NAME
 ORDER BY indexes_ DESC
 LIMIT 5;


-- ─────────────────────────────────────────────────────────────
--  11. BEECH-LAFZ KATAY HUE TITLES THEEK KARO
--
--  Upar wala backfill (hissa 4) description ki pehli line ko 90 characters
--  par kaat deta hai — aur wo kat aksar lafz ke darmiyan girta hai:
--    "…provides a solid foundation for individuals who want to p"
--  Ye parhne mein bura lagta hai aur walidain ko /news par nazar aata hai.
--
--  Neeche aise titles ko AAKHRI MUKAMMAL LAFZ tak wapas laate hain aur
--  aakhir mein … lagate hain.
--
--  Sirf wahi rows chunti hain jo:
--    • 85+ characters ki hon,
--    • pehle se … par khatam na hoti hon,
--    • description ki bilkul shuruaat hon (yani backfill se bani hon), aur
--    • jinke foran baad description mein harf/adad ho (yani kat lafz ke beech).
--  Is liye admin ka apna likha hua koi title isse nahi chhuta.
-- ─────────────────────────────────────────────────────────────
UPDATE `news`
   SET `title` = CONCAT(
         TRIM(LEFT(`title`, CHAR_LENGTH(`title`) - CHAR_LENGTH(SUBSTRING_INDEX(`title`, ' ', -1)))), '…')
 WHERE CHAR_LENGTH(`title`) >= 85
   AND `title` NOT LIKE '%…'
   AND `description` IS NOT NULL
   AND LOCATE(`title`, `description`) = 1
   AND SUBSTRING(`description`, CHAR_LENGTH(`title`) + 1, 1) REGEXP '[A-Za-z0-9]'
   AND LOCATE(' ', `title`) > 0;

UPDATE `events`
   SET `title` = CONCAT(
         TRIM(LEFT(`title`, CHAR_LENGTH(`title`) - CHAR_LENGTH(SUBSTRING_INDEX(`title`, ' ', -1)))), '…')
 WHERE CHAR_LENGTH(`title`) >= 85
   AND `title` NOT LIKE '%…'
   AND `description` IS NOT NULL
   AND LOCATE(`title`, `description`) = 1
   AND SUBSTRING(`description`, CHAR_LENGTH(`title`) + 1, 1) REGEXP '[A-Za-z0-9]'
   AND LOCATE(' ', `title`) > 0;

UPDATE `jobs`
   SET `title` = CONCAT(
         TRIM(LEFT(`title`, CHAR_LENGTH(`title`) - CHAR_LENGTH(SUBSTRING_INDEX(`title`, ' ', -1)))), '…')
 WHERE CHAR_LENGTH(`title`) >= 85
   AND `title` NOT LIKE '%…'
   AND `description` IS NOT NULL
   AND LOCATE(`title`, `description`) = 1
   AND SUBSTRING(`description`, CHAR_LENGTH(`title`) + 1, 1) REGEXP '[A-Za-z0-9]'
   AND LOCATE(' ', `title`) > 0;

-- Tasdeeq: ab koi title lafz ke beech se kata hua nahi hona chahiye
SELECT COUNT(*) AS still_cut_mid_word FROM `news`
 WHERE CHAR_LENGTH(`title`) >= 85 AND `title` NOT LIKE '%…'
   AND `description` IS NOT NULL AND LOCATE(`title`, `description`) = 1
   AND SUBSTRING(`description`, CHAR_LENGTH(`title`) + 1, 1) REGEXP '[A-Za-z0-9]';