// src/tests/integration/data-integrity.integration.test.ts
import { describe, it, expect, beforeAll, afterAll, vi } from 'vitest';
import supertest from 'supertest';
import { createAndLoginUser, TEST_PASSWORD } from '../utils/testHelpers';
import type { UserProfile } from '../../types';
import { getPool } from '../../services/db/connection.db';

/**
 * @vitest-environment node
 *
 * Integration tests for data integrity: FK constraints, cascades, unique constraints, and CHECK constraints.
 * These tests verify that database-level constraints are properly enforced.
 */

describe('Data Integrity Integration Tests', () => {
  let request: ReturnType<typeof supertest>;
  let adminToken: string;
  let adminUser: UserProfile;

  beforeAll(async () => {
    vi.stubEnv('FRONTEND_URL', 'https://example.com');
    const app = (await import('../../../server')).default;
    request = supertest(app);

    // Create an admin user for admin-level tests
    const { user, token } = await createAndLoginUser({
      email: `data-integrity-admin-${Date.now()}@example.com`,
      fullName: 'Data Integrity Admin',
      role: 'admin',
      request,
    });
    adminUser = user;
    adminToken = token;
  });

  afterAll(async () => {
    vi.unstubAllEnvs();
    // Clean up admin user
    await getPool().query('DELETE FROM public.users WHERE user_id = $1', [adminUser.user.user_id]);
  });

  describe('Cascade Deletes', () => {
    it('should cascade delete shopping lists when user is deleted', async () => {
      // Create a test user with shopping lists
      const { token } = await createAndLoginUser({
        email: `cascade-test-${Date.now()}@example.com`,
        fullName: 'Cascade Test User',
        request,
      });

      // Create some shopping lists
      const listResponse = await request
        .post('/api/users/shopping-lists')
        .set('Authorization', `Bearer ${token}`)
        .send({ name: 'Cascade Test List' });
      expect(listResponse.status).toBe(201);
      const listId = listResponse.body.data.shopping_list_id;

      // Verify list exists
      const checkListBefore = await getPool().query(
        'SELECT * FROM public.shopping_lists WHERE shopping_list_id = $1',
        [listId],
      );
      expect(checkListBefore.rows.length).toBe(1);

      // Delete the user account
      const deleteResponse = await request
        .delete('/api/users/account')
        .set('Authorization', `Bearer ${token}`)
        .send({ password: TEST_PASSWORD });
      expect(deleteResponse.status).toBe(200);

      // Verify list was cascade deleted
      const checkListAfter = await getPool().query(
        'SELECT * FROM public.shopping_lists WHERE shopping_list_id = $1',
        [listId],
      );
      expect(checkListAfter.rows.length).toBe(0);
    });

    it('should cascade delete budgets when user is deleted', async () => {
      // Create a test user with budgets
      const { token } = await createAndLoginUser({
        email: `budget-cascade-${Date.now()}@example.com`,
        fullName: 'Budget Cascade User',
        request,
      });

      // Create a budget
      const budgetResponse = await request
        .post('/api/budgets')
        .set('Authorization', `Bearer ${token}`)
        .send({
          name: 'Cascade Test Budget',
          amount_cents: 10000,
          period: 'weekly',
          start_date: '2025-01-01',
        });
      expect(budgetResponse.status).toBe(201);
      const budgetId = budgetResponse.body.data.budget_id;

      // Verify budget exists
      const checkBefore = await getPool().query(
        'SELECT * FROM public.budgets WHERE budget_id = $1',
        [budgetId],
      );
      expect(checkBefore.rows.length).toBe(1);

      // Delete the user account
      const deleteResponse = await request
        .delete('/api/users/account')
        .set('Authorization', `Bearer ${token}`)
        .send({ password: TEST_PASSWORD });
      expect(deleteResponse.status).toBe(200);

      // Verify budget was cascade deleted
      const checkAfter = await getPool().query(
        'SELECT * FROM public.budgets WHERE budget_id = $1',
        [budgetId],
      );
      expect(checkAfter.rows.length).toBe(0);
    });

    it('should cascade delete shopping list items when list is deleted', async () => {
      // Create a test user
      const { user, token } = await createAndLoginUser({
        email: `item-cascade-${Date.now()}@example.com`,
        fullName: 'Item Cascade User',
        request,
      });

      // Create a shopping list
      const listResponse = await request
        .post('/api/users/shopping-lists')
        .set('Authorization', `Bearer ${token}`)
        .send({ name: 'Item Cascade List' });
      expect(listResponse.status).toBe(201);
      const listId = listResponse.body.data.shopping_list_id;

      // Add an item to the list
      const itemResponse = await request
        .post(`/api/users/shopping-lists/${listId}/items`)
        .set('Authorization', `Bearer ${token}`)
        .send({ customItemName: 'Test Item', quantity: 1 });
      expect(itemResponse.status).toBe(201);
      const itemId = itemResponse.body.data.shopping_list_item_id;

      // Verify item exists
      const checkItemBefore = await getPool().query(
        'SELECT * FROM public.shopping_list_items WHERE shopping_list_item_id = $1',
        [itemId],
      );
      expect(checkItemBefore.rows.length).toBe(1);

      // Delete the shopping list
      const deleteResponse = await request
        .delete(`/api/users/shopping-lists/${listId}`)
        .set('Authorization', `Bearer ${token}`);
      expect(deleteResponse.status).toBe(204);

      // Verify item was cascade deleted
      const checkItemAfter = await getPool().query(
        'SELECT * FROM public.shopping_list_items WHERE shopping_list_item_id = $1',
        [itemId],
      );
      expect(checkItemAfter.rows.length).toBe(0);

      // Clean up user
      await getPool().query('DELETE FROM public.users WHERE user_id = $1', [user.user.user_id]);
    });
  });

  describe('Admin Self-Deletion Prevention', () => {
    it('should prevent admin from deleting their own account via admin route', async () => {
      const response = await request
        .delete(`/api/admin/users/${adminUser.user.user_id}`)
        .set('Authorization', `Bearer ${adminToken}`);

      expect(response.status).toBe(400);
      expect(response.body.success).toBe(false);
      expect(response.body.error.code).toBe('VALIDATION_ERROR');
      expect(response.body.error.message).toContain('cannot delete');
    });
  });

  describe('FK Constraint Enforcement', () => {
    it('should return error when adding item with invalid shopping list ID', async () => {
      // Create a test user
      const { user, token } = await createAndLoginUser({
        email: `fk-test-${Date.now()}@example.com`,
        fullName: 'FK Test User',
        request,
      });

      // Try to add item to non-existent list
      const response = await request
        .post('/api/users/shopping-lists/999999/items')
        .set('Authorization', `Bearer ${token}`)
        .send({ customItemName: 'Invalid List Item', quantity: 1 });

      expect(response.status).toBe(404);

      // Clean up
      await getPool().query('DELETE FROM public.users WHERE user_id = $1', [user.user.user_id]);
    });

    it('should enforce FK constraints at database level', async () => {
      // Try to insert directly into DB with invalid FK
      try {
        await getPool().query(
          `INSERT INTO public.shopping_list_items (shopping_list_id, custom_item_name, quantity)
           VALUES (999999999, 'Direct Insert Test', 1)`,
        );
        // If we get here, the constraint didn't fire
        expect.fail('Expected FK constraint violation');
      } catch (error) {
        // Expected - FK constraint should prevent this
        expect(error).toBeDefined();
        expect((error as Error).message).toContain('violates foreign key constraint');
      }
    });
  });

  describe('Unique Constraints', () => {
    it('should return CONFLICT for duplicate email registration', async () => {
      const email = `unique-test-${Date.now()}@example.com`;

      // Register first user
      const firstResponse = await request
        .post('/api/auth/register')
        .send({ email, password: TEST_PASSWORD, full_name: 'First User' });
      expect(firstResponse.status).toBe(201);

      // Try to register second user with same email
      const secondResponse = await request
        .post('/api/auth/register')
        .send({ email, password: TEST_PASSWORD, full_name: 'Second User' });

      expect(secondResponse.status).toBe(409); // CONFLICT
      expect(secondResponse.body.success).toBe(false);
      expect(secondResponse.body.error.code).toBe('CONFLICT');

      // Clean up first user
      const userId = firstResponse.body.data.userprofile.user.user_id;
      await getPool().query('DELETE FROM public.users WHERE user_id = $1', [userId]);
    });
  });

  describe('CHECK Constraints', () => {
    it('should reject budget with invalid period via API', async () => {
      const { user, token } = await createAndLoginUser({
        email: `check-test-${Date.now()}@example.com`,
        fullName: 'Check Constraint User',
        request,
      });

      const response = await request
        .post('/api/budgets')
        .set('Authorization', `Bearer ${token}`)
        .send({
          name: 'Invalid Period Budget',
          amount_cents: 10000,
          period: 'yearly', // Invalid - only weekly/monthly allowed
          start_date: '2025-01-01',
        });

      expect(response.status).toBe(400);
      expect(response.body.success).toBe(false);

      // Clean up
      await getPool().query('DELETE FROM public.users WHERE user_id = $1', [user.user.user_id]);
    });

    it('should reject budget with negative amount via API', async () => {
      const { user, token } = await createAndLoginUser({
        email: `amount-check-${Date.now()}@example.com`,
        fullName: 'Amount Check User',
        request,
      });

      const response = await request
        .post('/api/budgets')
        .set('Authorization', `Bearer ${token}`)
        .send({
          name: 'Negative Amount Budget',
          amount_cents: -100, // Invalid - must be positive
          period: 'weekly',
          start_date: '2025-01-01',
        });

      expect(response.status).toBe(400);
      expect(response.body.success).toBe(false);

      // Clean up
      await getPool().query('DELETE FROM public.users WHERE user_id = $1', [user.user.user_id]);
    });

    it('should enforce CHECK constraints at database level', async () => {
      // Try to insert directly with invalid period
      const { user, token: _ } = await createAndLoginUser({
        email: `db-check-${Date.now()}@example.com`,
        fullName: 'DB Check User',
        request,
      });

      try {
        await getPool().query(
          `INSERT INTO public.budgets (user_id, name, amount_cents, period, start_date)
           VALUES ($1, 'Direct Insert', 10000, 'yearly', '2025-01-01')`,
          [user.user.user_id],
        );
        // If we get here, the constraint didn't fire
        expect.fail('Expected CHECK constraint violation');
      } catch (error) {
        // Expected - CHECK constraint should prevent this
        expect(error).toBeDefined();
        expect((error as Error).message).toContain('violates check constraint');
      }

      // Clean up
      await getPool().query('DELETE FROM public.users WHERE user_id = $1', [user.user.user_id]);
    });
  });

  describe('NOT NULL Constraints', () => {
    it('should require budget name via API', async () => {
      const { user, token } = await createAndLoginUser({
        email: `notnull-test-${Date.now()}@example.com`,
        fullName: 'NotNull Test User',
        request,
      });

      const response = await request
        .post('/api/budgets')
        .set('Authorization', `Bearer ${token}`)
        .send({
          // name is missing - required field
          amount_cents: 10000,
          period: 'weekly',
          start_date: '2025-01-01',
        });

      expect(response.status).toBe(400);
      expect(response.body.success).toBe(false);
      expect(response.body.error.code).toBe('VALIDATION_ERROR');

      // Clean up
      await getPool().query('DELETE FROM public.users WHERE user_id = $1', [user.user.user_id]);
    });
  });

  describe('Transaction Rollback', () => {
    it('should rollback partial inserts on constraint violation', async () => {
      const pool = getPool();
      const client = await pool.connect();

      try {
        await client.query('BEGIN');

        // First insert should work
        const { user } = await createAndLoginUser({
          email: `transaction-test-${Date.now()}@example.com`,
          fullName: 'Transaction Test User',
          request,
        });

        await client.query(
          `INSERT INTO public.shopping_lists (user_id, name) VALUES ($1, 'Transaction Test List') RETURNING shopping_list_id`,
          [user.user.user_id],
        );

        // This should fail due to FK constraint
        await client.query(
          `INSERT INTO public.shopping_list_items (shopping_list_id, custom_item_name, quantity)
           VALUES (999999999, 'Should Fail', 1)`,
        );

        await client.query('COMMIT');
        expect.fail('Expected transaction to fail');
      } catch {
        await client.query('ROLLBACK');
        // Expected - transaction should have rolled back
      } finally {
        client.release();
      }
    });
  });
});
