import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { SQLiteManager } from '../../../storage/sqlite-manager';
import { randomUUID } from 'crypto';
import path from 'path';
import fs from 'fs/promises';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));

describe('Epic Integration Tests - Real SQLite', () => {
  let db: SQLiteManager;
  const testDbPath = path.join(__dirname, 'test-epic-integration.db');

  beforeEach(async () => {
    // Remove test database if it exists
    try {
      await fs.unlink(testDbPath);
    } catch (error) {
      // Ignore if file doesn't exist
    }

    // Create new database with real schema
    db = new SQLiteManager(testDbPath);
    await db.initialize();

    // Create test project
    await db.run(
      'INSERT INTO projects (id, name) VALUES (?, ?)',
      ['test-project', 'Test Project']
    );
  });

  afterEach(async () => {
    await db.close();
    try {
      await fs.unlink(testDbPath);
    } catch (error) {
      // Ignore cleanup errors
    }
  });

  it('should successfully insert epic with all columns (after schema fix)', async () => {
    const epicId = randomUUID();
    const now = Date.now();

    // This is the exact INSERT from the create_epic tool
    const result = await db.run(
      `INSERT INTO agile_epics 
       (id, title, description, goals, priority, owner, target_date, 
        success_criteria, repositories, status, project_id, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        epicId,
        'Test Epic',
        'Test Description',
        JSON.stringify(['Goal 1', 'Goal 2']),
        'high',
        'john.doe',
        new Date('2025-04-01').getTime(),
        JSON.stringify(['Criteria 1']),
        JSON.stringify(['repo1', 'repo2']),
        'planning',
        'test-project',
        now,
        now
      ]
    );

    // This should now succeed with the fixed schema
    if (!result.success) {
      console.error('Insert failed:', result.error);
    }
    expect(result.success).toBe(true);
    expect(result.data?.changes).toBe(1);

    // Verify the epic was created correctly
    const epic = await db.get(
      'SELECT * FROM agile_epics WHERE id = ?',
      [epicId]
    );

    expect(epic.success).toBe(true);
    expect(epic.data).toBeDefined();
    expect(epic.data.owner).toBe('john.doe');
    expect(epic.data.repositories).toBe(JSON.stringify(['repo1', 'repo2']));
  });

  it('should handle both target_date and target_quarter columns', async () => {
    const epicId = randomUUID();
    const now = Date.now();
    const targetDate = new Date('2025-04-01').getTime();

    // Insert using both target_date and target_quarter
    const result = await db.run(
      `INSERT INTO agile_epics 
       (id, title, description, goals, priority, target_date, target_quarter, 
        success_criteria, status, project_id, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        epicId,
        'Test Epic',
        'Test Description', 
        JSON.stringify(['Goal 1', 'Goal 2']),
        'high',
        targetDate,
        '2025-Q2',
        JSON.stringify(['Criteria 1']),
        'planning',
        'test-project',
        now,
        now
      ]
    );

    expect(result.success).toBe(true);
    expect(result.data?.changes).toBe(1);

    // Verify the epic was created with both date fields
    const epic = await db.get(
      'SELECT * FROM agile_epics WHERE id = ?',
      [epicId]
    );

    expect(epic.success).toBe(true);
    expect(epic.data).toBeDefined();
    expect(epic.data.target_date).toBe(targetDate);
    expect(epic.data.target_quarter).toBe('2025-Q2');
  });

  it('should list all columns in agile_epics table', async () => {
    const result = await db.query<{ name: string, type: string }>(
      "PRAGMA table_info(agile_epics)"
    );

    expect(result.success).toBe(true);
    
    const columnNames = result.data?.map(col => col.name) || [];
    console.log('Actual columns in agile_epics:', columnNames);

    // These columns should exist based on schema.sql
    expect(columnNames).toContain('id');
    expect(columnNames).toContain('title');
    expect(columnNames).toContain('description');
    expect(columnNames).toContain('goals');
    expect(columnNames).toContain('target_quarter');
    expect(columnNames).toContain('success_criteria');

    // These columns should now exist after the fix
    expect(columnNames).toContain('owner');
    expect(columnNames).toContain('target_date');
    expect(columnNames).toContain('repositories');
  });
});