-- ============================================================
-- LIVE DB MIGRATION SCRIPT
-- Target DB: crmlive1 (or the production database)
-- Purpose: Align live DB schema with local (igcrm) schema
-- Run: mysql -u root -p crmlive1 < fix_live_db.sql
-- ============================================================

USE crmlive1;

-- ============================================================
-- 1. FIX followup.type enum
-- Local has: CALL, MESSAGE, DEMO_SCHEDULED, DEMO_COMPLETED,
--            QUOTE_REQUEST, QUOTE_SEND, VISIT_SCHEDULED,
--            VISIT_COMPLETED, NEGOTIATION, FOLLOWUP, DEMO, QUOTE
-- Live has:  FOLLOWUP, DEMO, QUOTE, NEGOTIATION (old/smaller enum)
-- ============================================================
ALTER TABLE followup
  MODIFY COLUMN `type` 
    enum('CALL','MESSAGE','DEMO_SCHEDULED','DEMO_COMPLETED','QUOTE_REQUEST','QUOTE_SEND','VISIT_SCHEDULED','VISIT_COMPLETED','NEGOTIATION','FOLLOWUP','DEMO','QUOTE')
    NOT NULL DEFAULT 'FOLLOWUP';

-- ============================================================
-- 2. ADD MISSING columns in opportunity table
-- Local has: closingProbability, expectedClosingTimeframe
-- Live: these columns are missing
-- ============================================================
ALTER TABLE opportunity
  ADD COLUMN IF NOT EXISTS `closingProbability` INT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `expectedClosingTimeframe` VARCHAR(191) NULL;

-- ============================================================
-- VERIFICATION QUERIES (run after migration to confirm)
-- ============================================================
-- SHOW COLUMNS FROM followup WHERE Field = 'type';
-- SHOW COLUMNS FROM opportunity LIKE 'closing%';
-- SHOW COLUMNS FROM opportunity LIKE 'expected%';