-- ===============================================
-- UPDATE EXISTING DATABASE SCHEMA
-- Run this script to update your existing database
-- ===============================================

USE halohost_ndps;

-- ===============================================
-- STEP 1: Update reports table with new columns
-- ===============================================

-- Add new columns to reports table (if they don't exist)
-- Note: These will be NULL for existing records, which is fine

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

-- Modify existing columns to allow NULL (for backward compatibility)
ALTER TABLE reports 
MODIFY COLUMN chat_id BIGINT NULL,
MODIFY COLUMN description TEXT NULL;

-- Update existing records: Set source to 'telegram' for old records
UPDATE reports 
SET source = 'telegram' 
WHERE source IS NULL OR source = '';

-- Update existing records: Generate input_number for old records
UPDATE reports 
SET input_number = CONCAT(
    LPAD(DAY(created_at), 2, '0'), 
    '/', 
    YEAR(created_at)
)
WHERE input_number IS NULL OR input_number = '';

-- Update existing records: Set input_date from created_at if not set
UPDATE reports 
SET input_date = created_at 
WHERE input_date IS NULL;

-- Update existing records: Copy location to input_location if input_location is empty
UPDATE reports 
SET input_location = location 
WHERE (input_location IS NULL OR input_location = '') 
AND location IS NOT NULL AND location != '';

-- Update existing records: Copy suspect_info to suspect_name_address if empty
UPDATE reports 
SET suspect_name_address = suspect_info 
WHERE (suspect_name_address IS NULL OR suspect_name_address = '') 
AND suspect_info IS NOT NULL AND suspect_info != '';

-- ===============================================
-- STEP 2: Add indexes for new columns
-- ===============================================

-- Add indexes for better query performance
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);

-- ===============================================
-- STEP 3: Create login_activities table
-- ===============================================

CREATE TABLE IF NOT EXISTS login_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_type VARCHAR(20) NOT NULL COMMENT 'admin or form_user',
    username VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent TEXT,
    login_status VARCHAR(20) DEFAULT 'success' COMMENT 'success, failed, logout',
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_time TIMESTAMP NULL,
    session_duration INT COMMENT 'Duration in seconds',
    INDEX idx_user_type (user_type),
    INDEX idx_username (username),
    INDEX idx_login_time (login_time),
    INDEX idx_login_status (login_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- STEP 4: Create activity_logs table
-- ===============================================

CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_type VARCHAR(20) COMMENT 'admin, form_user, telegram',
    user_id VARCHAR(100) COMMENT 'username, chat_id, or user identifier',
    activity_type VARCHAR(50) NOT NULL COMMENT 'login, logout, form_submit, report_view, report_update, report_delete, etc.',
    activity_description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    related_id INT COMMENT 'Related report_id or other entity ID',
    metadata TEXT COMMENT 'JSON data for additional info',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_type (user_type),
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at),
    INDEX idx_related_id (related_id),
    INDEX idx_user_activity (user_type, activity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- STEP 5: Verify the updates
-- ===============================================

-- Check reports table structure
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'halohost_ndps' 
AND TABLE_NAME = 'reports'
ORDER BY ORDINAL_POSITION;

-- Check if new tables exist
SHOW TABLES LIKE 'login_activities';
SHOW TABLES LIKE 'activity_logs';

-- Count existing reports
SELECT COUNT(*) as total_reports FROM reports;

-- Count reports by source
SELECT source, COUNT(*) as count FROM reports GROUP BY source;

-- ===============================================
-- STEP 6: Optional - Backfill data for existing reports
-- ===============================================

-- If you want to set default values for existing records based on description field
-- (Uncomment and modify as needed)

/*
-- Try to extract product type from description (example)
UPDATE reports 
SET product_type = CASE
    WHEN description LIKE '%ganja%' OR description LIKE '%ગાંજો%' THEN 'ગાંજો'
    WHEN description LIKE '%charas%' OR description LIKE '%ચરસ%' THEN 'ચરસ'
    WHEN description LIKE '%opium%' OR description LIKE '%afim%' OR description LIKE '%અફીણ%' THEN 'અફીણ'
    WHEN description LIKE '%md%' OR description LIKE '%એમ.ડી%' THEN 'એમ.ડી'
    WHEN description LIKE '%codeine%' OR description LIKE '%કોડેઈન%' THEN 'કોડેઈન'
    ELSE NULL
END
WHERE product_type IS NULL AND description IS NOT NULL;
*/

-- ===============================================
-- MIGRATION COMPLETE
-- ===============================================

-- Summary message
SELECT 
    'Migration completed successfully!' as message,
    (SELECT COUNT(*) FROM reports) as total_reports,
    (SELECT COUNT(*) FROM login_activities) as login_records,
    (SELECT COUNT(*) FROM activity_logs) as activity_records;