-- ============================================
-- SCHEMA PARA BOT DE INSPECCIONES MUNICIPALES
-- Base de datos: tramify_uoctbot
-- ============================================

-- Tabla para manejar sesiones de usuario (estados del flujo)
CREATE TABLE IF NOT EXISTS user_sessions (
    user_id BIGINT NOT NULL,
    chat_id BIGINT NOT NULL,
    state VARCHAR(50) NOT NULL DEFAULT 'awaiting_type',
    inspection_type VARCHAR(50) DEFAULT NULL,
    username VARCHAR(255) DEFAULT NULL,
    first_name VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, chat_id),
    INDEX idx_state (state),
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla para almacenar las inspecciones reportadas
CREATE TABLE IF NOT EXISTS inspections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    chat_id BIGINT NOT NULL,
    username VARCHAR(255) DEFAULT NULL,
    first_name VARCHAR(255) DEFAULT NULL,
    inspection_type VARCHAR(50) NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_type (inspection_type),
    INDEX idx_created (created_at),
    INDEX idx_location (latitude, longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verificar tablas creadas
SHOW TABLES LIKE 'user_sessions';
SHOW TABLES LIKE 'inspections';