-- ===============================================
-- NARCOTICS HELPLINE BOT DATABASE SCHEMA
-- ===============================================

-- Create database (if not exists)
CREATE DATABASE IF NOT EXISTS halohost_ndps 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE halohost_ndps;

-- ===============================================
-- TABLE: user_states
-- Stores user session information and preferences
-- ===============================================

CREATE TABLE IF NOT EXISTS user_states (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL UNIQUE,
    language VARCHAR(5) DEFAULT 'en',
    state VARCHAR(50) DEFAULT 'menu',
    report_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_chat_id (chat_id),
    INDEX idx_state (state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- TABLE: reports
-- Stores all submitted reports
-- ===============================================

CREATE TABLE IF NOT EXISTS reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT,
    reference_id VARCHAR(50) NOT NULL UNIQUE,
    input_number VARCHAR(50),
    input_date DATETIME,
    input_location VARCHAR(500),
    product_type VARCHAR(100),
    suspect_name_address TEXT,
    mobile_email VARCHAR(255),
    info_type VARCHAR(50),
    suspect_vehicle TEXT,
    special_note TEXT,
    caller_name_address TEXT,
    caller_mobile VARCHAR(50),
    photo_url VARCHAR(500),
    description TEXT,
    location VARCHAR(500),
    suspect_info TEXT,
    status VARCHAR(50) DEFAULT 'pending',
    source VARCHAR(20) DEFAULT 'telegram',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_chat_id (chat_id),
    INDEX idx_reference_id (reference_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_product_type (product_type),
    INDEX idx_info_type (info_type),
    INDEX idx_source (source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- TABLE: bot_logs
-- Stores bot activity logs for debugging
-- ===============================================

CREATE TABLE IF NOT EXISTS bot_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT,
    log_type VARCHAR(50),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_chat_id (chat_id),
    INDEX idx_log_type (log_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- TABLE: report_updates
-- Tracks status updates and notes for reports
-- ===============================================

CREATE TABLE IF NOT EXISTS report_updates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_id INT NOT NULL,
    status VARCHAR(50),
    notes TEXT,
    updated_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_report_id (report_id),
    FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- TABLE: user_feedback
-- Stores user feedback and ratings
-- ===============================================

CREATE TABLE IF NOT EXISTS user_feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    feedback_type VARCHAR(50),
    rating INT,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_chat_id (chat_id),
    INDEX idx_feedback_type (feedback_type),
    FOREIGN KEY (chat_id) REFERENCES user_states(chat_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- SAMPLE QUERIES FOR ADMIN PANEL
-- ===============================================

-- Get all pending reports
-- SELECT r.*, u.language 
-- FROM reports r 
-- JOIN user_states u ON r.chat_id = u.chat_id 
-- WHERE r.status = 'pending' 
-- ORDER BY r.created_at DESC;

-- Get report statistics
-- SELECT 
--     status, 
--     COUNT(*) as count 
-- FROM reports 
-- GROUP BY status;

-- Get user activity
-- SELECT 
--     DATE(created_at) as date, 
--     COUNT(*) as reports 
-- FROM reports 
-- GROUP BY DATE(created_at) 
-- ORDER BY date DESC;

-- ===============================================
-- INSERT SAMPLE DATA (FOR TESTING)
-- ===============================================

-- Uncomment below to insert test data

-- INSERT INTO user_states (chat_id, language, state) VALUES
-- (123456789, 'en', 'menu'),
-- (987654321, 'gu', 'menu');

-- ===============================================
-- MAINTENANCE QUERIES
-- ===============================================

-- Clear old bot logs (older than 30 days)
-- DELETE FROM bot_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Reset user states (if needed)
-- UPDATE user_states SET state = 'menu', report_data = NULL WHERE state != 'menu';

-- ===============================================
-- INDEXES FOR PERFORMANCE
-- ===============================================

-- Additional indexes can be added based on query patterns
-- ALTER TABLE reports ADD INDEX idx_chat_created (chat_id, created_at);
-- ALTER TABLE user_states ADD INDEX idx_lang_state (language, state);

-- ===============================================
-- GRANTS (Update with your actual username)
-- ===============================================

-- GRANT SELECT, INSERT, UPDATE, DELETE ON halohost_ndps.* TO 'halohost_ndps'@'localhost';
-- FLUSH PRIVILEGES;

-- ===============================================
-- BACKUP RECOMMENDATION
-- ===============================================

-- Regular backup command:
-- mysqldump -u halohost_ndps -p halohost_ndps > backup_$(date +%Y%m%d).sql

-- Restore command:
-- mysql -u halohost_ndps -p halohost_ndps < backup_YYYYMMDD.sql