# ⚡ Sequencer Pro — Product Sequencing App for Shopify

A Shopify embedded app that lets merchants reorder products in collections by uploading an Excel/CSV file with SKU-to-collection mappings. Includes Razorpay subscription billing with monthly plans.

---

## Features

- **Excel/CSV Upload** — Upload a spreadsheet with SKU + Collection Handle columns to auto-reorder products
- **Razorpay Subscriptions** — Monthly billing with Free, Basic, and Pro plans
- **Usage Tracking** — Monthly SKU limits per plan with real-time usage display
- **Billing Dashboard** — View current plan, payment history, and switch plans
- **Activity Logs** — Full audit trail of all sequencing operations
- **GDPR Compliant** — Shopify-required data erasure webhooks included

---

## Directory Structure

```
product-sequencing/
├── index.php                    # Main app (sidebar + pages)
├── config.php                   # All credentials & DB connection
├── helpers.php                  # Shared utility functions
├── install.php                  # Shopify OAuth initiation
├── callback.php                 # OAuth callback + token exchange
├── composer.json                # PHP dependencies
├── .htaccess                    # Apache security rules
│
├── api/
│   ├── process_upload.php       # Excel upload → Shopify reorder
│   └── check_subscription.php   # Returns current plan + usage
│
├── razorpay/
│   ├── create_subscription.php  # Creates Razorpay subscription
│   ├── verify_payment.php       # Post-checkout payment verification
│   ├── cancel_subscription.php  # Cancel active subscription
│   └── webhook.php              # Razorpay webhook handler
│
├── webhooks/
│   ├── app_uninstalled.php      # Shopify app/uninstalled webhook
│   └── gdpr.php                 # GDPR data request/erasure
│
├── assets/
│   ├── style.css                # App stylesheet
│   └── app.js                   # Frontend JavaScript
│
└── database/
    └── schema.sql               # Complete MySQL schema
```

---

## Database Schema

### Tables Overview

| Table | Purpose |
|---|---|
| `stores` | One row per installed Shopify store (domain, access token, status) |
| `plans` | Subscription plans (Free, Basic, Pro) with limits and Razorpay plan IDs |
| `subscriptions` | Active/past subscriptions linking stores to plans via Razorpay |
| `payments` | Every Razorpay payment event logged with amount, method, status |
| `sequencing_logs` | Audit trail of every upload with per-SKU results |
| `usage_tracker` | Monthly SKU usage counters per store (for plan limits) |
| `webhooks_log` | Raw Razorpay webhook payloads for debugging |
| `app_sessions` | Session tokens for embedded app authentication |

### Key Relationships

```
stores (1) ──→ (many) subscriptions ──→ (1) plans
stores (1) ──→ (many) payments
stores (1) ──→ (many) sequencing_logs
stores (1) ──→ (many) usage_tracker
```

---

## Setup Instructions

### 1. Server Requirements

- PHP 7.4+ with extensions: curl, json, pdo_mysql, mbstring, zip, gd
- MySQL 5.7+ or MariaDB 10.3+
- Apache with mod_rewrite enabled
- SSL certificate (required for Shopify apps)
- Composer installed

### 2. Create Database

```bash
mysql -u root -p < database/schema.sql
```

### 3. Install Dependencies

```bash
composer install
```

### 4. Configure Credentials

Edit `config.php` and fill in:

```php
// Shopify (from Partner Dashboard → Apps → Your App)
define('SHOPIFY_API_KEY',    'your_api_key');
define('SHOPIFY_API_SECRET', 'your_api_secret');
define('SHOPIFY_APP_HANDLE', 'your-app-handle');

// Your hosting domain
define('APP_URL', 'https://your-domain.com');

// Database
define('DB_HOST', 'localhost');
define('DB_NAME', 'product_sequencing_app');
define('DB_USER', 'your_user');
define('DB_PASS', 'your_password');

// Razorpay (from Dashboard → Settings → API Keys)
define('RAZORPAY_KEY_ID',     'rzp_live_xxxxxxxxxxxx');
define('RAZORPAY_KEY_SECRET', 'your_secret');
define('RAZORPAY_WEBHOOK_SECRET', 'your_webhook_secret');
```

### 5. Shopify Partner Dashboard Setup

1. Create a Custom App in Partner Dashboard
2. Set **App URL**: `https://your-domain.com/index.php`
3. Set **Allowed redirection URL(s)**: `https://your-domain.com/callback.php`
4. Set **App scopes**: `read_products, write_products`
5. Register webhooks:
   - `app/uninstalled` → `https://your-domain.com/webhooks/app_uninstalled.php`
6. Set GDPR webhooks:
   - Customer data request → `https://your-domain.com/webhooks/gdpr.php`
   - Customer data erasure → `https://your-domain.com/webhooks/gdpr.php`
   - Shop data erasure → `https://your-domain.com/webhooks/gdpr.php`

### 6. Razorpay Dashboard Setup

1. Go to **Settings → Webhooks** in Razorpay Dashboard
2. Add webhook URL: `https://your-domain.com/razorpay/webhook.php`
3. Select events:
   - `subscription.activated`
   - `subscription.charged`
   - `subscription.cancelled`
   - `subscription.halted`
   - `subscription.pending`
   - `subscription.completed`
   - `subscription.expired`
   - `payment.captured`
4. Copy the webhook secret to `config.php`

---

## How It Works

### Upload Flow

1. Merchant uploads Excel/CSV with columns: `SKU`, `Collection Handle`
2. App groups SKUs by collection handle
3. For each collection:
   - Resolves collection handle → Shopify GID
   - Resolves each SKU → Product GID via Shopify GraphQL
   - Calls `collectionReorderProducts` mutation with position mappings
4. Results displayed in real-time table
5. Usage counter incremented

### Subscription Flow

1. Merchant clicks "Subscribe Now" on a plan
2. Server creates Razorpay Plan (if not exists) + Subscription
3. Razorpay Checkout opens in browser
4. After payment, signature verified server-side
5. Subscription marked `active` in database
6. Razorpay webhooks keep status synced (renewals, cancellations, failures)

### Access Control

- **Free plan**: Default for all stores, limited SKUs/collections per month
- **Paid plans**: Upload button always active, higher/unlimited limits
- **Expired/cancelled**: Falls back to free plan limits
- **Upload button disabled** when monthly limit reached on free plan

---

## Excel Template Format

| SKU | Collection Handle |
|---|---|
| SKU-001 | summer-collection |
| SKU-002 | summer-collection |
| SKU-003 | winter-sale |
| SKU-004 | winter-sale |

Products will be ordered 1st, 2nd, 3rd... in each collection based on their row order in the file.

---

## Default Plans

| Plan | Price | SKU Limit | Collections |
|---|---|---|---|
| Free | ₹0 | 50/month | 2 |
| Basic | ₹499/month | 500/month | 10 |
| Pro | ₹1,499/month | Unlimited | Unlimited |

Edit plans in the `plans` table or add new ones directly in the database.

---

## Production Checklist

- [ ] Set `APP_DEBUG` to `false` in config.php
- [ ] Use Razorpay live keys (replace `rzp_test_` with `rzp_live_`)
- [ ] Set strong DB password
- [ ] Enable SSL on your domain
- [ ] Test webhook delivery in Razorpay Dashboard
- [ ] Test full install → subscribe → upload → uninstall flow
- [ ] Set up cron to clean expired sessions: `DELETE FROM app_sessions WHERE expires_at < NOW()`
