# Smart Retail Siting & Data Engine

A production-grade, local implementation of an AI-powered retail location intelligence system. The system runs completely locally with zero cloud billing dependencies, using open-source infrastructure to simulate enterprise spatial data capabilities.

## 🏗️ Architecture

### Technical Stack
- **Backend**: Python 3.11+ with FastAPI (asynchronous execution)
- **AI Orchestration**: LangChain for LLM integration
- **Database**: SQLite with SpatiaLite extension for spatial operations
- **MCP Server**: Local Model Context Protocol implementation for database tool contracts
- **LLM Core**: Ollama (local) or Google Gemini API (free tier)
- **Frontend**: Responsive dark-mode dashboard with Tailwind CSS and Chart.js
- **Real-time**: WebSocket/SSE for live log streaming

### Core Components

#### 1. Local Database & Seed Pipeline
- SQLite database with SpatiaLite extension for geospatial operations
- Automated ingestion pipeline for mock spatial datasets
- Tables: transit stations, commerce points, storefront locations
- Spatial queries using Haversine formula for distance calculations

#### 2. Local MCP Server
- `get_schema`: Returns database schema and table structures
- `execute_query`: Executes SQL queries with security validation
- `get_spatial_examples`: Provides example spatial analysis queries
- `get_table_schema`: Detailed schema for specific tables

#### 3. Agentic Execution Controller
Four-phase execution loop:
- **Phase A**: Schema inspection and database learning
- **Phase B**: LLM generates optimized SQL queries
- **Phase C**: Query execution with raw data capture
- **Phase D**: LLM generates strategic business insights

#### 4. Real-time Frontend
- Live WebSocket connection for streaming logs
- Interactive dashboard with phase progress tracking
- Chart.js visualization of spatial analysis results
- Database statistics and monitoring

## 🚀 Quick Start

### Prerequisites
- Python 3.11 or higher
- Ollama (recommended) or Google Gemini API key
- Git

### Installation

#### Windows
```bash
# Run the setup script
setup.bat

# Or manually
python -m venv venv
venv\Scripts\activate
pip install -r requirements.txt
python run.py setup
```

#### Linux/Mac
```bash
# Make setup script executable
chmod +x setup.sh

# Run setup
./setup.sh

# Or manually
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
python3 run.py setup
```

### Configuration

Edit the `.env` file to configure your settings:

```env
# LLM Configuration
LLM_PROVIDER=OLLAMA  # or GEMINI

# Ollama Configuration (recommended)
OLLAMA_BASE_URL=http://localhost:11434
OLLAMA_MODEL=llama3

# Google Gemini Configuration (alternative)
GEMINI_API_KEY=your_api_key_here
GEMINI_MODEL=gemini-pro

# Database Configuration
DATABASE_PATH=data/retail_siting.db

# Server Configuration
HOST=0.0.0.0
PORT=8000
LOG_LEVEL=INFO
```

### Running Ollama (Recommended)

1. Install Ollama from [ollama.ai](https://ollama.ai)
2. Start the Ollama service:
   ```bash
   ollama serve
   ```
3. Pull the model:
   ```bash
   ollama pull llama3
   ```

### Starting the Application

```bash
# Development mode (setup + run)
python run.py dev

# Or run directly
python run.py run
```

The application will be available at:
- Backend API: http://localhost:8000
- Frontend Dashboard: http://localhost:8000/static/index.html
- API Documentation: http://localhost:8000/docs

## 📊 Usage

### Running Analysis

1. Open the frontend dashboard in your browser
2. Enter your analysis request in the text area, for example:
   > "Identify the top 3 high-volume cyclist transit points for a new coffee shop store layout expansion"
3. Select your LLM provider (Ollama or Gemini)
4. Click "Run Analysis"
5. Watch the real-time execution progress in the console
6. View the results and business insights

### API Endpoints

- `GET /health` - Health check
- `GET /api/schema` - Get database schema
- `POST /api/analyze` - Run analysis request
- `GET /api/status` - Get execution status
- `POST /api/ingest-data` - Trigger data ingestion
- `WS /ws` - WebSocket for real-time logs
- `GET /api/logs` - Get recent logs
- `GET /api/logs/stream` - SSE log streaming

### Example API Usage

```bash
# Get database schema
curl http://localhost:8000/api/schema

# Run analysis
curl -X POST http://localhost:8000/api/analyze \
  -H "Content-Type: application/json" \
  -d '{"prompt": "Find top 5 high-traffic transit stations", "llm_provider": "ollama"}'

# Check execution status
curl http://localhost:8000/api/status
```

## 🧪 Testing

Run the test suite:

```bash
# Run all tests
pytest tests/

# Run specific test file
pytest tests/test_database.py

# Run with coverage
pytest tests/ --cov=backend --cov-report=html
```

## 📁 Project Structure

```
Retail-MCP-Siting-Engine/
├── backend/
│   ├── app.py              # FastAPI server with WebSocket
│   ├── database.py         # SQLite + SpatiaLite database
│   ├── data_ingestion.py   # Mock data generation pipeline
│   ├── mcp_server.py       # MCP server implementation
│   └── agent_controller.py # Agentic execution controller
├── data/
│   └── retail_siting.db    # Local database (created on init)
├── frontend/
│   ├── index.html          # Main dashboard
│   └── app.js              # Frontend JavaScript
├── tests/
│   ├── test_database.py    # Database tests
│   └── test_mcp_server.py  # MCP server tests
├── requirements.txt        # Python dependencies
├── run.py                 # Main setup and run script
├── setup.bat              # Windows setup script
├── setup.sh               # Linux/Mac setup script
├── .env.example           # Environment configuration template
└── README.md              # This file
```

## � Features

### Database Capabilities
- **Transit Stations**: Bike transit routes with capacity scores
- **Commerce Points**: Business locations with revenue metrics
- **Storefronts**: Potential retail locations with accessibility scores
- **Spatial Queries**: Distance calculations, nearby points, spatial joins

### AI/LLM Integration
- **Multi-Provider Support**: Ollama (local) or Google Gemini
- **Dynamic SQL Generation**: LLM writes optimized queries based on schema
- **Business Intelligence**: Strategic insights from query results
- **Error Handling**: Graceful failure with informative messages

### Real-time Monitoring
- **Live Logs**: WebSocket streaming of application logs
- **Phase Tracking**: Visual progress through execution phases
- **Status Updates**: Real-time execution status and progress
- **Error Reporting**: Immediate feedback on failures

## 🛡️ Security

- SQL injection protection via query validation
- Dangerous SQL keyword blocking (DROP, DELETE, etc.)
- Environment variable configuration for sensitive data
- No cloud dependencies - everything runs locally
- No data leaves your machine

## ⚙️ Configuration Options

### LLM Providers

**Ollama (Recommended)**
- Completely free and local
- Requires Ollama installation
- Supports multiple models (llama3, mistral, etc.)
- No API keys needed

**Google Gemini**
- Free tier available
- Requires API key
- Good alternative if Ollama is not available
- Configure via `.env` file

### Database Options

**SQLite + SpatiaLite (Default)**
- Lightweight, serverless
- Built-in spatial extensions
- Portable database file
- No additional setup required

**DuckDB (Optional)**
- Alternative database engine
- Included in requirements
- Can be configured in code

## 🐛 Troubleshooting

### Ollama Connection Issues
```bash
# Check if Ollama is running
curl http://localhost:11434/api/tags

# Start Ollama service
ollama serve

# Pull required model
ollama pull llama3
```

### Database Issues
```bash
# Reinitialize database
rm data/retail_siting.db
python run.py setup
```

### Port Already in Use
```bash
# Change port in .env
PORT=8001

# Or kill the process using the port
# Windows
netstat -ano | findstr :8000
taskkill /PID <PID> /F

# Linux/Mac
lsof -ti:8000 | xargs kill -9
```

### Dependency Issues
```bash
# Create fresh virtual environment
python -m venv venv
source venv/bin/activate  # or venv\Scripts\activate on Windows
pip install --upgrade pip
pip install -r requirements.txt
```

## 📈 Performance

- **Database Query**: < 100ms for typical spatial queries
- **LLM Generation**: 2-10 seconds depending on query complexity
- **Total Analysis**: 5-30 seconds for complete 4-phase execution
- **Memory Usage**: ~500MB for Python + LLM
- **Disk Usage**: ~10MB for database with mock data

## 🤝 Contributing

This is a demonstration project for local AI-powered spatial analysis. Feel free to extend it with:

- Additional spatial analysis functions
- More sophisticated mock data generation
- Support for other LLM providers
- Enhanced frontend visualizations
- Additional database backends

## 📄 License

This project is provided as-is for educational and demonstration purposes.

## 🙏 Acknowledgments

- **FastAPI** - Modern, fast web framework
- **LangChain** - LLM orchestration framework
- **Ollama** - Local LLM runtime
- **SpatiaLite** - Spatial extensions for SQLite
- **Chart.js** - Data visualization library
- **Tailwind CSS** - Utility-first CSS framework

## 📞 Support

For issues and questions:
1. Check the troubleshooting section
2. Review the logs in the frontend console
3. Ensure all prerequisites are installed
4. Verify configuration in `.env` file

---

**Built with ❤️ for local, privacy-first AI applications**