-- ==========================================================
-- SaniBook CONSOLIDATED DATABASE REFINEMENT SCRIPT
-- Date: Feb 18, 2026
-- ==========================================================

-- 1. SCHEMA MIGRATION: Update Submissions and Users tables
-- Adds new registration fields and removes redundant location columns

ALTER TABLE `submissions`
ADD COLUMN IF NOT EXISTS `title` VARCHAR(255) AFTER `org_name`,
ADD COLUMN IF NOT EXISTS `contact_person` VARCHAR(255) AFTER `title`,
ADD COLUMN IF NOT EXISTS `contact_role` VARCHAR(100) AFTER `contact_person`,
ADD COLUMN IF NOT EXISTS `description` TEXT AFTER `story`,
ADD COLUMN IF NOT EXISTS `county` VARCHAR(100) AFTER `description`,
ADD COLUMN IF NOT EXISTS `service_chains` JSON AFTER `is_verified`,
ADD COLUMN IF NOT EXISTS `enabling_pillars` JSON AFTER `service_chains`,
ADD COLUMN IF NOT EXISTS `thematic_categories` JSON AFTER `enabling_pillars`,
ADD COLUMN IF NOT EXISTS `status` VARCHAR(50) DEFAULT 'pending' AFTER `thematic_categories`,
ADD COLUMN IF NOT EXISTS `admin_notes` TEXT AFTER `status`,
ADD COLUMN IF NOT EXISTS `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- Separate command for column removal
ALTER TABLE `submissions` DROP COLUMN IF EXISTS `sub_county`;

ALTER TABLE `users`
ADD COLUMN IF NOT EXISTS `user_type` ENUM('individual', 'organization') DEFAULT 'organization',
ADD COLUMN IF NOT EXISTS `salutation` VARCHAR(20) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `personal_email` VARCHAR(255) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `personal_phone` VARCHAR(50) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `company_email` VARCHAR(255) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `company_phone` VARCHAR(50) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `designation` VARCHAR(255) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `website` VARCHAR(255) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `service_chains` TEXT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `enabling_pillars` TEXT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `challenges_sector` TEXT DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `counties` TEXT DEFAULT NULL;

ALTER TABLE `users` DROP COLUMN IF EXISTS `sub_county`;


-- 2. PRICING REFINEMENT: Update package descriptions
-- Set clean descriptions and remove placeholder phrases

-- Set Free tier description
UPDATE `subscription_packages` 
SET `description` = 'Entry-level package. Best for CBOs and small projects.' 
WHERE `price` = 0;

-- Remove specific targeted phrases from paid packages
UPDATE `subscription_packages` 
SET `description` = REPLACE(`description`, 'Ideal for County-level programs with high volume.', '')
WHERE `price` > 0;

UPDATE `subscription_packages` 
SET `description` = REPLACE(`description`, 'For National Partners & International NGOs.', '')
WHERE `price` > 0;

-- Remove placeholder "Best for" text if present
UPDATE `subscription_packages` 
SET `description` = REGEXP_REPLACE(`description`, 'Best for [^.]+', '')
WHERE `price` > 0 AND `description` LIKE '%Best for%';

-- Clean up any trailing periods or excess whitespace
UPDATE `subscription_packages` 
SET `description` = TRIM(`description`)
WHERE `price` > 0;


-- 3. DUMMY DATA PURGE: Remove placeholder content

-- Purge Submissions
DELETE FROM `submissions` 
WHERE `org_name` LIKE '%Example%' 
   OR `org_name` LIKE '%Dummy%' 
   OR `org_name` LIKE '%Test Org%'
   OR `title`    LIKE '%Placeholder%' 
   OR `title`    LIKE '%Test Submission%'
   OR `story`    LIKE '%Lorem ipsum%';

-- Purge Content (Legacy CMS items)
-- Corrected: 'body' is used instead of 'story' in the content table
DELETE FROM `content` 
WHERE `title` LIKE '%Dummy%' 
   OR `title` LIKE '%Test%' 
   OR `body` LIKE '%Lorem ipsum%'
   OR `category_id` = 0;

-- ==========================================================
-- SCRIPT END
-- ==========================================================
