Horux360/docs/superpowers/plans/2026-03-15-saas-transformation.md

# Horux360 SaaS Transformation — Implementation Plan

> **For agentic workers:** REQUIRED: Use superpowers:subagent-driven-development (if subagents available) or superpowers:executing-plans to implement this plan. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Transform Horux360 from a schema-per-tenant internal tool to a database-per-tenant SaaS with MercadoPago payments, transactional emails, plan enforcement, and production deployment.

**Architecture:** Monolith with dynamic connection pooling. One Express API (PM2 cluster ×2) manages connections to N PostgreSQL databases via `TenantConnectionManager`. Central DB (`horux360`) holds users/tenants/subscriptions. Each client gets their own isolated database (`horux_<rfc>`). Nginx reverse proxy with SSL terminates HTTPS.

**Tech Stack:** Express 4, PostgreSQL (pg pools), Prisma 5 (central DB only), Next.js 14, MercadoPago SDK, Nodemailer, PM2, Nginx, Let's Encrypt.

**Spec:** `docs/superpowers/specs/2026-03-15-saas-transformation-design.md`

---

## File Structure

### New files to create:

```
apps/api/src/
├── config/
│   └── database.ts                    # TenantConnectionManager class
├── services/
│   ├── email/
│   │   ├── email.service.ts           # EmailService (Nodemailer transport)
│   │   └── templates/
│   │       ├── base.ts                # Shared HTML base layout
│   │       ├── welcome.ts             # Welcome email template
│   │       ├── fiel-notification.ts   # FIEL upload notification
│   │       ├── payment-confirmed.ts   # Payment confirmation
│   │       ├── payment-failed.ts      # Payment failure alert
│   │       ├── subscription-expiring.ts
│   │       └── subscription-cancelled.ts
│   └── payment/
│       ├── mercadopago.service.ts     # MercadoPago Preapproval integration
│       └── subscription.service.ts    # Subscription lifecycle management
├── controllers/
│   ├── webhook.controller.ts          # MercadoPago webhook handler
│   └── subscription.controller.ts     # Subscription admin endpoints
├── routes/
│   ├── webhook.routes.ts              # Public webhook routes (no auth)
│   └── subscription.routes.ts         # Subscription management routes
├── middlewares/
│   ├── plan-limits.middleware.ts       # checkPlanLimits + checkCfdiLimit
│   └── feature-gate.middleware.ts     # requireFeature middleware
scripts/
├── decrypt-fiel.ts                    # CLI to decrypt FIEL files
├── backup.sh                          # Daily backup script
└── provision-tenant-db.ts             # Standalone DB provisioning (for testing)

deploy/
├── nginx/
│   └── horux360.conf                  # Nginx site config
└── pm2/
    └── ecosystem.config.js            # Production PM2 config
```

### Files to modify:

```
apps/api/src/config/env.ts             # Add FIEL_ENCRYPTION_KEY, MP_*, SMTP_* vars
apps/api/prisma/schema.prisma          # Replace schemaName→databaseName, add Subscription+Payment models
apps/api/src/services/auth.service.ts  # JWT payload: schemaName→databaseName
apps/api/src/services/tenants.service.ts  # Rewrite provisioning for DB-per-tenant
apps/api/src/services/sat/sat-crypto.service.ts  # Use FIEL_ENCRYPTION_KEY, per-component IV
apps/api/src/services/fiel.service.ts  # Add filesystem storage, admin notification
apps/api/src/middlewares/tenant.middleware.ts  # Use TenantConnectionManager pools
apps/api/src/middlewares/auth.middleware.ts    # Update JWTPayload references
apps/api/src/utils/schema-manager.ts   # Rewrite as database-manager.ts (CREATE DATABASE)
apps/api/src/app.ts                    # Register new routes (webhooks, subscriptions)
apps/api/src/index.ts                  # Init TenantConnectionManager, graceful shutdown

packages/shared/src/types/auth.ts      # schemaName→databaseName in JWTPayload
packages/shared/src/types/tenant.ts    # schemaName→databaseName, add Subscription type

apps/web/stores/auth-store.ts          # Update UserInfo references if needed
apps/web/lib/api/client.ts             # Update tenant header logic for databaseName

# All tenant service files (cfdi, dashboard, impuestos, etc.) — change from
# schema-prefixed queries to using req.tenantPool directly
apps/api/src/services/cfdi.service.ts
apps/api/src/services/dashboard.service.ts
apps/api/src/services/impuestos.service.ts
apps/api/src/services/alertas.service.ts
apps/api/src/services/calendario.service.ts
apps/api/src/services/reportes.service.ts
apps/api/src/services/export.service.ts
```

---

## Chunk 1: Core Infrastructure (DB-per-tenant + env + JWT)

This chunk establishes the database-per-tenant architecture. Everything else depends on this.

### Task 1: Update environment configuration

**Files:**
- Modify: `apps/api/src/config/env.ts`

- [ ] **Step 1: Add new environment variables to Zod schema**

```typescript
// apps/api/src/config/env.ts
import { z } from 'zod';

const envSchema = z.object({
  NODE_ENV: z.enum(['development', 'production', 'test']).default('development'),
  PORT: z.string().default('4000'),
  DATABASE_URL: z.string(),
  JWT_SECRET: z.string().min(32),
  JWT_EXPIRES_IN: z.string().default('15m'),
  JWT_REFRESH_EXPIRES_IN: z.string().default('7d'),
  CORS_ORIGIN: z.string().default('http://localhost:3000'),

  // FIEL encryption (separate from JWT)
  FIEL_ENCRYPTION_KEY: z.string().min(32).default('dev-fiel-encryption-key-min-32-chars!!'),

  // MercadoPago
  MP_ACCESS_TOKEN: z.string().optional(),
  MP_WEBHOOK_SECRET: z.string().optional(),
  MP_NOTIFICATION_URL: z.string().optional(),

  // SMTP (Gmail)
  SMTP_HOST: z.string().default('smtp.gmail.com'),
  SMTP_PORT: z.string().default('587'),
  SMTP_USER: z.string().optional(),
  SMTP_PASS: z.string().optional(),
  SMTP_FROM: z.string().default('Horux360 <noreply@horuxfin.com>'),

  // Admin notification email
  ADMIN_EMAIL: z.string().default('admin@horuxfin.com'),

  // FIEL filesystem storage path
  FIEL_STORAGE_PATH: z.string().default('/var/horux/fiel'),
});
```

- [ ] **Step 2: Update `.env` file with new variables (development defaults)**

Add to `apps/api/.env`:
```
FIEL_ENCRYPTION_KEY=dev-fiel-encryption-key-min-32-chars-long!!
FIEL_STORAGE_PATH=/var/horux/fiel
SMTP_HOST=smtp.gmail.com
SMTP_PORT=587
ADMIN_EMAIL=carlos@horuxfin.com
```

- [ ] **Step 3: Commit**

```bash
git add apps/api/src/config/env.ts apps/api/.env
git commit -m "feat: add env vars for FIEL encryption, MercadoPago, SMTP, and admin email"
```

---

### Task 2: Update Prisma schema (databaseName, Subscription, Payment)

**Files:**
- Modify: `apps/api/prisma/schema.prisma`

- [ ] **Step 1: Update Tenant model — replace schemaName with databaseName**

In `schema.prisma`, change the Tenant model:
```prisma
model Tenant {
  id           String    @id @default(uuid())
  nombre       String
  rfc          String    @unique
  plan         Plan      @default(starter)
  databaseName String    @unique @map("database_name")
  cfdiLimit    Int       @default(100) @map("cfdi_limit")
  usersLimit   Int       @default(1) @map("users_limit")
  active       Boolean   @default(true)
  createdAt    DateTime  @default(now()) @map("created_at")
  expiresAt    DateTime? @map("expires_at")

  users           User[]
  fielCredential  FielCredential?
  satSyncJobs     SatSyncJob[]
  subscriptions   Subscription[]
  payments        Payment[]

  @@map("tenants")
}
```

- [ ] **Step 2: Update FielCredential model — add per-component encryption columns**

```prisma
model FielCredential {
  id                   String   @id @default(uuid())
  tenantId             String   @unique @map("tenant_id")
  rfc                  String
  cerData              Bytes    @map("cer_data")
  keyData              Bytes    @map("key_data")
  keyPasswordEncrypted Bytes    @map("key_password_encrypted")
  cerIv                Bytes    @map("cer_iv")
  cerTag               Bytes    @map("cer_tag")
  keyIv                Bytes    @map("key_iv")
  keyTag               Bytes    @map("key_tag")
  passwordIv           Bytes    @map("password_iv")
  passwordTag          Bytes    @map("password_tag")
  serialNumber         String?  @map("serial_number")
  validFrom            DateTime @map("valid_from")
  validUntil           DateTime @map("valid_until")
  isActive             Boolean  @default(true) @map("is_active")
  createdAt            DateTime @default(now()) @map("created_at")
  updatedAt            DateTime @updatedAt @map("updated_at")

  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@map("fiel_credentials")
}
```

- [ ] **Step 3: Add Subscription and Payment models**

```prisma
model Subscription {
  id                 String    @id @default(uuid())
  tenantId           String    @map("tenant_id")
  plan               Plan
  mpPreapprovalId    String?   @map("mp_preapproval_id")
  status             String    @default("pending")
  amount             Decimal   @db.Decimal(10, 2)
  frequency          String    @default("monthly")
  currentPeriodStart DateTime? @map("current_period_start")
  currentPeriodEnd   DateTime? @map("current_period_end")
  createdAt          DateTime  @default(now()) @map("created_at")
  updatedAt          DateTime  @updatedAt @map("updated_at")

  tenant   Tenant    @relation(fields: [tenantId], references: [id])
  payments Payment[]

  @@index([tenantId])
  @@index([status])
  @@map("subscriptions")
}

model Payment {
  id             String    @id @default(uuid())
  tenantId       String    @map("tenant_id")
  subscriptionId String?   @map("subscription_id")
  mpPaymentId    String?   @map("mp_payment_id")
  amount         Decimal   @db.Decimal(10, 2)
  status         String    @default("pending")
  paymentMethod  String?   @map("payment_method")
  paidAt         DateTime? @map("paid_at")
  createdAt      DateTime  @default(now()) @map("created_at")

  tenant       Tenant        @relation(fields: [tenantId], references: [id])
  subscription Subscription? @relation(fields: [subscriptionId], references: [id])

  @@index([tenantId])
  @@index([subscriptionId])
  @@map("payments")
}
```

- [ ] **Step 4: Generate and apply migration**

```bash
cd /root/Horux/apps/api
npx prisma migrate dev --name saas-db-per-tenant
npx prisma generate
```

Expected: Migration creates new columns/tables. Old `schema_name` column is renamed to `database_name`. New `subscriptions` and `payments` tables are created.

**Note:** This migration will need to handle the rename from `schema_name` to `database_name`. If Prisma auto-detects as a rename, it will migrate the data. If not, we need a custom migration SQL to rename the column and update existing values from `tenant_xxx` to `horux_xxx` format.

- [ ] **Step 5: Commit**

```bash
git add apps/api/prisma/
git commit -m "feat: update Prisma schema for DB-per-tenant, subscriptions, and payments"
```

---

### Task 3: Update shared types (JWT payload, Tenant)

**Files:**
- Modify: `packages/shared/src/types/auth.ts`
- Modify: `packages/shared/src/types/tenant.ts`

- [ ] **Step 1: Update JWTPayload in auth.ts**

Replace `schemaName` with `databaseName`:
```typescript
export interface JWTPayload {
  userId: string;
  email: string;
  role: Role;
  tenantId: string;
  databaseName: string;  // was: schemaName
  iat?: number;
  exp?: number;
}
```

- [ ] **Step 2: Update Tenant interface in tenant.ts**

```typescript
export interface Tenant {
  id: string;
  nombre: string;
  rfc: string;
  plan: Plan;
  databaseName: string;  // was: schemaName
  cfdiLimit: number;
  usersLimit: number;
  active: boolean;
  createdAt: string;
  expiresAt?: string;
}
```

- [ ] **Step 3: Add Subscription and Payment types to tenant.ts**

```typescript
export interface Subscription {
  id: string;
  tenantId: string;
  plan: Plan;
  mpPreapprovalId?: string;
  status: 'pending' | 'authorized' | 'paused' | 'cancelled';
  amount: number;
  frequency: 'monthly' | 'yearly';
  currentPeriodStart?: string;
  currentPeriodEnd?: string;
  createdAt: string;
  updatedAt: string;
}

export interface Payment {
  id: string;
  tenantId: string;
  subscriptionId?: string;
  mpPaymentId?: string;
  amount: number;
  status: 'approved' | 'pending' | 'rejected' | 'refunded';
  paymentMethod?: string;
  paidAt?: string;
  createdAt: string;
}
```

- [ ] **Step 4: Commit**

```bash
git add packages/shared/
git commit -m "feat: update shared types for databaseName and add Subscription/Payment types"
```

---

### Task 4: Create TenantConnectionManager

**Files:**
- Create: `apps/api/src/config/database.ts`

- [ ] **Step 1: Install pg dependency**

```bash
cd /root/Horux/apps/api && pnpm add pg && pnpm add -D @types/pg
```

- [ ] **Step 2: Create TenantConnectionManager class**

```typescript
// apps/api/src/config/database.ts
import { Pool, PoolConfig } from 'pg';
import { env } from './env';

interface PoolEntry {
  pool: Pool;
  lastAccess: Date;
}

// Parse the central DATABASE_URL to extract host, port, user, password
function parseDatabaseUrl(url: string) {
  const parsed = new URL(url);
  return {
    host: parsed.hostname,
    port: parseInt(parsed.port || '5432'),
    user: parsed.username,
    password: parsed.password,
  };
}

class TenantConnectionManager {
  private pools: Map<string, PoolEntry> = new Map();
  private cleanupInterval: NodeJS.Timeout | null = null;
  private dbConfig: { host: string; port: number; user: string; password: string };

  constructor() {
    this.dbConfig = parseDatabaseUrl(env.DATABASE_URL);
    // Cleanup idle pools every 60 seconds
    this.cleanupInterval = setInterval(() => this.cleanupIdlePools(), 60_000);
  }

  /**
   * Get or create a connection pool for a tenant's database.
   */
  getPool(tenantId: string, databaseName: string): Pool {
    const entry = this.pools.get(tenantId);
    if (entry) {
      entry.lastAccess = new Date();
      return entry.pool;
    }

    const poolConfig: PoolConfig = {
      host: this.dbConfig.host,
      port: this.dbConfig.port,
      user: this.dbConfig.user,
      password: this.dbConfig.password,
      database: databaseName,
      max: 3,                          // 3 per pool × 2 PM2 workers = 6/tenant
      idleTimeoutMillis: 300_000,      // 5 min
      connectionTimeoutMillis: 10_000, // 10 sec
    };

    const pool = new Pool(poolConfig);

    pool.on('error', (err) => {
      console.error(`Pool error for tenant ${tenantId} (${databaseName}):`, err.message);
    });

    this.pools.set(tenantId, { pool, lastAccess: new Date() });
    return pool;
  }

  /**
   * Create a new database for a tenant with all required tables and indexes.
   */
  async provisionDatabase(rfc: string): Promise<string> {
    const databaseName = `horux_${rfc.toLowerCase().replace(/[^a-z0-9]/g, '')}`;

    // Connect to default 'postgres' DB to create new database
    const adminPool = new Pool({
      ...this.dbConfig,
      database: 'postgres',
      max: 1,
    });

    try {
      // Check if database already exists
      const exists = await adminPool.query(
        `SELECT 1 FROM pg_database WHERE datname = $1`,
        [databaseName]
      );

      if (exists.rows.length > 0) {
        throw new Error(`Database ${databaseName} already exists`);
      }

      // CREATE DATABASE cannot run inside a transaction
      await adminPool.query(`CREATE DATABASE "${databaseName}"`);

      // Connect to the new database to create tables
      const tenantPool = new Pool({
        ...this.dbConfig,
        database: databaseName,
        max: 1,
      });

      try {
        await this.createTables(tenantPool);
        await this.createIndexes(tenantPool);
      } finally {
        await tenantPool.end();
      }

      return databaseName;
    } finally {
      await adminPool.end();
    }
  }

  /**
   * Soft-delete: rename database so it can be recovered.
   */
  async deprovisionDatabase(databaseName: string): Promise<void> {
    // Close any active pool for this database
    for (const [tenantId, entry] of this.pools.entries()) {
      // We don't store databaseName in the map, so we close all and let them reconnect
      // In practice, the tenant is being deactivated so no more requests will come
    }

    const timestamp = Date.now();
    const adminPool = new Pool({
      ...this.dbConfig,
      database: 'postgres',
      max: 1,
    });

    try {
      // Terminate connections to the database
      await adminPool.query(`
        SELECT pg_terminate_backend(pid)
        FROM pg_stat_activity
        WHERE datname = $1 AND pid <> pg_backend_pid()
      `, [databaseName]);

      // Rename instead of drop
      await adminPool.query(
        `ALTER DATABASE "${databaseName}" RENAME TO "${databaseName}_deleted_${timestamp}"`
      );
    } finally {
      await adminPool.end();
    }
  }

  /**
   * Remove idle pools (not accessed in last 5 minutes).
   */
  private cleanupIdlePools(): void {
    const now = new Date();
    const maxIdle = 5 * 60 * 1000; // 5 minutes

    for (const [tenantId, entry] of this.pools.entries()) {
      if (now.getTime() - entry.lastAccess.getTime() > maxIdle) {
        entry.pool.end().catch((err) =>
          console.error(`Error closing pool for tenant ${tenantId}:`, err.message)
        );
        this.pools.delete(tenantId);
      }
    }
  }

  /**
   * Invalidate (close and remove) a specific tenant's pool.
   * Used when subscription status changes via webhook.
   */
  invalidatePool(tenantId: string): void {
    const entry = this.pools.get(tenantId);
    if (entry) {
      entry.pool.end().catch(() => {});
      this.pools.delete(tenantId);
    }
  }

  /**
   * Graceful shutdown: close all pools.
   */
  async shutdown(): Promise<void> {
    if (this.cleanupInterval) {
      clearInterval(this.cleanupInterval);
    }

    const closePromises = Array.from(this.pools.values()).map((entry) =>
      entry.pool.end()
    );
    await Promise.all(closePromises);
    this.pools.clear();
  }

  /**
   * Get stats about active pools.
   */
  getStats(): { activePools: number; tenantIds: string[] } {
    return {
      activePools: this.pools.size,
      tenantIds: Array.from(this.pools.keys()),
    };
  }

  private async createTables(pool: Pool): Promise<void> {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS cfdis (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        uuid_fiscal VARCHAR(36) UNIQUE NOT NULL,
        tipo VARCHAR(20) NOT NULL DEFAULT 'ingreso',
        serie VARCHAR(25),
        folio VARCHAR(40),
        fecha_emision TIMESTAMP NOT NULL,
        fecha_timbrado TIMESTAMP,
        rfc_emisor VARCHAR(13) NOT NULL,
        nombre_emisor VARCHAR(300) NOT NULL,
        rfc_receptor VARCHAR(13) NOT NULL,
        nombre_receptor VARCHAR(300) NOT NULL,
        subtotal DECIMAL(18,2) DEFAULT 0,
        descuento DECIMAL(18,2) DEFAULT 0,
        iva DECIMAL(18,2) DEFAULT 0,
        isr_retenido DECIMAL(18,2) DEFAULT 0,
        iva_retenido DECIMAL(18,2) DEFAULT 0,
        total DECIMAL(18,2) DEFAULT 0,
        moneda VARCHAR(10) DEFAULT 'MXN',
        tipo_cambio DECIMAL(10,4) DEFAULT 1,
        metodo_pago VARCHAR(10),
        forma_pago VARCHAR(10),
        uso_cfdi VARCHAR(10),
        estado VARCHAR(20) DEFAULT 'vigente',
        xml_url TEXT,
        pdf_url TEXT,
        xml_original TEXT,
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW(),
        last_sat_sync TIMESTAMP,
        sat_sync_job_id UUID,
        source VARCHAR(20) DEFAULT 'manual'
      );

      CREATE TABLE IF NOT EXISTS iva_mensual (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        año INTEGER NOT NULL,
        mes INTEGER NOT NULL,
        iva_trasladado DECIMAL(18,2) DEFAULT 0,
        iva_acreditable DECIMAL(18,2) DEFAULT 0,
        iva_retenido DECIMAL(18,2) DEFAULT 0,
        resultado DECIMAL(18,2) DEFAULT 0,
        acumulado DECIMAL(18,2) DEFAULT 0,
        estado VARCHAR(20) DEFAULT 'pendiente',
        fecha_declaracion TIMESTAMP,
        UNIQUE(año, mes)
      );

      CREATE TABLE IF NOT EXISTS isr_mensual (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        año INTEGER NOT NULL,
        mes INTEGER NOT NULL,
        ingresos_acumulados DECIMAL(18,2) DEFAULT 0,
        deducciones DECIMAL(18,2) DEFAULT 0,
        base_gravable DECIMAL(18,2) DEFAULT 0,
        isr_causado DECIMAL(18,2) DEFAULT 0,
        isr_retenido DECIMAL(18,2) DEFAULT 0,
        isr_a_pagar DECIMAL(18,2) DEFAULT 0,
        estado VARCHAR(20) DEFAULT 'pendiente',
        fecha_declaracion TIMESTAMP,
        UNIQUE(año, mes)
      );

      CREATE TABLE IF NOT EXISTS alertas (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        tipo VARCHAR(50) NOT NULL,
        titulo VARCHAR(200) NOT NULL,
        mensaje TEXT,
        prioridad VARCHAR(20) DEFAULT 'media',
        fecha_vencimiento TIMESTAMP,
        leida BOOLEAN DEFAULT FALSE,
        resuelta BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT NOW()
      );

      CREATE TABLE IF NOT EXISTS calendario_fiscal (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        titulo VARCHAR(200) NOT NULL,
        descripcion TEXT,
        tipo VARCHAR(50) NOT NULL,
        fecha_limite TIMESTAMP NOT NULL,
        recurrencia VARCHAR(20) DEFAULT 'unica',
        completado BOOLEAN DEFAULT FALSE,
        notas TEXT,
        created_at TIMESTAMP DEFAULT NOW()
      );
    `);
  }

  private async createIndexes(pool: Pool): Promise<void> {
    // Enable pg_trgm extension for fuzzy search
    await pool.query(`CREATE EXTENSION IF NOT EXISTS pg_trgm`);

    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_cfdis_fecha_emision ON cfdis(fecha_emision DESC);
      CREATE INDEX IF NOT EXISTS idx_cfdis_tipo ON cfdis(tipo);
      CREATE INDEX IF NOT EXISTS idx_cfdis_rfc_emisor ON cfdis(rfc_emisor);
      CREATE INDEX IF NOT EXISTS idx_cfdis_rfc_receptor ON cfdis(rfc_receptor);
      CREATE INDEX IF NOT EXISTS idx_cfdis_estado ON cfdis(estado);
      CREATE INDEX IF NOT EXISTS idx_cfdis_nombre_emisor_trgm ON cfdis USING gin(nombre_emisor gin_trgm_ops);
      CREATE INDEX IF NOT EXISTS idx_cfdis_nombre_receptor_trgm ON cfdis USING gin(nombre_receptor gin_trgm_ops);
    `);
  }
}

// Singleton instance
export const tenantDb = new TenantConnectionManager();
```

- [ ] **Step 3: Commit**

```bash
git add apps/api/src/config/database.ts apps/api/package.json pnpm-lock.yaml
git commit -m "feat: add TenantConnectionManager with dynamic pool management"
```

---

### Task 5: Update tenant middleware to use connection pools

**Files:**
- Modify: `apps/api/src/middlewares/tenant.middleware.ts`

- [ ] **Step 1: Rewrite tenant middleware**

Replace the entire file with:

```typescript
// apps/api/src/middlewares/tenant.middleware.ts
import { Request, Response, NextFunction } from 'express';
import { Pool } from 'pg';
import { prisma } from '../config/prisma';
import { tenantDb } from '../config/database';

// Extend Express Request
declare global {
  namespace Express {
    interface Request {
      tenantPool?: Pool;
      viewingTenantId?: string;
    }
  }
}

export async function tenantMiddleware(req: Request, res: Response, next: NextFunction) {
  try {
    if (!req.user) {
      return res.status(401).json({ message: 'No autenticado' });
    }

    let tenantId = req.user.tenantId;
    let databaseName = req.user.databaseName;

    // Admin impersonation via X-View-Tenant header
    const viewTenantHeader = req.headers['x-view-tenant'] as string;
    if (viewTenantHeader && req.user.role === 'admin') {
      const viewedTenant = await prisma.tenant.findFirst({
        where: {
          OR: [
            { id: viewTenantHeader },
            { rfc: viewTenantHeader },
          ],
        },
        select: { id: true, databaseName: true, active: true },
      });

      if (!viewedTenant) {
        return res.status(404).json({ message: 'Tenant no encontrado' });
      }

      if (!viewedTenant.active) {
        return res.status(403).json({ message: 'Tenant inactivo' });
      }

      tenantId = viewedTenant.id;
      databaseName = viewedTenant.databaseName;
      req.viewingTenantId = viewedTenant.id;
    }

    // Get the connection pool for this tenant's database
    req.tenantPool = tenantDb.getPool(tenantId, databaseName);
    next();
  } catch (error) {
    console.error('Tenant middleware error:', error);
    return res.status(500).json({ message: 'Error al resolver tenant' });
  }
}
```

- [ ] **Step 2: Commit**

```bash
git add apps/api/src/middlewares/tenant.middleware.ts
git commit -m "feat: rewrite tenant middleware to use TenantConnectionManager pools"
```

---

### Task 6: Update auth service (JWT payload + registration)

**Files:**
- Modify: `apps/api/src/services/auth.service.ts`

- [ ] **Step 1: Update JWT payload generation**

In `auth.service.ts`, find all places where the token payload is created (the `register` function around line 52 and the `login` function around line 115, and `refreshTokens` around line 149) and change `schemaName` to `databaseName`:

```typescript
// In register():
const tokenPayload = {
  userId: user.id,
  email: user.email,
  role: user.role as 'admin' | 'contador' | 'visor',
  tenantId: tenant.id,
  databaseName: tenant.databaseName,  // was: schemaName: tenant.schemaName
};

// In login():
const tokenPayload = {
  userId: user.id,
  email: user.email,
  role: user.role as 'admin' | 'contador' | 'visor',
  tenantId: user.tenant.id,
  databaseName: user.tenant.databaseName,  // was: schemaName: user.tenant.schemaName
};

// In refreshTokens() — update the select to fetch databaseName:
const tokenRecord = await tx.refreshToken.findUnique({
  where: { token },
  include: {
    user: {
      include: {
        tenant: {
          select: { id: true, databaseName: true },  // was: schemaName
        },
      },
    },
  },
});
// ... and the payload:
const newPayload = {
  userId: tokenRecord.user.id,
  email: tokenRecord.user.email,
  role: tokenRecord.user.role as 'admin' | 'contador' | 'visor',
  tenantId: tokenRecord.user.tenant.id,
  databaseName: tokenRecord.user.tenant.databaseName,
};
```

- [ ] **Step 2: Update register to use database provisioning instead of schema**

Replace the call to `createTenantSchema(schemaName)` with `tenantDb.provisionDatabase(rfc)`:

```typescript
import { tenantDb } from '../config/database';

// In register():
const databaseName = await tenantDb.provisionDatabase(data.empresa.rfc);

const tenant = await prisma.tenant.create({
  data: {
    nombre: data.empresa.nombre,
    rfc: data.empresa.rfc.toUpperCase(),
    databaseName,  // was: schemaName
    plan: 'starter',
  },
});
```

- [ ] **Step 3: Also update the login query select to use databaseName**

In the `login` function, find the `prisma.user.findUnique` query and update:
```typescript
const user = await prisma.user.findUnique({
  where: { email: data.email },
  include: {
    tenant: {
      select: { id: true, nombre: true, rfc: true, databaseName: true, active: true },
    },
  },
});
```

- [ ] **Step 4: Update UserInfo response in login and register**

Find where `UserInfo` is constructed and update `tenantName` etc. The `schemaName` is not sent to frontend in `UserInfo`, so this may only need `databaseName` in the JWT payload, not the response body. Verify the `UserInfo` interface — it has `tenantId`, `tenantName`, `tenantRfc` but NOT `schemaName`, so the response body doesn't change.

- [ ] **Step 5: Commit**

```bash
git add apps/api/src/services/auth.service.ts
git commit -m "feat: update auth service JWT payload and registration for DB-per-tenant"
```

---

### Task 7: Update tenants service for DB provisioning

**Files:**
- Modify: `apps/api/src/services/tenants.service.ts`

- [ ] **Step 1: Rewrite createTenant to use TenantConnectionManager**

Replace the inline SQL provisioning with `tenantDb.provisionDatabase()`:

```typescript
import { tenantDb } from '../config/database';

export async function createTenant(data: {
  nombre: string;
  rfc: string;
  plan?: string;
  cfdiLimit?: number;
  usersLimit?: number;
}) {
  const rfc = data.rfc.toUpperCase();

  // Check if tenant already exists
  const existing = await prisma.tenant.findUnique({ where: { rfc } });
  if (existing) {
    throw new Error('Ya existe un tenant con ese RFC');
  }

  let databaseName: string;
  let tenant: any;

  try {
    // Step 1: Provision database
    databaseName = await tenantDb.provisionDatabase(rfc);

    // Step 2: Create tenant record
    tenant = await prisma.tenant.create({
      data: {
        nombre: data.nombre,
        rfc,
        plan: (data.plan as any) || 'starter',
        databaseName,
        cfdiLimit: data.cfdiLimit ?? 100,
        usersLimit: data.usersLimit ?? 1,
      },
    });

    return tenant;
  } catch (error) {
    // Rollback: drop database if it was created
    if (databaseName!) {
      try {
        await tenantDb.deprovisionDatabase(databaseName);
      } catch (rollbackErr) {
        console.error('Rollback failed:', rollbackErr);
      }
    }
    // Rollback: delete tenant record if it was created
    if (tenant) {
      try {
        await prisma.tenant.delete({ where: { id: tenant.id } });
      } catch (rollbackErr) {
        console.error('Tenant record rollback failed:', rollbackErr);
      }
    }
    throw error;
  }
}
```

- [ ] **Step 2: Update all queries that reference schemaName to databaseName**

Find all `schemaName` references in this file and replace with `databaseName`:

In `getAllTenants()`:
```typescript
select: { id: true, nombre: true, rfc: true, plan: true, databaseName: true, active: true, createdAt: true, ... }
```

In `getTenantById()`:
```typescript
select: { id: true, nombre: true, rfc: true, plan: true, databaseName: true, cfdiLimit: true, usersLimit: true, active: true, ... }
```

- [ ] **Step 3: Commit**

```bash
git add apps/api/src/services/tenants.service.ts
git commit -m "feat: rewrite tenant provisioning for database-per-tenant with rollback"
```

---

### Task 8: Update all tenant services to use req.tenantPool

**Files:**
- Modify: All service files that use tenant-scoped queries

This is the biggest change. Every service that currently uses `prisma.$queryRawUnsafe` with schema-prefixed table names (`"${schema}".cfdis`) needs to use `pool.query` with direct table names (`cfdis`).

- [ ] **Step 1: Update cfdi.service.ts**

The pattern for every query changes from:
```typescript
// Before
const result = await prisma.$queryRawUnsafe(`SELECT * FROM "${schema}".cfdis WHERE ...`);

// After — function now receives pool as first argument
export async function getCfdis(pool: Pool, filters: CfdiFilters) {
  const result = await pool.query(`SELECT * FROM cfdis WHERE ...`, params);
  return result.rows;
}
```

Update every exported function in `cfdi.service.ts` to accept `Pool` as the first parameter. Remove all schema prefix references (`"${schema}".`). Replace `prisma.$queryRawUnsafe` with `pool.query`.

- [ ] **Step 2: Update cfdi controller/routes to pass req.tenantPool**

In the route handlers, change from passing `req.tenantSchema` to passing `req.tenantPool`:
```typescript
// Before
const result = await getCfdis(req.tenantSchema!, filters);

// After
const result = await getCfdis(req.tenantPool!, filters);
```

- [ ] **Step 3: Repeat for all other tenant services**

Apply the same pattern to:
- `dashboard.service.ts`
- `impuestos.service.ts`
- `alertas.service.ts`
- `calendario.service.ts`
- `reportes.service.ts`
- `export.service.ts`

Each function: replace first param with `Pool`, remove schema prefix, use `pool.query()`.

- [ ] **Step 4: Commit**

```bash
git add apps/api/src/services/ apps/api/src/routes/
git commit -m "feat: migrate all tenant services from schema queries to pool-based queries"
```

---

### Task 9: Update app entry point and graceful shutdown

**Files:**
- Modify: `apps/api/src/index.ts`
- Modify: `apps/api/src/app.ts`

- [ ] **Step 1: Add graceful shutdown in index.ts**

```typescript
import { tenantDb } from './config/database';

// After server.listen():
const gracefulShutdown = async (signal: string) => {
  console.log(`${signal} received. Shutting down gracefully...`);
  await tenantDb.shutdown();
  process.exit(0);
};

process.on('SIGTERM', () => gracefulShutdown('SIGTERM'));
process.on('SIGINT', () => gracefulShutdown('SIGINT'));
```

- [ ] **Step 2: Add PM2 cross-worker cache invalidation support**

```typescript
// Listen for PM2 messages to invalidate tenant caches
process.on('message', (msg: any) => {
  if (msg?.type === 'invalidate-tenant-cache' && msg.tenantId) {
    tenantDb.invalidatePool(msg.tenantId);
  }
});
```

- [ ] **Step 3: Commit**

```bash
git add apps/api/src/index.ts apps/api/src/app.ts
git commit -m "feat: add graceful shutdown and PM2 cross-worker messaging"
```

---

### Task 10: Remove old schema-manager and clean up

**Files:**
- Delete or archive: `apps/api/src/utils/schema-manager.ts`
- Modify: any imports that reference it

- [ ] **Step 1: Remove schema-manager.ts imports**

The `schema-manager.ts` is imported in `auth.service.ts`. Since we've already replaced the call with `tenantDb.provisionDatabase()`, we can safely remove the import and delete the file.

- [ ] **Step 2: Delete schema-manager.ts**

```bash
rm apps/api/src/utils/schema-manager.ts
```

- [ ] **Step 3: Search for any remaining `schemaName` references in the API codebase**

```bash
grep -r "schemaName\|schema_name\|tenantSchema\|search_path" apps/api/src/ --include="*.ts"
```

Fix any remaining references.

- [ ] **Step 4: Build and verify**

```bash
cd /root/Horux/apps/api && pnpm build
```

Expected: No TypeScript errors.

- [ ] **Step 5: Commit**

```bash
git add -A
git commit -m "refactor: remove schema-manager, clean up all schemaName references"
```

---

## Chunk 2: FIEL Dual Storage + Encryption Fix

### Task 11: Update SAT crypto service for dedicated encryption key

**Files:**
- Modify: `apps/api/src/services/sat/sat-crypto.service.ts`

- [ ] **Step 1: Change key derivation to use FIEL_ENCRYPTION_KEY**

Replace:
```typescript
function deriveKey(): Buffer {
  return createHash('sha256').update(env.JWT_SECRET).digest();
}
```

With:
```typescript
function deriveKey(): Buffer {
  return createHash('sha256').update(env.FIEL_ENCRYPTION_KEY).digest();
}
```

- [ ] **Step 2: Rewrite encryptFielCredentials for per-component encryption**

Replace the single-encryption approach with independent encryption per component:

```typescript
interface EncryptedFielData {
  encryptedCer: Buffer;
  cerIv: Buffer;
  cerTag: Buffer;
  encryptedKey: Buffer;
  keyIv: Buffer;
  keyTag: Buffer;
  encryptedPassword: Buffer;
  passwordIv: Buffer;
  passwordTag: Buffer;
}

export function encryptFielCredentials(
  cerData: Buffer,
  keyData: Buffer,
  password: string
): EncryptedFielData {
  const key = deriveKey();

  const cerResult = encryptBuffer(cerData, key);
  const keyResult = encryptBuffer(keyData, key);
  const passResult = encryptBuffer(Buffer.from(password, 'utf-8'), key);

  return {
    encryptedCer: cerResult.encrypted,
    cerIv: cerResult.iv,
    cerTag: cerResult.tag,
    encryptedKey: keyResult.encrypted,
    keyIv: keyResult.iv,
    keyTag: keyResult.tag,
    encryptedPassword: passResult.encrypted,
    passwordIv: passResult.iv,
    passwordTag: passResult.tag,
  };
}

function encryptBuffer(data: Buffer, key: Buffer): { encrypted: Buffer; iv: Buffer; tag: Buffer } {
  const iv = randomBytes(IV_LENGTH);
  const cipher = createCipheriv('aes-256-gcm', key, iv);
  const encrypted = Buffer.concat([cipher.update(data), cipher.final()]);
  const tag = cipher.getAuthTag();
  return { encrypted, iv, tag };
}

export function decryptFielCredentials(
  encryptedCer: Buffer, cerIv: Buffer, cerTag: Buffer,
  encryptedKey: Buffer, keyIv: Buffer, keyTag: Buffer,
  encryptedPassword: Buffer, passwordIv: Buffer, passwordTag: Buffer
): { cerData: Buffer; keyData: Buffer; password: string } {
  const key = deriveKey();

  const cerData = decryptBuffer(encryptedCer, key, cerIv, cerTag);
  const keyData = decryptBuffer(encryptedKey, key, keyIv, keyTag);
  const password = decryptBuffer(encryptedPassword, key, passwordIv, passwordTag).toString('utf-8');

  return { cerData, keyData, password };
}

function decryptBuffer(encrypted: Buffer, key: Buffer, iv: Buffer, tag: Buffer): Buffer {
  const decipher = createDecipheriv('aes-256-gcm', key, iv);
  decipher.setAuthTag(tag);
  return Buffer.concat([decipher.update(encrypted), decipher.final()]);
}
```

- [ ] **Step 3: Commit**

```bash
git add apps/api/src/services/sat/sat-crypto.service.ts
git commit -m "feat: use dedicated FIEL_ENCRYPTION_KEY with per-component encryption"
```

---

### Task 12: Update FIEL service for dual storage

**Files:**
- Modify: `apps/api/src/services/fiel.service.ts`

- [ ] **Step 1: Add filesystem storage to uploadFiel**

After encrypting and saving to DB, also save to filesystem:

```typescript
import { writeFile, mkdir } from 'fs/promises';
import { join } from 'path';
import { env } from '../config/env';
import { encryptFielCredentials } from './sat/sat-crypto.service';

// In uploadFiel(), after the prisma.fielCredential.upsert:

// Save to filesystem
const fielDir = join(env.FIEL_STORAGE_PATH, rfc.toUpperCase());
await mkdir(fielDir, { recursive: true, mode: 0o700 });

// Encrypt files for filesystem storage
const fsEncrypted = encryptFielCredentials(cerBuffer, keyBuffer, password);

await writeFile(join(fielDir, 'certificate.cer.enc'), fsEncrypted.encryptedCer, { mode: 0o600 });
await writeFile(join(fielDir, 'private_key.key.enc'), fsEncrypted.encryptedKey, { mode: 0o600 });

// Encrypt metadata too
const metadata = JSON.stringify({
  serial: serialNumber,
  validFrom: validFrom.toISOString(),
  validUntil: validUntil.toISOString(),
  uploadedAt: new Date().toISOString(),
  rfc: rfc.toUpperCase(),
});
const metaEncrypted = encryptBuffer(Buffer.from(metadata, 'utf-8'), deriveKey());
await writeFile(join(fielDir, 'metadata.json.enc'), metaEncrypted.encrypted, { mode: 0o600 });
// Also store IV and tag for metadata
await writeFile(join(fielDir, 'metadata.iv'), metaEncrypted.iv, { mode: 0o600 });
await writeFile(join(fielDir, 'metadata.tag'), metaEncrypted.tag, { mode: 0o600 });
```

- [ ] **Step 2: Update the upsert call to use new per-component columns**

```typescript
await prisma.fielCredential.upsert({
  where: { tenantId },
  create: {
    tenantId,
    rfc: rfc.toUpperCase(),
    cerData: encrypted.encryptedCer,
    keyData: encrypted.encryptedKey,
    keyPasswordEncrypted: encrypted.encryptedPassword,
    cerIv: encrypted.cerIv,
    cerTag: encrypted.cerTag,
    keyIv: encrypted.keyIv,
    keyTag: encrypted.keyTag,
    passwordIv: encrypted.passwordIv,
    passwordTag: encrypted.passwordTag,
    serialNumber,
    validFrom,
    validUntil,
  },
  update: {
    // same fields...
  },
});
```

- [ ] **Step 3: Update getDecryptedFiel to use new columns**

- [ ] **Step 4: Commit**

```bash
git add apps/api/src/services/fiel.service.ts
git commit -m "feat: add filesystem FIEL storage with per-component encryption"
```

---

### Task 13: Create FIEL decrypt CLI script

**Files:**
- Create: `apps/api/scripts/decrypt-fiel.ts`

- [ ] **Step 1: Write the CLI decrypt script**

```typescript
// apps/api/scripts/decrypt-fiel.ts
import { readFile, writeFile, mkdir, rm } from 'fs/promises';
import { join } from 'path';
import { createDecipheriv, createHash } from 'crypto';

const FIEL_PATH = process.env.FIEL_STORAGE_PATH || '/var/horux/fiel';
const FIEL_KEY = process.env.FIEL_ENCRYPTION_KEY;

const rfc = process.argv[2];
if (!rfc) {
  console.error('Usage: npx tsx scripts/decrypt-fiel.ts <RFC>');
  process.exit(1);
}
if (!FIEL_KEY) {
  console.error('FIEL_ENCRYPTION_KEY env var is required');
  process.exit(1);
}

async function main() {
  const fielDir = join(FIEL_PATH, rfc.toUpperCase());
  const outputDir = `/tmp/horux-fiel-${rfc.toUpperCase()}`;

  const key = createHash('sha256').update(FIEL_KEY!).digest();

  // Read encrypted files
  const cerEnc = await readFile(join(fielDir, 'certificate.cer.enc'));
  const keyEnc = await readFile(join(fielDir, 'private_key.key.enc'));

  // We need IV and tag — for filesystem, we'll read from metadata
  // For now, a simplified approach: store iv/tag alongside the .enc files
  // (This will be refined during implementation to match the storage format)

  await mkdir(outputDir, { recursive: true, mode: 0o700 });

  console.log(`Decrypted files written to: ${outputDir}`);
  console.log('Files will be auto-deleted in 30 minutes.');

  // Auto-delete after 30 minutes
  setTimeout(async () => {
    await rm(outputDir, { recursive: true, force: true });
    console.log(`Cleaned up ${outputDir}`);
    process.exit(0);
  }, 30 * 60 * 1000);
}

main().catch(console.error);
```

**Note:** The exact implementation will depend on how IVs and tags are stored on the filesystem. This will be refined during implementation to match the actual file storage format from Task 12.

- [ ] **Step 2: Commit**

```bash
git add apps/api/scripts/decrypt-fiel.ts
git commit -m "feat: add CLI script for decrypting FIEL credentials"
```

---

## Chunk 3: Email Service

### Task 14: Create email service and templates

**Files:**
- Create: `apps/api/src/services/email/email.service.ts`
- Create: `apps/api/src/services/email/templates/base.ts`
- Create: `apps/api/src/services/email/templates/welcome.ts`
- Create: `apps/api/src/services/email/templates/fiel-notification.ts`
- Create: `apps/api/src/services/email/templates/payment-confirmed.ts`
- Create: `apps/api/src/services/email/templates/payment-failed.ts`
- Create: `apps/api/src/services/email/templates/subscription-expiring.ts`
- Create: `apps/api/src/services/email/templates/subscription-cancelled.ts`

- [ ] **Step 1: Install nodemailer**

```bash
cd /root/Horux/apps/api && pnpm add nodemailer && pnpm add -D @types/nodemailer
```

- [ ] **Step 2: Create email service**

```typescript
// apps/api/src/services/email/email.service.ts
import { createTransport, Transporter } from 'nodemailer';
import { env } from '../../config/env';

let transporter: Transporter | null = null;

function getTransporter(): Transporter {
  if (!transporter) {
    if (!env.SMTP_USER || !env.SMTP_PASS) {
      console.warn('SMTP not configured. Emails will be logged to console.');
      // Return a mock transporter that logs to console
      return {
        sendMail: async (opts: any) => {
          console.log('[EMAIL] Would send:', { to: opts.to, subject: opts.subject });
          return { messageId: 'mock' };
        },
      } as any;
    }

    transporter = createTransport({
      host: env.SMTP_HOST,
      port: parseInt(env.SMTP_PORT),
      secure: false, // STARTTLS
      auth: {
        user: env.SMTP_USER,
        pass: env.SMTP_PASS,
      },
    });
  }
  return transporter;
}

async function sendEmail(to: string, subject: string, html: string, text?: string) {
  const transport = getTransporter();
  try {
    await transport.sendMail({
      from: env.SMTP_FROM,
      to,
      subject,
      html,
      text: text || html.replace(/<[^>]*>/g, ''),
    });
  } catch (error) {
    console.error('Error sending email:', error);
    // Don't throw — email failure shouldn't break the main flow
  }
}

// Public API
export const emailService = {
  sendWelcome: async (to: string, data: { nombre: string; email: string; tempPassword: string }) => {
    const { welcomeEmail } = await import('./templates/welcome');
    await sendEmail(to, 'Bienvenido a Horux360', welcomeEmail(data));
  },

  sendFielNotification: async (data: { clienteNombre: string; clienteRfc: string }) => {
    const { fielNotificationEmail } = await import('./templates/fiel-notification');
    await sendEmail(env.ADMIN_EMAIL, `[${data.clienteNombre}] subió su FIEL`, fielNotificationEmail(data));
  },

  sendPaymentConfirmed: async (to: string, data: { nombre: string; amount: number; plan: string; date: string }) => {
    const { paymentConfirmedEmail } = await import('./templates/payment-confirmed');
    await sendEmail(to, 'Confirmación de pago - Horux360', paymentConfirmedEmail(data));
  },

  sendPaymentFailed: async (to: string, data: { nombre: string; amount: number; plan: string }) => {
    const { paymentFailedEmail } = await import('./templates/payment-failed');
    await sendEmail(to, 'Problema con tu pago - Horux360', paymentFailedEmail(data));
    // Also notify admin
    await sendEmail(env.ADMIN_EMAIL, `Pago fallido: ${data.nombre}`, paymentFailedEmail(data));
  },

  sendSubscriptionExpiring: async (to: string, data: { nombre: string; plan: string; expiresAt: string }) => {
    const { subscriptionExpiringEmail } = await import('./templates/subscription-expiring');
    await sendEmail(to, 'Tu suscripción vence en 5 días', subscriptionExpiringEmail(data));
  },

  sendSubscriptionCancelled: async (to: string, data: { nombre: string; plan: string }) => {
    const { subscriptionCancelledEmail } = await import('./templates/subscription-cancelled');
    await sendEmail(to, 'Suscripción cancelada - Horux360', subscriptionCancelledEmail(data));
    await sendEmail(env.ADMIN_EMAIL, `Suscripción cancelada: ${data.nombre}`, subscriptionCancelledEmail(data));
  },
};
```

- [ ] **Step 3: Create base HTML template**

```typescript
// apps/api/src/services/email/templates/base.ts
export function baseTemplate(content: string): string {
  return `<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body style="margin:0;padding:0;background-color:#f4f4f5;font-family:Arial,sans-serif;">
  <table width="100%" cellpadding="0" cellspacing="0" style="background-color:#f4f4f5;padding:32px 0;">
    <tr>
      <td align="center">
        <table width="600" cellpadding="0" cellspacing="0" style="background-color:#ffffff;border-radius:8px;overflow:hidden;">
          <tr>
            <td style="background-color:#1e293b;padding:24px 32px;text-align:center;">
              <h1 style="color:#ffffff;margin:0;font-size:24px;">Horux360</h1>
            </td>
          </tr>
          <tr>
            <td style="padding:32px;">
              ${content}
            </td>
          </tr>
          <tr>
            <td style="background-color:#f8fafc;padding:16px 32px;text-align:center;font-size:12px;color:#94a3b8;">
              <p style="margin:0;">&copy; ${new Date().getFullYear()} Horux360 - Plataforma Fiscal Inteligente</p>
              <p style="margin:4px 0 0;">Consultoria Alcaraz Salazar</p>
            </td>
          </tr>
        </table>
      </td>
    </tr>
  </table>
</body>
</html>`;
}
```

- [ ] **Step 4: Create each email template**

Create welcome, fiel-notification, payment-confirmed, payment-failed, subscription-expiring, subscription-cancelled templates. Each uses `baseTemplate()` and receives typed data.

Example for `welcome.ts`:
```typescript
// apps/api/src/services/email/templates/welcome.ts
import { baseTemplate } from './base';

export function welcomeEmail(data: { nombre: string; email: string; tempPassword: string }): string {
  return baseTemplate(`
    <h2 style="color:#1e293b;margin:0 0 16px;">Bienvenido a Horux360</h2>
    <p style="color:#475569;line-height:1.6;">Hola ${data.nombre},</p>
    <p style="color:#475569;line-height:1.6;">Tu cuenta ha sido creada exitosamente. Aquí tienes tus credenciales de acceso:</p>
    <div style="background-color:#f1f5f9;border-radius:8px;padding:16px;margin:16px 0;">
      <p style="margin:0;color:#334155;"><strong>Email:</strong> ${data.email}</p>
      <p style="margin:8px 0 0;color:#334155;"><strong>Contraseña temporal:</strong> ${data.tempPassword}</p>
    </div>
    <p style="color:#475569;line-height:1.6;">Te recomendamos cambiar tu contraseña después de iniciar sesión.</p>
    <a href="https://horux360.consultoria-as.com/login" style="display:inline-block;background-color:#2563eb;color:#ffffff;padding:12px 24px;border-radius:6px;text-decoration:none;margin-top:16px;">Iniciar sesión</a>
  `);
}
```

- [ ] **Step 5: Commit**

```bash
git add apps/api/src/services/email/ apps/api/package.json pnpm-lock.yaml
git commit -m "feat: add email service with Nodemailer and 6 HTML templates"
```

---

## Chunk 4: MercadoPago Payments

### Task 15: Create MercadoPago service

**Files:**
- Create: `apps/api/src/services/payment/mercadopago.service.ts`

- [ ] **Step 1: Install MercadoPago SDK**

```bash
cd /root/Horux/apps/api && pnpm add mercadopago
```

- [ ] **Step 2: Create MercadoPago service**

Implement functions:
- `createSubscription(tenantId, plan, amount, email)` — creates a Preapproval and returns the init_point URL
- `getSubscription(mpPreapprovalId)` — gets subscription status from MP
- `verifyWebhookSignature(headers, body)` — validates webhook authenticity
- `getPaymentDetails(paymentId)` — gets payment info from MP API

- [ ] **Step 3: Commit**

```bash
git add apps/api/src/services/payment/
git commit -m "feat: add MercadoPago preapproval subscription service"
```

---

### Task 16: Create subscription service and webhook handler

**Files:**
- Create: `apps/api/src/services/payment/subscription.service.ts`
- Create: `apps/api/src/controllers/webhook.controller.ts`
- Create: `apps/api/src/routes/webhook.routes.ts`

- [ ] **Step 1: Create subscription service**

Functions:
- `createSubscription(tenantId, plan, amount, frequency)` — creates DB record + MP preapproval
- `getActiveSubscription(tenantId)` — cached 5 min TTL
- `updateSubscriptionStatus(mpPreapprovalId, status)` — updates DB + invalidates cache
- `recordPayment(tenantId, subscriptionId, mpPaymentId, amount, status, method)` — inserts payment
- `markAsPaidManually(tenantId, amount)` — for bank transfers
- `generatePaymentLink(tenantId)` — returns MP init_point URL

- [ ] **Step 2: Create webhook controller**

Handle `POST /api/webhooks/mercadopago`:
- Verify signature
- Handle `payment` type: fetch payment details from MP, record in DB
- Handle `subscription_preapproval` type: update subscription status
- On cancellation: mark tenant as inactive, send notification email
- Broadcast cache invalidation to all PM2 workers

- [ ] **Step 3: Create webhook routes**

```typescript
// apps/api/src/routes/webhook.routes.ts
import { Router } from 'express';
import { handleMercadoPagoWebhook } from '../controllers/webhook.controller';

const router = Router();

// Public endpoint — no auth middleware
router.post('/mercadopago', handleMercadoPagoWebhook);

export default router;
```

- [ ] **Step 4: Register route in app.ts**

Add to `apps/api/src/app.ts`:
```typescript
import webhookRoutes from './routes/webhook.routes';
app.use('/api/webhooks', webhookRoutes);
```

- [ ] **Step 5: Commit**

```bash
git add apps/api/src/services/payment/ apps/api/src/controllers/ apps/api/src/routes/ apps/api/src/app.ts
git commit -m "feat: add subscription service, webhook handler, and payment routes"
```

---

### Task 17: Create subscription admin endpoints

**Files:**
- Create: `apps/api/src/controllers/subscription.controller.ts`
- Create: `apps/api/src/routes/subscription.routes.ts`

- [ ] **Step 1: Create controller with admin endpoints**

Endpoints:
- `GET /api/subscriptions/:tenantId` — get subscription info
- `POST /api/subscriptions/:tenantId/generate-link` — generate payment link
- `POST /api/subscriptions/:tenantId/mark-paid` — manual payment
- `GET /api/subscriptions/:tenantId/payments` — payment history

- [ ] **Step 2: Create routes with auth + admin authorization**

- [ ] **Step 3: Register in app.ts**

- [ ] **Step 4: Commit**

```bash
git add apps/api/src/controllers/subscription.controller.ts apps/api/src/routes/subscription.routes.ts apps/api/src/app.ts
git commit -m "feat: add subscription admin endpoints for payment management"
```

---

## Chunk 5: Plan Enforcement

### Task 18: Create plan limits and feature gate middleware

**Files:**
- Create: `apps/api/src/middlewares/plan-limits.middleware.ts`
- Create: `apps/api/src/middlewares/feature-gate.middleware.ts`

- [ ] **Step 1: Create plan limits middleware**

```typescript
// apps/api/src/middlewares/plan-limits.middleware.ts
import { Request, Response, NextFunction } from 'express';
import { prisma } from '../config/prisma';

// Simple in-memory cache with TTL
const cache = new Map<string, { data: any; expires: number }>();

async function getCached<T>(key: string, ttlMs: number, fetcher: () => Promise<T>): Promise<T> {
  const entry = cache.get(key);
  if (entry && entry.expires > Date.now()) return entry.data;
  const data = await fetcher();
  cache.set(key, { data, expires: Date.now() + ttlMs });
  return data;
}

export function invalidateTenantCache(tenantId: string) {
  for (const key of cache.keys()) {
    if (key.includes(tenantId)) cache.delete(key);
  }
}

export async function checkPlanLimits(req: Request, res: Response, next: NextFunction) {
  if (!req.user) return next();

  // Admin impersonation bypasses subscription check
  if (req.headers['x-view-tenant'] && req.user.role === 'admin') {
    return next();
  }

  const subscription = await getCached(
    `sub:${req.user.tenantId}`,
    5 * 60 * 1000, // 5 min TTL
    () => prisma.subscription.findFirst({
      where: { tenantId: req.user!.tenantId },
      orderBy: { createdAt: 'desc' },
    })
  );

  const allowedStatuses = ['authorized', 'pending'];

  if (!subscription || !allowedStatuses.includes(subscription.status)) {
    if (req.method !== 'GET') {
      return res.status(403).json({
        message: 'Suscripción inactiva. Contacta soporte para reactivar.',
      });
    }
  }

  next();
}

export async function checkCfdiLimit(req: Request, res: Response, next: NextFunction) {
  if (!req.user || !req.tenantPool) return next();

  const tenant = await getCached(
    `tenant:${req.user.tenantId}`,
    5 * 60 * 1000,
    () => prisma.tenant.findUnique({
      where: { id: req.user!.tenantId },
      select: { cfdiLimit: true },
    })
  );

  if (!tenant || tenant.cfdiLimit === -1) return next(); // unlimited

  const countResult = await getCached(
    `cfdi-count:${req.user.tenantId}`,
    5 * 60 * 1000,
    async () => {
      const result = await req.tenantPool!.query('SELECT COUNT(*) FROM cfdis');
      return parseInt(result.rows[0].count);
    }
  );

  const newCount = Array.isArray(req.body) ? req.body.length : 1;
  if (countResult + newCount > tenant.cfdiLimit) {
    return res.status(403).json({
      message: `Límite de CFDIs alcanzado (${countResult}/${tenant.cfdiLimit}). Contacta soporte para upgrade.`,
    });
  }

  next();
}
```

- [ ] **Step 2: Create feature gate middleware**

```typescript
// apps/api/src/middlewares/feature-gate.middleware.ts
import { Request, Response, NextFunction } from 'express';
import { hasFeature } from '@horux/shared';
import { prisma } from '../config/prisma';

const planCache = new Map<string, { plan: string; expires: number }>();

export function requireFeature(feature: string) {
  return async (req: Request, res: Response, next: NextFunction) => {
    if (!req.user) return res.status(401).json({ message: 'No autenticado' });

    let plan: string;
    const cached = planCache.get(req.user.tenantId);
    if (cached && cached.expires > Date.now()) {
      plan = cached.plan;
    } else {
      const tenant = await prisma.tenant.findUnique({
        where: { id: req.user.tenantId },
        select: { plan: true },
      });
      if (!tenant) return res.status(404).json({ message: 'Tenant no encontrado' });
      plan = tenant.plan;
      planCache.set(req.user.tenantId, { plan, expires: Date.now() + 5 * 60 * 1000 });
    }

    if (!hasFeature(plan as any, feature)) {
      return res.status(403).json({
        message: 'Tu plan no incluye esta función. Contacta soporte para upgrade.',
      });
    }

    next();
  };
}
```

- [ ] **Step 3: Apply middleware to routes in app.ts**

Add `checkPlanLimits` to tenant-scoped routes and `checkCfdiLimit` to CFDI write endpoints. Add `requireFeature` to protected routes:

```typescript
// In route registration:
app.use('/api/cfdi', authenticate, tenantMiddleware, checkPlanLimits, cfdiRoutes);
// In cfdi routes, POST endpoints get additional checkCfdiLimit
router.post('/', checkCfdiLimit, createCfdi);
router.post('/bulk', checkCfdiLimit, createManyCfdis);

// Feature-gated routes:
app.use('/api/reportes', authenticate, tenantMiddleware, checkPlanLimits, requireFeature('reportes'), reportesRoutes);
app.use('/api/alertas', authenticate, tenantMiddleware, checkPlanLimits, requireFeature('alertas'), alertasRoutes);
app.use('/api/calendario', authenticate, tenantMiddleware, checkPlanLimits, requireFeature('calendario'), calendarioRoutes);
```

- [ ] **Step 4: Add PM2 message handler for cache invalidation**

```typescript
// In index.ts, extend the existing message handler:
process.on('message', (msg: any) => {
  if (msg?.type === 'invalidate-tenant-cache' && msg.tenantId) {
    tenantDb.invalidatePool(msg.tenantId);
    invalidateTenantCache(msg.tenantId); // from plan-limits middleware
  }
});
```

- [ ] **Step 5: Commit**

```bash
git add apps/api/src/middlewares/ apps/api/src/app.ts apps/api/src/index.ts
git commit -m "feat: add plan limits, CFDI limit check, and feature gating middleware"
```

---

## Chunk 6: Tenant Provisioning Flow (Full Integration)

### Task 19: Integrate email + subscription into tenant creation

**Files:**
- Modify: `apps/api/src/services/tenants.service.ts`

- [ ] **Step 1: Update createTenant to generate user, email, and subscription**

The full provisioning flow:
1. Provision database
2. Create tenant record
3. Create admin user with temp password
4. Create initial subscription record (status: pending)
5. Send welcome email

```typescript
import { emailService } from './email/email.service';
import { randomBytes } from 'crypto';

export async function createTenant(data: {
  nombre: string;
  rfc: string;
  plan?: string;
  cfdiLimit?: number;
  usersLimit?: number;
  adminEmail: string;
  adminNombre: string;
  amount: number;
}) {
  // ... provisioning code from Task 7 ...

  // After tenant is created:

  // Create admin user with temp password
  const tempPassword = randomBytes(4).toString('hex'); // 8-char random
  const hashedPassword = await bcrypt.hash(tempPassword, 10);

  const user = await prisma.user.create({
    data: {
      tenantId: tenant.id,
      email: data.adminEmail,
      passwordHash: hashedPassword,
      nombre: data.adminNombre,
      role: 'admin',
    },
  });

  // Create initial subscription
  await prisma.subscription.create({
    data: {
      tenantId: tenant.id,
      plan: (data.plan as any) || 'starter',
      status: 'pending',
      amount: data.amount,
      frequency: 'monthly',
    },
  });

  // Send welcome email (non-blocking)
  emailService.sendWelcome(data.adminEmail, {
    nombre: data.adminNombre,
    email: data.adminEmail,
    tempPassword,
  }).catch(err => console.error('Welcome email failed:', err));

  return { tenant, user, tempPassword };
}
```

- [ ] **Step 2: Update the tenants controller to accept new fields**

- [ ] **Step 3: Commit**

```bash
git add apps/api/src/services/tenants.service.ts apps/api/src/controllers/ apps/api/src/routes/
git commit -m "feat: integrate email and subscription into tenant provisioning flow"
```

---

### Task 20: Send FIEL notification email on upload

**Files:**
- Modify: `apps/api/src/services/fiel.service.ts`

- [ ] **Step 1: Add email notification after FIEL upload**

After successful FIEL upload in `uploadFiel()`:
```typescript
import { emailService } from './email/email.service';

// After saving to DB and filesystem:
const tenant = await prisma.tenant.findUnique({ where: { id: tenantId } });
if (tenant) {
  emailService.sendFielNotification({
    clienteNombre: tenant.nombre,
    clienteRfc: tenant.rfc,
  }).catch(err => console.error('FIEL notification email failed:', err));
}
```

- [ ] **Step 2: Commit**

```bash
git add apps/api/src/services/fiel.service.ts
git commit -m "feat: send admin notification email when client uploads FIEL"
```

---

## Chunk 7: Production Deployment

### Task 21: Create production PM2 config

**Files:**
- Modify: `ecosystem.config.js`

- [ ] **Step 1: Update ecosystem.config.js for production**

```javascript
module.exports = {
  apps: [
    {
      name: 'horux-api',
      script: 'dist/index.js',
      cwd: '/root/Horux/apps/api',
      instances: 2,
      exec_mode: 'cluster',
      autorestart: true,
      max_memory_restart: '1G',
      env: {
        NODE_ENV: 'production',
        PORT: 4000,
      },
    },
    {
      name: 'horux-web',
      script: 'node_modules/.bin/next',
      args: 'start',
      cwd: '/root/Horux/apps/web',
      instances: 1,
      exec_mode: 'fork',
      autorestart: true,
      max_memory_restart: '512M',
      env: {
        NODE_ENV: 'production',
        PORT: 3000,
      },
    },
  ],
};
```

- [ ] **Step 2: Commit**

```bash
git add ecosystem.config.js
git commit -m "feat: update PM2 config for production cluster mode"
```

---

### Task 22: Create Nginx config

**Files:**
- Create: `deploy/nginx/horux360.conf`

- [ ] **Step 1: Write Nginx site configuration**

Full Nginx config with SSL, rate limiting zones, security headers, and proxy rules as defined in the spec.

- [ ] **Step 2: Commit**

```bash
git add deploy/nginx/
git commit -m "feat: add Nginx reverse proxy config with SSL and rate limiting"
```

---

### Task 23: Create backup script

**Files:**
- Create: `scripts/backup.sh`

- [ ] **Step 1: Write backup script as defined in spec**

Daily/weekly rotation, .pgpass auth, empty file verification.

- [ ] **Step 2: Commit**

```bash
git add scripts/backup.sh
git commit -m "feat: add automated backup script with daily/weekly rotation"
```

---

### Task 24: PostgreSQL tuning

- [ ] **Step 1: Create PostgreSQL config tuning script**

```bash
# scripts/tune-postgres.sh
sudo -u postgres psql -c "ALTER SYSTEM SET max_connections = 300;"
sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '4GB';"
sudo -u postgres psql -c "ALTER SYSTEM SET work_mem = '16MB';"
sudo -u postgres psql -c "ALTER SYSTEM SET effective_cache_size = '16GB';"
sudo -u postgres psql -c "ALTER SYSTEM SET maintenance_work_mem = '512MB';"
sudo systemctl restart postgresql
```

- [ ] **Step 2: Commit**

```bash
git add scripts/tune-postgres.sh
git commit -m "feat: add PostgreSQL production tuning script"
```

---

### Task 25: Create FIEL storage directory and set up server

- [ ] **Step 1: Create required directories**

```bash
sudo mkdir -p /var/horux/fiel
sudo mkdir -p /var/horux/backups/daily
sudo mkdir -p /var/horux/backups/weekly
sudo chmod 700 /var/horux/fiel
sudo chmod 700 /var/horux/backups
```

- [ ] **Step 2: Set up .pgpass for backups**

```bash
echo "localhost:5432:*:postgres:<password>" > /root/.pgpass
chmod 600 /root/.pgpass
```

- [ ] **Step 3: Add backup cron job**

```bash
crontab -e
# Add: 0 1 * * * /var/horux/scripts/backup.sh >> /var/log/horux-backup.log 2>&1
```

---

### Task 26: Install and configure Nginx + SSL

- [ ] **Step 1: Install Nginx and Certbot**

```bash
apt update && apt install -y nginx certbot python3-certbot-nginx
```

- [ ] **Step 2: Deploy Nginx config**

```bash
cp deploy/nginx/horux360.conf /etc/nginx/sites-available/
ln -s /etc/nginx/sites-available/horux360.conf /etc/nginx/sites-enabled/
rm /etc/nginx/sites-enabled/default
nginx -t && systemctl restart nginx
```

- [ ] **Step 3: Get SSL certificate**

```bash
certbot --nginx -d horux360.consultoria-as.com
```

- [ ] **Step 4: Configure firewall**

```bash
ufw allow 22/tcp
ufw allow 80/tcp
ufw allow 443/tcp
ufw --force enable
```

---

### Task 27: Build and deploy production

- [ ] **Step 1: Build both apps**

```bash
cd /root/Horux
pnpm build
```

- [ ] **Step 2: Set up production environment**

Create `apps/api/.env.production` with all production values (JWT secret, FIEL key, MercadoPago tokens, SMTP credentials).

- [ ] **Step 3: Stop old services and start PM2**

```bash
systemctl stop horux-api horux-web
systemctl disable horux-api horux-web
npx pm2 start ecosystem.config.js
npx pm2 save
npx pm2 startup
```

- [ ] **Step 4: Apply PostgreSQL tuning**

```bash
bash scripts/tune-postgres.sh
```

- [ ] **Step 5: Verify everything works**

```bash
curl https://horux360.consultoria-as.com/api/health
npx pm2 status
```

- [ ] **Step 6: Flush old refresh tokens**

```bash
PGPASSWORD=<password> psql -h localhost -U postgres -d horux360 -c "DELETE FROM refresh_tokens;"
```

This forces all users to re-login and get new JWTs with `databaseName` instead of `schemaName`.

- [ ] **Step 7: Commit any final changes**

```bash
git add -A
git commit -m "feat: production deployment configuration complete"
```

---

## Chunk 8: Frontend Updates

### Task 28: Update frontend for plan display and subscription info

**Files:**
- Modify: `apps/web/lib/api/client.ts` — update tenant header logic if needed
- Create: `apps/web/lib/api/subscription.ts` — API client for subscription endpoints
- Create: `apps/web/app/(dashboard)/configuracion/suscripcion/page.tsx` — subscription info page (client view)
- Modify: `apps/web/app/(dashboard)/clientes/page.tsx` — add subscription status + payment link generation

- [ ] **Step 1: Create subscription API client**

```typescript
// apps/web/lib/api/subscription.ts
import { api } from './client';

export async function getSubscription(tenantId: string) {
  const { data } = await api.get(`/subscriptions/${tenantId}`);
  return data;
}

export async function generatePaymentLink(tenantId: string) {
  const { data } = await api.post(`/subscriptions/${tenantId}/generate-link`);
  return data;
}

export async function markAsPaid(tenantId: string, amount: number) {
  const { data } = await api.post(`/subscriptions/${tenantId}/mark-paid`, { amount });
  return data;
}

export async function getPaymentHistory(tenantId: string) {
  const { data } = await api.get(`/subscriptions/${tenantId}/payments`);
  return data;
}
```

- [ ] **Step 2: Create subscription info page for clients**

A page at `/configuracion/suscripcion` that shows:
- Current plan and status
- Next billing date
- Payment history table

- [ ] **Step 3: Update clientes page for admin**

Add to each client card:
- Subscription status badge
- "Generar link de pago" button
- "Marcar como pagado" button
- Payment history expandable section

- [ ] **Step 4: Update sidebar/navigation for feature gating**

In the navigation components, filter menu items based on the tenant's plan using `hasFeature()`.

- [ ] **Step 5: Commit**

```bash
git add apps/web/
git commit -m "feat: add subscription UI for clients and admin panel"
```

---

## Implementation Order Summary

| Order | Task | Description | Depends on |
|-------|------|-------------|------------|
| 1 | Task 1 | Environment config | — |
| 2 | Task 2 | Prisma schema migration | Task 1 |
| 3 | Task 3 | Shared types update | — |
| 4 | Task 4 | TenantConnectionManager | Task 1 |
| 5 | Task 5 | Tenant middleware rewrite | Tasks 3, 4 |
| 6 | Task 6 | Auth service update | Tasks 2, 3 |
| 7 | Task 7 | Tenants service rewrite | Tasks 4, 6 |
| 8 | Task 8 | All tenant services migration | Tasks 4, 5 |
| 9 | Task 9 | App entry + shutdown | Task 4 |
| 10 | Task 10 | Cleanup old schema-manager | Tasks 6, 7, 8 |
| 11 | Task 11 | SAT crypto update | Task 1 |
| 12 | Task 12 | FIEL dual storage | Tasks 2, 11 |
| 13 | Task 13 | FIEL decrypt CLI | Task 12 |
| 14 | Task 14 | Email service + templates | Task 1 |
| 15 | Task 15 | MercadoPago service | Task 1 |
| 16 | Task 16 | Subscription + webhooks | Tasks 2, 14, 15 |
| 17 | Task 17 | Subscription admin endpoints | Task 16 |
| 18 | Task 18 | Plan enforcement middleware | Tasks 2, 5 |
| 19 | Task 19 | Full provisioning integration | Tasks 7, 14, 16 |
| 20 | Task 20 | FIEL email notification | Tasks 12, 14 |
| 21 | Task 21 | PM2 production config | — |
| 22 | Task 22 | Nginx config | — |
| 23 | Task 23 | Backup script | — |
| 24 | Task 24 | PostgreSQL tuning | — |
| 25 | Task 25 | Server directories setup | — |
| 26 | Task 26 | Nginx + SSL install | Task 22 |
| 27 | Task 27 | Build + deploy | All above |
| 28 | Task 28 | Frontend updates | Tasks 16, 17, 18 |