# Migration from Sequelize to Supabase

This document explains the migration from Sequelize ORM to Supabase for the PineHealth backend.

## What Changed

### 1. Database Layer

**Before (Sequelize):**
- MySQL database with Sequelize ORM
- Models defined in `src/models/`
- Direct SQL queries through Sequelize
- Manual password hashing in model hooks

**After (Supabase):**
- PostgreSQL database via Supabase
- Service layer in `src/services/`
- REST API calls through Supabase client
- Supabase Auth for authentication

### 2. Authentication

**Before:**
- Custom JWT token generation
- Manual password hashing with bcryptjs
- Custom token verification middleware

**After:**
- Supabase Auth built-in authentication
- Automatic session management
- JWT tokens managed by Supabase
- Auth middleware uses Supabase token verification

### 3. Configuration

**Environment Variables:**

```env
# Old (Sequelize)
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=pinehealth
DB_USER=root
DB_PASSWORD=
DB_DIALECT=mysql
JWT_SECRET=...
JWT_REFRESH_SECRET=...

# New (Supabase)
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key
```

## Setup Instructions

### 1. Create Supabase Project

1. Go to [https://supabase.com](https://supabase.com) and create an account
2. Create a new project
3. Copy your project URL and API keys

### 2. Set Up Database Tables

Run the following SQL in your Supabase SQL Editor:

```sql
-- Users table
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  about VARCHAR(255),
  email_verified_at TIMESTAMP,
  password VARCHAR(255) NOT NULL,
  provider_id VARCHAR(100),
  provider VARCHAR(50),
  remember_token VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Posts table
CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  slug VARCHAR(255) UNIQUE NOT NULL,
  user_id INTEGER NOT NULL,
  category_id INTEGER NOT NULL,
  thumbnail_image VARCHAR(100),
  status VARCHAR(1) DEFAULT '0',
  meta_image VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Post contents table
CREATE TABLE post_contents (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
  language_code VARCHAR(10) NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  meta_title VARCHAR(255),
  meta_tags VARCHAR(255),
  meta_description VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Categories table
CREATE TABLE categories (
  id BIGSERIAL PRIMARY KEY,
  slug VARCHAR(255) UNIQUE NOT NULL,
  thumbnail_image VARCHAR(100),
  status VARCHAR(1) DEFAULT '1',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Category contents table
CREATE TABLE category_contents (
  id BIGSERIAL PRIMARY KEY,
  category_id BIGINT REFERENCES categories(id) ON DELETE CASCADE,
  language_code VARCHAR(10) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Add similar CREATE TABLE statements for:
-- - tags, taggables
-- - pages, page_contents
-- - doctors, doctor_contents
-- - departments, department_contents
-- - products, product_contents, product_categories, product_category_contents
-- - orders, order_items
-- - reviews
-- - tickets, ticket_replies
-- - contacts
-- - settings
-- - languages
-- - coupons
-- - testimonials, testimonial_contents
-- - media
-- - comments
-- - subscribers
```

### 3. Configure Environment

```bash
cd backend
cp .env.example .env
```

Edit `.env` and add your Supabase credentials:

```env
SUPABASE_URL=https://xxxxx.supabase.co
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
```

### 4. Install Dependencies

```bash
npm install
```

### 5. Start the Server

```bash
npm run dev
```

## API Changes

### Authentication

**Registration:**

```javascript
// Request (same as before)
POST /api/auth/register
{
  "name": "John Doe",
  "email": "john@example.com",
  "password": "password123"
}

// Response (changed)
{
  "success": true,
  "message": "User registered successfully",
  "data": {
    "user": { ... },
    "session": {
      "access_token": "...",
      "refresh_token": "...",
      "expires_at": 1234567890
    }
  }
}
```

**Login:**

```javascript
// Request (same as before)
POST /api/auth/login
{
  "email": "john@example.com",
  "password": "password123"
}

// Response (changed)
{
  "success": true,
  "message": "Login successful",
  "data": {
    "user": { ... },
    "session": {
      "access_token": "...",
      "refresh_token": "...",
      "expires_at": 1234567890
    }
  }
}
```

**Using Tokens:**

```bash
# Use the access_token in Authorization header
curl -H "Authorization: Bearer <access_token>" http://localhost:3000/api/user/profile
```

### Token Refresh

```javascript
// Supabase handles token refresh automatically
// On the client, use the Supabase JS client:
const { data, error } = await supabase.auth.refreshSession()
```

## Benefits of Supabase

1. **Built-in Authentication:**
   - Email/password authentication
   - OAuth providers (Google, Facebook, etc.)
   - Magic links
   - Password reset
   - Email verification

2. **Real-time Capabilities:**
   - Subscribe to database changes
   - Real-time updates

3. **Row Level Security (RLS):**
   - Database-level security policies
   - Protect data at the row level

4. **Storage:**
   - Built-in file storage
   - S3-compatible API

5. **Edge Functions:**
   - Serverless functions
   - Deploy custom logic

6. **Auto-generated REST API:**
   - Instant REST API for your database
   - GraphQL support

## Migration Checklist

- [x] Replace Sequelize with Supabase client
- [x] Update package.json dependencies
- [x] Create Supabase configuration
- [x] Create service layer (replaces models)
- [x] Update authentication controller
- [x] Update auth middleware
- [x] Update user controller
- [x] Update server.js
- [x] Update .env.example
- [ ] Set up Supabase project
- [ ] Create database tables in Supabase
- [ ] Migrate existing data (if any)
- [ ] Update remaining controllers (admin, public)
- [ ] Test all endpoints
- [ ] Update documentation

## Data Migration

If you have existing data in MySQL, you can migrate it to Supabase:

1. Export data from MySQL:
```bash
mysqldump -u root -p pinehealth > backup.sql
```

2. Convert MySQL dump to PostgreSQL format (use tools like pgloader)

3. Import to Supabase using SQL Editor or CLI

## Testing

Test the authentication flow:

```bash
# Register
curl -X POST http://localhost:3000/api/auth/register \
  -H "Content-Type: application/json" \
  -d '{"name":"Test User","email":"test@example.com","password":"test123"}'

# Login
curl -X POST http://localhost:3000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"test@example.com","password":"test123"}'

# Get Profile (use access_token from login response)
curl -X GET http://localhost:3000/api/user/profile \
  -H "Authorization: Bearer <access_token>"
```

## Troubleshooting

### Connection Error

If you see "Unable to connect to Supabase":
- Verify your SUPABASE_URL and keys in .env
- Check if your Supabase project is active
- Ensure you're using the correct API keys

### Authentication Error

If authentication fails:
- Verify the token is included in Authorization header
- Check if the user exists in Supabase Auth
- Ensure SUPABASE_SERVICE_ROLE_KEY is set for admin operations

### Query Error

If database queries fail:
- Check table names match your Supabase schema
- Verify Row Level Security (RLS) policies
- Ensure the user has proper permissions

## Resources

- [Supabase Documentation](https://supabase.com/docs)
- [Supabase JS Client](https://supabase.com/docs/reference/javascript/introduction)
- [Supabase Auth](https://supabase.com/docs/guides/auth)
- [PostgreSQL Documentation](https://www.postgresql.org/docs/)