# Subscription System Setup Guide

This guide will help you set up the subscription system with Stripe integration, including subscription plans management.

## Prerequisites

1. A Stripe account with API keys
2. Node.js and npm installed
3. Database (MySQL/PostgreSQL) configured

## Environment Variables

Add the following environment variables to your `.env` file:

```env
# Stripe Configuration
STRIPE_SECRET_KEY=sk_test_your_stripe_secret_key_here
STRIPE_PUBLISHABLE_KEY=pk_test_your_stripe_publishable_key_here
STRIPE_WEBHOOK_SECRET=whsec_your_webhook_secret_here
```

## Database Migration

Run the migration to create the required tables:

```bash
npm run migrate
```

Or if you have a specific migration command:

```bash
npx knex migrate:latest
```

This will create:
- `subscription_plans` - For storing subscription plan details
- `subscription_plan_features` - For storing plan features
- `subscriptions` - For storing user subscriptions
- `customers` - For storing customer information

### Migration Files Structure:

#### 1. Create Subscription Plans Table
```typescript
// migrations/20250701000100_create-plan.ts
export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('subscription_plans', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable();
    table.text('description');
    table.decimal('price', 10, 2).notNullable();
    table.string('currency', 3).defaultTo('USD');
    table.string('interval', 10); // 'monthly', 'yearly', 'lifetime'
    table.string('type', 10).notNullable(); // 'recurring', 'one-off', 'both'
    table.string('status', 20).defaultTo('active');
    table.string('stripeProductId').notNullable();
    table.string('stripeRecurringPriceId');
    table.string('stripeOneOffPriceId');
    table.timestamps(true, true);
    table.timestamp('deletedAt').nullable();
    table.integer('createdBy').nullable();
    table.integer('updatedBy').nullable();
    table.integer('deletedBy').nullable();
  });
}
```

#### 2. Create Subscription Plan Features Table
```typescript
// migrations/20250701000200_create-subscription-plan-features.ts
export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('subscription_plan_features', (table) => {
    table.increments('id').primary();
    table.integer('subscriptionPlanId').notNullable();
    table.string('name').notNullable();
    table.timestamps(true, true);
    table.timestamp('deletedAt').nullable();
    
    // Foreign key
    table.foreign('subscriptionPlanId')
      .references('id')
      .inTable('subscription_plans')
      .onDelete('CASCADE');
  });
}
```

#### 3. Create Customers Table
```typescript
// migrations/20250701000300_create-customer.ts
export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('customers', (table) => {
    table.increments('id').primary();
    table.string('firstName').notNullable();
    table.string('lastName').notNullable();
    table.string('email').notNullable().unique();
    table.string('password');
    table.string('phoneNumber', 20);
    table.string('status', 50).defaultTo('pending');
    table.string('stripeCustomerId');
    table.string('stripeSubscriptionId');
    table.string('stripePaymentIntentId');
    table.string('subscriptionStatus', 50).defaultTo('inactive');
    table.integer('planId').nullable();
    table.timestamps(true, true);
    table.timestamp('deletedAt').nullable();
    
    // Foreign key
    table.foreign('planId')
      .references('id')
      .inTable('subscription_plans');
  });
}
```

#### 4. Create Subscriptions Table
```typescript
// migrations/20250701000400_create-subscription.ts
export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('subscriptions', (table) => {
    table.increments('id').primary();
    table.integer('customerId').notNullable();
    table.string('stripeCustomerId').notNullable();
    table.string('stripeSubscriptionId').notNullable().unique();
    table.string('stripePriceId').notNullable();
    table.string('stripeProductId').notNullable();
    table.string('status', 50).notNullable().defaultTo('active');
    table.timestamp('currentPeriodStart').notNullable();
    table.timestamp('currentPeriodEnd').notNullable();
    table.boolean('cancelAtPeriodEnd').defaultTo(false);
    table.timestamp('canceledAt').nullable();
    table.timestamp('trialStart').nullable();
    table.timestamp('trialEnd').nullable();
    table.integer('quantity').defaultTo(1);
    table.json('metadata');
    table.timestamps(true, true);
    table.timestamp('deletedAt').nullable();
    table.integer('createdBy').nullable();
    table.integer('updatedBy').nullable();
    table.integer('deletedBy').nullable();
    
    // Foreign key
    table.foreign('customerId')
      .references('id')
      .inTable('customers')
      .onDelete('CASCADE');
  });
}
```

## Stripe Setup

### 1. Create Products and Prices

In your Stripe dashboard:

1. Go to Products > Add Product
2. Create your subscription products (e.g., Basic, Pro, Enterprise)
3. Add pricing for each product (monthly/yearly)
4. Note down the Product IDs and Price IDs

**Note**: The system now automatically creates Stripe products and prices when you create subscription plans through the API.

### 2. Configure Webhooks

1. Go to Developers > Webhooks in Stripe dashboard
2. Add endpoint: `https://your-domain.com/web/stripe/webhook`
3. Select the following events:
   - `customer.subscription.created`
   - `customer.subscription.updated`
   - `customer.subscription.deleted`
   - `customer.subscription.trial_will_end`
   - `invoice.payment_succeeded`
   - `invoice.payment_failed`
4. Copy the webhook signing secret to your environment variables

### 3. Update Express Configuration

Add raw body parsing for the webhook endpoint in your main server file:

```typescript
// In your main server file (e.g., src/server.ts or src/index.ts)
app.use('/web/stripe/webhook', express.raw({ type: 'application/json' }));
```

## API Endpoints

### Subscription Plans Management

#### Get All Plans (Public)
```
GET /web/plans
```
Returns all active subscription plans with features.

#### Get Admin Plans (Admin Only)
```
GET /admin/plans
Query Parameters:
- page (number, default: 1)
- pageSize (number, default: 10)
- name (string, optional) - Search by plan name
- type (string, optional) - Filter by plan type (recurring, one-off)
- status (string, optional) - Filter by status (active, inactive)
- currency (string, optional) - Filter by currency
- price (number, optional) - Filter by price
- createdBy (number, optional) - Filter by creator
- orderBy (string, default: 'createdAt')
- orderDirection (string, default: 'DESC')
```

#### Get Plan by ID
```
GET /admin/plans/:id
```

#### Create Plan (Admin Only)
```
POST /admin/plans
Body:
{
  "name": "Pro Plan",
  "description": "Professional subscription plan",
  "price": 29.99,
  "currency": "USD",
  "interval": "monthly",
  "status": "Active",
  "planType": "recurring", // "recurring", "one-off", or "both"
  "features": [
    "Unlimited access",
    "Priority support",
    "Advanced analytics"
  ]
}
```

#### Update Plan (Admin Only)
```
PUT /admin/plans/:id
Body:
{
  "name": "Updated Pro Plan",
  "description": "Updated description",
  "price": 39.99,
  "currency": "USD",
  "interval": "yearly",
  "status": "Active",
  "features": [
    "Unlimited access",
    "Priority support",
    "Advanced analytics",
    "New feature"
  ]
}
```

**Important**: Plan type cannot be changed after creation. Only price, currency, interval, status, name, description, and features can be updated.

#### Delete Plan (Admin Only)
```
DELETE /admin/plans/:id
```

#### Delete Plans by Name and Type (Admin Only)
```
DELETE /admin/plans/delete-by-name
Body:
{
  "planName": "Pro Plan",
  "planTypes": ["recurring", "one-off"]
}
```

### Subscription Management

#### Get All Subscriptions
```
GET /admin/subscriptions
Query Parameters:
- page (number, default: 1)
- pageSize (number, default: 10)
- userId (number, optional)
- status (string, optional)
- stripeCustomerId (string, optional)
- stripeSubscriptionId (string, optional)
- orderBy (string, default: 'createdAt')
- orderDirection (string, default: 'DESC')
```

#### Get Subscription by ID
```
GET /admin/subscriptions/:id
```

#### Get User Subscriptions
```
GET /admin/subscriptions/user/:userId
```

#### Create Subscription
```
POST /admin/subscriptions
Body:
{
  "userId": 1,
  "stripePriceId": "price_1234567890",
  "stripeProductId": "prod_1234567890",
  "quantity": 1,
  "trialDays": 7,
  "metadata": {
    "customField": "value"
  }
}
```

#### Update Subscription
```
PUT /admin/subscriptions/:id
Body:
{
  "stripePriceId": "price_new1234567890",
  "quantity": 2,
  "cancelAtPeriodEnd": false,
  "metadata": {
    "updatedField": "newValue"
  }
}
```

#### Cancel Subscription
```
POST /admin/subscriptions/:id/cancel
Body:
{
  "cancelAtPeriodEnd": true
}
```

#### Reactivate Subscription
```
POST /admin/subscriptions/:id/reactivate
```

#### Sync with Stripe
```
POST /admin/subscriptions/:id/sync
```

#### Delete Subscription (Soft Delete)
```
DELETE /admin/subscriptions/:id
```

### Public Endpoints

#### Stripe Webhook
```
POST /web/stripe/webhook
```
This endpoint is used by Stripe to send webhook events. No authentication required.

## Usage Examples

### Creating a Subscription Plan

```javascript
const response = await fetch('/admin/plans', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer your_jwt_token'
  },
  body: JSON.stringify({
    name: 'Pro Plan',
    description: 'Professional subscription plan',
    price: 29.99,
    currency: 'USD',
    interval: 'monthly',
    status: 'Active',
    planType: 'recurring',
    features: [
      'Unlimited access',
      'Priority support',
      'Advanced analytics'
    ]
  })
});
```

### Updating a Subscription Plan

```javascript
const response = await fetch('/admin/plans/1', {
  method: 'PUT',
  headers: {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer your_jwt_token'
  },
  body: JSON.stringify({
    price: 39.99,
    interval: 'yearly',
    features: [
      'Unlimited access',
      'Priority support',
      'Advanced analytics',
      'New feature'
    ]
  })
});
```

### Creating a Subscription

```javascript
const response = await fetch('/admin/subscriptions', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer your_jwt_token'
  },
  body: JSON.stringify({
    userId: 1,
    stripePriceId: 'price_1234567890',
    stripeProductId: 'prod_1234567890',
    quantity: 1,
    trialDays: 7
  })
});
```

### Getting User Subscriptions

```javascript
const response = await fetch('/admin/subscriptions/user/1', {
  headers: {
    'Authorization': 'Bearer your_jwt_token'
  }
});
```

## Features

### Subscription Plans
- ✅ Full CRUD operations for subscription plans
- ✅ Automatic Stripe product and price creation
- ✅ Plan features management
- ✅ Support for recurring, one-off, and both plan types
- ✅ Plan type cannot be changed after creation
- ✅ Price updates create new Stripe prices (old ones deactivated)
- ✅ Soft delete support
- ✅ Audit trail (createdBy, updatedBy, deletedBy)

### Subscriptions
- ✅ Full CRUD operations for subscriptions
- ✅ Stripe integration with webhook support
- ✅ Automatic customer creation in Stripe
- ✅ Trial period support
- ✅ Subscription cancellation (immediate or at period end)
- ✅ Subscription reactivation
- ✅ Real-time sync with Stripe via webhooks
- ✅ Pagination and filtering
- ✅ Soft delete support
- ✅ Audit trail (createdBy, updatedBy, deletedBy)
- ✅ Role-based access control

## Error Handling

The system includes comprehensive error handling for:
- Invalid Stripe API calls
- Duplicate subscriptions
- Non-existent users or plans
- Webhook signature verification
- Database constraints
- Plan type change restrictions
- Stripe product/price creation failures

## Security Considerations

1. Always use HTTPS in production
2. Keep Stripe API keys secure
3. Verify webhook signatures
4. Implement proper role-based access control
5. Use environment variables for sensitive data
6. Plan type changes are restricted after creation

## Testing

To test the subscription system:

1. Use Stripe test keys
2. Create test products and prices in Stripe test mode
3. Use Stripe CLI to test webhooks locally
4. Verify all CRUD operations work as expected
5. Test plan type restrictions (should not allow type changes after creation)
6. Test price updates (should create new Stripe prices)

## Support

For issues or questions:
1. Check Stripe documentation
2. Review server logs for error details
3. Verify environment variables are set correctly
4. Ensure database migration has been run
5. Check plan type restrictions if updating plans