SET @language_mode_exists = (
    SELECT COUNT(*) FROM settings WHERE `key` = 'site.language_mode'
);
SET @language_mode_value = IF(
    (SELECT COUNT(*) FROM languages WHERE is_enabled = 1) = 1,
    'single',
    'multilingual'
);
SET @language_mode_sql = IF(
    @language_mode_exists = 0,
    CONCAT(
        'INSERT INTO settings (`key`,value,type,is_secret,created_at,updated_at) VALUES (''site.language_mode'',''',
        @language_mode_value,
        ''',''string'',0,NOW(),NOW())'
    ),
    'SELECT 1'
);
PREPARE language_mode_stmt FROM @language_mode_sql;
EXECUTE language_mode_stmt;
DEALLOCATE PREPARE language_mode_stmt;

UPDATE layouts SET status = 'draft' WHERE status IS NULL OR status NOT IN ('draft', 'published');

SET @layout_count = (SELECT COUNT(*) FROM layouts);
SET @published_layout_count = (SELECT COUNT(*) FROM layouts WHERE status = 'published');
SET @only_layout_id = (SELECT MIN(id) FROM layouts);
UPDATE layouts SET status = 'published'
WHERE @layout_count = 1 AND @published_layout_count = 0 AND id = @only_layout_id;

SET @reports_notes_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'reports' AND COLUMN_NAME = 'notes'
);
SET @reports_notes_sql = IF(
    @reports_notes_exists = 0,
    'ALTER TABLE reports ADD COLUMN notes TEXT NULL AFTER status',
    'SELECT 1'
);
PREPARE reports_notes_stmt FROM @reports_notes_sql;
EXECUTE reports_notes_stmt;
DEALLOCATE PREPARE reports_notes_stmt;

SET @title_normalized_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'questions' AND COLUMN_NAME = 'title_normalized'
);
SET @title_normalized_sql = IF(
    @title_normalized_exists = 0,
    'ALTER TABLE questions ADD COLUMN title_normalized VARCHAR(255) NULL AFTER title',
    'SELECT 1'
);
PREPARE title_normalized_stmt FROM @title_normalized_sql;
EXECUTE title_normalized_stmt;
DEALLOCATE PREPARE title_normalized_stmt;

SET @body_normalized_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'questions' AND COLUMN_NAME = 'body_normalized'
);
SET @body_normalized_sql = IF(
    @body_normalized_exists = 0,
    'ALTER TABLE questions ADD COLUMN body_normalized LONGTEXT NULL AFTER body',
    'SELECT 1'
);
PREPARE body_normalized_stmt FROM @body_normalized_sql;
EXECUTE body_normalized_stmt;
DEALLOCATE PREPARE body_normalized_stmt;

UPDATE questions SET title_normalized = LOWER(REPLACE(title, 'ـ', '')), body_normalized = LOWER(REPLACE(body, 'ـ', ''));
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'أ', 'ا'), body_normalized = REPLACE(body_normalized, 'أ', 'ا');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'إ', 'ا'), body_normalized = REPLACE(body_normalized, 'إ', 'ا');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'آ', 'ا'), body_normalized = REPLACE(body_normalized, 'آ', 'ا');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'ٱ', 'ا'), body_normalized = REPLACE(body_normalized, 'ٱ', 'ا');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'ى', 'ي'), body_normalized = REPLACE(body_normalized, 'ى', 'ي');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'ة', 'ه'), body_normalized = REPLACE(body_normalized, 'ة', 'ه');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'ؤ', 'و'), body_normalized = REPLACE(body_normalized, 'ؤ', 'و');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'ئ', 'ي'), body_normalized = REPLACE(body_normalized, 'ئ', 'ي');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'ً', ''), body_normalized = REPLACE(body_normalized, 'ً', '');
UPDATE questions SET title_normalized = REPLACE(title_normalized, 'ٌ', ''), body_normalized = REPLACE(body_normalized, 'ٌ', '');

SET @status_created_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'questions' AND INDEX_NAME = 'idx_questions_status_created'
);
SET @status_created_sql = IF(
    @status_created_exists = 0,
    'ALTER TABLE questions ADD KEY idx_questions_status_created (status, created_at)',
    'SELECT 1'
);
PREPARE status_created_stmt FROM @status_created_sql;
EXECUTE status_created_stmt;
DEALLOCATE PREPARE status_created_stmt;

SET @status_views_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'questions' AND INDEX_NAME = 'idx_questions_status_views'
);
SET @status_views_sql = IF(
    @status_views_exists = 0,
    'ALTER TABLE questions ADD KEY idx_questions_status_views (status, views_count)',
    'SELECT 1'
);
PREPARE status_views_stmt FROM @status_views_sql;
EXECUTE status_views_stmt;
DEALLOCATE PREPARE status_views_stmt;

SET @status_votes_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'questions' AND INDEX_NAME = 'idx_questions_status_votes'
);
SET @status_votes_sql = IF(
    @status_votes_exists = 0,
    'ALTER TABLE questions ADD KEY idx_questions_status_votes (status, votes_count)',
    'SELECT 1'
);
PREPARE status_votes_stmt FROM @status_votes_sql;
EXECUTE status_votes_stmt;
DEALLOCATE PREPARE status_votes_stmt;

-- Re-apply the idempotent homepage widget seeds from migrations 021 and 022.
INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'top_contributors',NULL,'{"limit":5}',200,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='top_contributors');

INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'most_viewed',NULL,'{"limit":5}',210,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='most_viewed');

INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'most_useful',NULL,'{"limit":5}',220,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='most_useful');

INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'most_answered',NULL,'{"limit":5}',230,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='most_answered');

INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'unanswered',NULL,'{"limit":5}',240,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='unanswered');

INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'active_tags',NULL,'{"limit":10}',250,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='active_tags');

INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'trending',NULL,'{"limit":5}',260,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='trending');

INSERT INTO widgets (layout_section_id,type,title,config_json,sort_order,is_enabled,created_at,updated_at)
SELECT ls.id,'ad_slot',NULL,'{"placement":"homepage"}',190,1,NOW(),NOW()
FROM layout_sections ls INNER JOIN layouts l ON l.id=ls.layout_id
WHERE l.status='published' AND ls.section_key='homepage_main'
  AND NOT EXISTS (SELECT 1 FROM widgets w WHERE w.layout_section_id=ls.id AND w.type='ad_slot');
