# Shopify Order Management & Kalimera Integration System

A Flask-based web application that receives Shopify order webhooks, stores order data in SQLite, and automatically syncs orders to the Kalimera platform for automated calling campaigns. The system includes a dashboard for viewing orders, status management, and detailed order logs.

---

## 📋 Table of Contents

- [Overview](#overview)
- [Architecture](#architecture)
- [Features](#features)
- [Technology Stack](#technology-stack)
- [Project Structure](#project-structure)
- [Setup & Installation](#setup--installation)
- [Configuration](#configuration)
- [API Endpoints](#api-endpoints)
- [Database Schema](#database-schema)
- [Workflow & Data Flow](#workflow--data-flow)
- [External APIs](#external-apis)
- [Usage Examples](#usage-examples)
- [Troubleshooting](#troubleshooting)

---

## 🎯 Overview

This system acts as a bridge between Shopify and Kalimera:

1. **Receives** real-time order webhooks from Shopify
2. **Stores** order data in a local SQLite database
3. **Automatically syncs** orders to Kalimera for automated calling campaigns
4. **Provides** a web dashboard to view, filter, and manage orders
5. **Manages** order statuses (Placed, Success, Returned, Failed)
6. **Handles** order cancellation in Shopify when status is set to "Failed"

### Key Capabilities

- ✅ Real-time webhook processing with HMAC verification
- ✅ Automatic Kalimera contact creation and campaign assignment
- ✅ Multi-language agent support (English/Hindi based on region)
- ✅ Order status tracking and management
- ✅ Comprehensive activity logging
- ✅ RESTful API for programmatic access
- ✅ Responsive web dashboard with status filtering

---

## 🏗️ Architecture

┌─────────────┐
│   Shopify   │
│   Store     │
└──────┬──────┘
       │ Webhook (POST /shopify/webhook)
       │ HMAC Verified
       ▼
┌─────────────────────────────────────┐
│         Flask Application           │
│  ┌──────────────────────────────┐  │
│  │   Webhook Handler            │  │
│  │   - Verify HMAC             │  │
│  │   - Parse Order Data         │  │
│  │   - Save to Database         │  │
│  └───────────┬──────────────────┘  │
│              │                       │
│  ┌───────────▼──────────────────┐  │
│  │   Sync Service                │  │
│  │   - Check existing contact    │  │
│  │   - Create/Reuse contact     │  │
│  │   - Update campaign           │  │
│  │   - Add to campaign list      │  │
│  └───────────┬──────────────────┘  │
└──────────────┼──────────────────────┘
               │
       ┌───────┴────────┐
       │                │
       ▼                ▼
┌─────────────┐  ┌──────────────┐
│   SQLite    │  │   Kalimera   │
│  Database   │  │     API      │
└─────────────┘  └──────────────┘
       │
       │ Dashboard Queries
       ▼
┌─────────────────────┐
│   Web Dashboard     │
│   - View Orders     │
│   - Filter by Status│
│   - View Logs       │
└─────────────────────┘

### Component Breakdown

1. **Flask Application (`app.py`)**
- Main application entry point
- Route handlers for webhooks, dashboard, and APIs
- Database connection management
- Authentication (session-based)

2. **Configuration (`config.py`)**
- Centralized settings management
- Environment variable loading
- Default values for development

3. **Services Layer (`services/`)**
- `kalimera_service.py`: Kalimera API integration
- `shopify_service.py`: Shopify API operations
- `sync_service.py`: Orchestrates the sync workflow

4. **Database (`shopify_orders.db`)**
- SQLite database with two tables: `orders` and `logs`

5. **Templates (`templates/`)**
- Jinja2 templates for dashboard UI
- Responsive design with Tailwind CSS

---

## ✨ Features

### Order Management
- Real-time order capture from Shopify webhooks
- Order status tracking (Placed, Success, Returned, Failed)
- Product title extraction and storage
- Customer information management

### Kalimera Integration
- Automatic contact creation in Kalimera
- Campaign assignment with dynamic time windows
- Multi-language agent selection (English/Hindi)
- Idempotent sync (handles duplicate webhooks gracefully)

### Dashboard
- Live order monitoring
- Status-based filtering
- Revenue metrics
- Order detail views with activity logs
- Click-to-view order details

### API Access
- RESTful endpoints for order management
- Status update API
- Manual sync triggers
- Latest order retrieval

---

## 🛠️ Technology Stack

- **Backend**: Python 3.12, Flask 3.1.2
- **Database**: SQLite3
- **Frontend**: HTML5, Tailwind CSS (CDN), JavaScript
- **APIs**:
- Shopify Admin API (REST)
- Kalimera API (REST)
- **Security**: HMAC-SHA256 webhook verification
- **Dependencies**:
- `flask` - Web framework
- `requests` - HTTP client
- `python-dotenv` - Environment variable management

---

## 📁 Project Structure

shopify-project/
├── app.py                      # Main Flask application
├── config.py                   # Configuration management
├── .env                        # Environment variables (not in git)
├── shopify_orders.db           # SQLite database
├── README.md                   # This file
│
├── services/                   # Service layer
│   ├── __init__.py
│   ├── kalimera_service.py    # Kalimera API integration
│   ├── shopify_service.py      # Shopify API operations
│   └── sync_service.py         # Sync orchestration
│
└── templates/                  # HTML templates
    ├── dashboard.html          # Main dashboard
    ├── login.html             # Login page
    ├── order-detail.html      # Order detail view
    └── status-report.html     # Status-filtered reports

---

## 🚀 Setup & Installation

### Prerequisites

- Python 3.12 or higher
- pip (Python package manager)
- Access to Shopify store (for webhook configuration)
- Kalimera API credentials
- Shopify Admin API access token

### Installation Steps

1. **Clone or navigate to the project directory**

   cd shopify-project

2. **Create a virtual environment**

   python3 -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate

3. **Install dependencies**

   pip install flask requests python-dotenv

4. **Configure environment variables**

   cp .env.example .env  # If you have an example file
   # Or create .env manually (see Configuration section)

5. **Initialize the database**

   python app.py
   # The database will be created automatically on first run

6. **Run the application**

   python app.py
   # Or: flask run

7. **Access the dashboard**
- Open browser: `http://localhost:5000`
- Login: `admin` / `admin` (default credentials)

---

## ⚙️ Configuration

### Environment Variables (`.env` file)

Create a `.env` file in the project root with the following variables:

# Flask Configuration
FLASK_SECRET_KEY=""

# Shopify Webhook Security
SHOPIFY_SHARED_SECRET=

# Kalimera API Configuration
KALIMERA_CONTACT_URL=https://api.my-buddy.ai/kalimera/contact
KALIMERA_CAMPAIGN_ID=
KALIMERA_CAMPAIGN_URL=https://api.my-buddy.ai/kalimera/campaign/ef51958e-7f2f-4f19-8bca-4707dbbcb9f2
KALIMERA_CAMPAIGN_CONTACTS_URL=https://api.my-buddy.ai/kalimera/campaign/contacts
KALIMERA_AUTH_TOKEN="Bearer YOUR_KALIMERA_TOKEN_HERE"
KALIMERA_AGENT_ID_EN=
KALIMERA_AGENT_ID_HI=
KALIMERA_SIP_LIST_ID=

# Shopify API Configuration
SYNC_API_TOKEN=your-sync-api-token
SHOPIFY_STORE_DOMAIN=your-store-name
SHOPIFY_API_VERSION=2024-07
SHOPIFY_ACCESS_TOKEN=shpat_your_access_token_here

# Database
DATABASE_PATH=shopify_orders.db

### Configuration Notes

- **SHOPIFY_SHARED_SECRET**: Found in Shopify Admin → Settings → Notifications → Webhooks
- **KALIMERA_AUTH_TOKEN**: Bearer token from Kalimera API
- **SHOPIFY_ACCESS_TOKEN**: Admin API access token from Shopify
- **AGENT_ID_EN/HI**: Different agents for English and Hindi language support

---

## 🔌 API Endpoints

### Web Routes (UI)

| Method | Endpoint | Description | Auth Required |
|--------|----------|-------------|---------------|
| GET | `/` | Main dashboard | ✅ Yes |
| GET | `/login` | Login page | ❌ No |
| POST | `/login` | Authenticate user | ❌ No |
| GET | `/logout` | Logout user | ✅ Yes |
| GET | `/reports/<status_slug>` | Status-filtered report page | ✅ Yes |
| GET | `/orders/<shopify_id>` | Order detail view with logs | ✅ Yes |

### API Endpoints (JSON)

| Method | Endpoint | Description | Auth Required |
|--------|----------|-------------|---------------|
| POST | `/shopify/webhook` | Receive Shopify webhooks | ❌ No (HMAC verified) |
| POST | `/api/orders/<shopify_id>/sync` | Manually sync order to Kalimera | ✅ Header: `X-App-Token` |
| POST | `/api/orders/sync-latest` | Sync latest "Placed" order | ❌ No |
| GET | `/api/orders/latest` | Get latest "Placed" order data | ❌ No |
| POST | `/api/orders/<shopify_id>/status` | Update order status | ❌ No |

### Endpoint Details

#### `POST /shopify/webhook`
Receives order creation webhooks from Shopify.

**Headers Required:**
- `X-Shopify-Hmac-SHA256`: HMAC signature for verification
- `X-Shopify-Topic`: Webhook topic (e.g., `orders/create`)
- `X-Shopify-Shop-Domain`: Shopify store domain

**Response:** `200 OK` or `403 Unauthorized`

---

#### `POST /api/orders/<shopify_id>/sync`
Manually trigger Kalimera sync for a specific order.

**Headers:**

X-App-Token: your-sync-api-token
Content-Type: application/json

**Response:**

{
  "success": true,
  "message": "Order synced to Kalimera successfully.",
  "shopify_id": 123456789,
  "contact_id": "uuid-here"
}

---

#### `POST /api/orders/<shopify_id>/status`
Update the confirmation status of an order.

**Request Body:**

{
  "status": "Success"
}

**Valid Statuses:** `Placed`, `Success`, `Returned`, `Failed`

**Response:**

{
  "success": true,
  "shopify_id": 123456789,
  "previous_status": "Placed",
  "new_status": "Success",
  "shopify_cancelled": false
}

**Note:** If status is set to `Failed`, the system automatically cancels the order in Shopify.

---

#### `GET /api/orders/latest`
Retrieve the most recently inserted order with status "Placed".

**Response:**

{
  "success": true,
  "order": {
    "shopify_id": 123456789,
    "order_name": "#1001",
    "product_title": "Product Name",
    "total_price": 99.99,
    "currency": "USD",
    ...
  }
}

---

## 🗄️ Database Schema

### `orders` Table

| Column | Type | Description |
|--------|------|-------------|
| `shopify_id` | INTEGER PRIMARY KEY | Shopify order ID |
| `order_name` | TEXT | Order name (e.g., "#1001") |
| `product_title` | TEXT | Product title from order |
| `total_price` | REAL | Order total amount |
| `currency` | TEXT | Currency code (USD, INR, etc.) |
| `customer_email` | TEXT | Customer email address |
| `customer_phone` | TEXT | Customer phone number |
| `created_at` | TEXT | Order creation date |
| `confirmation_status` | TEXT | Status: Placed, Success, Returned, Failed |
| `webhook_timestamp` | REAL | Unix timestamp of webhook receipt |
| `first_name` | TEXT | Customer first name |
| `last_name` | TEXT | Customer last name |
| `address` | TEXT | Shipping address line 1 |
| `city` | TEXT | Shipping city |
| `state` | TEXT | Shipping state/province |

### `logs` Table

| Column | Type | Description |
|--------|------|-------------|
| `id` | INTEGER PRIMARY KEY | Auto-increment log ID |
| `shopify_id` | INTEGER | Foreign key to orders |
| `timestamp` | TEXT | ISO format timestamp |
| `event_type` | TEXT | Event type (e.g., "Kalimera_Contact_Created") |
| `message` | TEXT | Log message/details |

**Common Event Types:**
- `Shopify_Webhook_Received`
- `Kalimera_Contact_Created`
- `Kalimera_Campaign_Updated`
- `Kalimera_Contact_Added_To_List`
- `Kalimera_Sync_Success`
- `Order_Status_Updated`
- `Shopify_Cancel_Success`

---

## 🔄 Workflow & Data Flow

### 1. Order Creation Flow

Shopify Order Created
    │
    ▼
Webhook Sent to /shopify/webhook
    │
    ▼
HMAC Verification
    │
    ▼
Parse Order Data
    │
    ▼
Save to SQLite Database
    │
    ├─► Insert into `orders` table
    └─► Log: "Shopify_Webhook_Received"
    │
    ▼
Check for Existing Kalimera Contact
    │
    ├─► If exists: Reuse contact ID
    └─► If not: Create new contact
    │
    ▼
Sync to Kalimera (3 Steps)
    │
    ├─► Step 1: Create Contact
    │   └─► Log: "Kalimera_Contact_Created"
    │
    ├─► Step 2: Update Campaign
    │   ├─► Determine Agent (EN/HI based on state)
    │   ├─► Set call window (current time + 2 hours)
    │   └─► Log: "Kalimera_Campaign_Updated"
    │
    └─► Step 3: Add Contact to Campaign
        └─► Log: "Kalimera_Contact_Added_To_List"
    │
    ▼
Log: "Kalimera_Sync_Success"

### 2. Status Update Flow

API Call: POST /api/orders/<id>/status
    │
    ▼
Validate Status (Placed/Success/Returned/Failed)
    │
    ▼
Update Database
    │
    ├─► UPDATE orders SET confirmation_status = ?
    └─► Log: "Order_Status_Updated"
    │
    ▼
If Status = "Failed"
    │
    ▼
Cancel Order in Shopify
    │
    ├─► POST to Shopify Admin API
    ├─► /orders/{id}/cancel.json
    └─► Log: "Shopify_Cancel_Success" or "Shopify_Cancel_Error"

### 3. Agent Selection Logic

The system automatically selects the appropriate Kalimera agent based on the order's shipping state:

if state.lower() in ['gujarat', 'gj', 'gujrat']:
    agent_id = KALIMERA_AGENT_ID_HI  # Hindi agent
else:
    agent_id = KALIMERA_AGENT_ID_EN  # English agent

---

## 🌐 External APIs

### Shopify Admin API

**Base URL:** `https://{store}.myshopify.com/admin/api/{version}/`

#### Endpoints Used

1. **Cancel Order**
- `POST /orders/{order_id}/cancel.json`
- **Headers:**
- `X-Shopify-Access-Token`: Admin API token
- `Content-Type`: application/json
- **Payload:**

     {
       "email": true,
       "restock": true,
       "reason": "other"
     }

### Kalimera API

**Base URL:** `https://api.my-buddy.ai/kalimera/`

#### Endpoints Used

1. **Create Contact**
- `POST /contact`
- **Headers:**
- `Authorization`: Bearer token
- `Content-Type`: application/json
- **Payload:** Contact details (name, phone, email, address, etc.)

2. **Update Campaign**
- `PUT /campaign/{campaign_id}`
- **Headers:** Same as above
- **Payload:** Campaign configuration (agent_id, time window, etc.)

3. **Add Contact to Campaign**
- `POST /campaign/contacts`
- **Headers:** Same as above
- **Payload:** Campaign IDs and contact IDs

---

## 💡 Usage Examples

### 1. Testing Webhook Locally

Use a tool like `ngrok` to expose your local server:

# Terminal 1: Run Flask app
python app.py

# Terminal 2: Expose with ngrok
ngrok http 5000

# Use the ngrok URL in Shopify webhook settings:
# https://your-ngrok-url.ngrok.io/shopify/webhook

### 2. Manual Order Sync

curl -X POST http://localhost:5000/api/orders/7274738843834/sync \
  -H 'X-App-Token: your-sync-api-token' \
  -H 'Content-Type: application/json'

### 3. Update Order Status

curl -X POST http://localhost:5000/api/orders/7274738843834/status \
  -H 'Content-Type: application/json' \
  -d '{"status":"Success"}'

### 4. Get Latest Order

curl http://localhost:5000/api/orders/latest

### 5. Sync Latest Placed Order

curl -X POST http://localhost:5000/api/orders/sync-latest

---

## 🔍 Troubleshooting

### Common Issues

#### 1. Webhook Returns 403 Unauthorized

**Problem:** HMAC verification failing

**Solutions:**
- Verify `SHOPIFY_SHARED_SECRET` in `.env` matches Shopify webhook settings
- Check that the webhook URL is correct
- Ensure the raw request body is used for HMAC calculation (not parsed JSON)

#### 2. Kalimera Contact Creation Fails (400 Error)

**Problem:** Contact already exists or invalid data

**Solutions:**
- The system now handles duplicate contacts automatically (idempotent)
- Check that phone/email fields are valid
- Verify `KALIMERA_AUTH_TOKEN` is correct and not expired

#### 3. Database Lock Errors

**Problem:** SQLite database locked

**Solutions:**
- Ensure only one instance of the app is running
- Check for long-running database transactions
- Restart the application

#### 4. Shopify Cancel Order Fails (401)

**Problem:** Invalid access token

**Solutions:**
- Verify `SHOPIFY_ACCESS_TOKEN` in `.env`
- Ensure the token has "Order editing" permissions
- Check that the token hasn't expired

#### 5. Agent Selection Not Working

**Problem:** Wrong agent ID being used

**Solutions:**
- Check the order's `state` field in the database
- Verify `KALIMERA_AGENT_ID_EN` and `KALIMERA_AGENT_ID_HI` in `.env`
- Check logs for "Agent_Selected" entries

### Debug Mode

Enable Flask debug mode for detailed error messages:

# In app.py, change:
app.run(debug=True, port=5000)

### Viewing Logs

All activity is logged in the `logs` table. View logs for a specific order:

SELECT * FROM logs WHERE shopify_id = 7274738843834 ORDER BY timestamp DESC;

Or via the dashboard: Click on any order row to view its detailed logs.

---

## 📝 Notes

- **Idempotency**: The system is designed to handle duplicate webhooks gracefully. If a contact already exists for an order, it will be reused instead of creating a duplicate.

- **Security**:
- Webhooks are verified using HMAC-SHA256
- API endpoints can be protected with tokens (configured via `SYNC_API_TOKEN`)
- Session-based authentication for dashboard access

- **Scalability**:
- SQLite is suitable for small to medium workloads
- For production with high volume, consider migrating to PostgreSQL or MySQL
- Consider adding connection pooling for external APIs

- **Time Zones**:
- Campaign time windows use "Asia/Kolkata" timezone
- Adjust in `services/kalimera_service.py` if needed

---

## 👥 Support

For issues or questions, please contact the development team or refer to the project documentation.

---