-- ===============================================
-- MIGRATION: Add new form fields to reports table
-- ===============================================

USE halohost_ndps;

-- Add new columns to reports table
ALTER TABLE reports 
ADD COLUMN IF NOT EXISTS input_number VARCHAR(50) AFTER reference_id,
ADD COLUMN IF NOT EXISTS input_date DATETIME AFTER input_number,
ADD COLUMN IF NOT EXISTS input_location VARCHAR(500) AFTER input_date,
ADD COLUMN IF NOT EXISTS product_type VARCHAR(100) AFTER input_location,
ADD COLUMN IF NOT EXISTS suspect_name_address TEXT AFTER product_type,
ADD COLUMN IF NOT EXISTS mobile_email VARCHAR(255) AFTER suspect_name_address,
ADD COLUMN IF NOT EXISTS info_type VARCHAR(50) AFTER mobile_email,
ADD COLUMN IF NOT EXISTS suspect_vehicle TEXT AFTER info_type,
ADD COLUMN IF NOT EXISTS special_note TEXT AFTER suspect_vehicle,
ADD COLUMN IF NOT EXISTS caller_name_address TEXT AFTER special_note,
ADD COLUMN IF NOT EXISTS caller_mobile VARCHAR(50) AFTER caller_name_address,
ADD COLUMN IF NOT EXISTS source VARCHAR(20) DEFAULT 'telegram' AFTER status;

-- Add indexes for new fields
ALTER TABLE reports 
ADD INDEX IF NOT EXISTS idx_product_type (product_type),
ADD INDEX IF NOT EXISTS idx_info_type (info_type),
ADD INDEX IF NOT EXISTS idx_source (source);

-- Modify chat_id to allow NULL (for web form submissions)
ALTER TABLE reports MODIFY COLUMN chat_id BIGINT NULL;

-- Modify description to allow NULL (since we have more specific fields now)
ALTER TABLE reports MODIFY COLUMN description TEXT NULL;