import { describe, it, expect, beforeEach, afterAll } from 'vitest';
import BetterSqlite3 from 'better-sqlite3';
import { Kysely, SqliteDialect } from 'kysely';
import { SqlStorage } from './storage.js';
import { migrate, reset } from '../../infrastructure/db/schema.js';
import type { Database } from '../../infrastructure/db/database.js';
import type { FeedItem, FeedSource } from '../../interfaces/feed.types.js';

describe('SqlStorage', () => {
  let sqliteDb: BetterSqlite3.Database;
  let db: Kysely<Database>;
  let storage: SqlStorage;

  beforeEach(async () => {
    // Create in-memory database for each test
    sqliteDb = new BetterSqlite3(':memory:');
    sqliteDb.pragma('journal_mode = WAL');

    db = new Kysely<Database>({
      dialect: new SqliteDialect({
        database: sqliteDb,
      }),
    });

    // Reset and migrate
    await reset(db);
    await migrate(db);

    storage = new SqlStorage(db);
  });

  afterAll(async () => {
    await db.destroy();
  });

  describe('save', () => {
    it('saves a single item', async () => {
      const item: FeedItem = {
        id: 'abc123',
        source: 'https://example.com/feed.xml',
        title: 'Test Article',
        url: 'https://example.com/article',
        publishedAt: new Date('2024-09-06T09:00:00Z'),
        summary: 'A summary',
        content: 'Full content',
      };

      await storage.save([item]);

      const recent = await storage.getRecent(1);
      expect(recent).toHaveLength(1);
      expect(recent[0].id).toBe('abc123');
      expect(recent[0].title).toBe('Test Article');
    });

    it('saves multiple items in batch', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'Article 1',
          url: 'https://example.com/1',
          publishedAt: new Date('2024-09-06T10:00:00Z'),
        },
        {
          id: 'item2',
          source: 'https://example.com/feed.xml',
          title: 'Article 2',
          url: 'https://example.com/2',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
        },
      ];

      await storage.save(items);

      const recent = await storage.getRecent(10);
      expect(recent).toHaveLength(2);
    });

    it('handles empty array gracefully', async () => {
      await storage.save([]);
      const recent = await storage.getRecent(10);
      expect(recent).toHaveLength(0);
    });

    it('updates existing item on duplicate id (upsert)', async () => {
      const item: FeedItem = {
        id: 'same-id',
        source: 'https://example.com/feed.xml',
        title: 'Original Title',
        url: 'https://example.com/article',
        publishedAt: new Date('2024-09-06T09:00:00Z'),
        summary: 'Original summary',
      };

      await storage.save([item]);

      // Update with new title and content
      const updatedItem: FeedItem = {
        ...item,
        title: 'Updated Title',
        content: 'Updated content',
        summary: 'Updated summary',
      };

      await storage.save([updatedItem]);

      const recent = await storage.getRecent(1);
      expect(recent).toHaveLength(1);
      expect(recent[0].title).toBe('Updated Title');
      expect(recent[0].content).toBe('Updated content');
    });

    it('preserves other fields during upsert', async () => {
      const item: FeedItem = {
        id: 'preserved-id',
        source: 'https://example.com/feed.xml',
        title: 'Title',
        url: 'https://example.com/article',
        publishedAt: new Date('2024-09-06T09:00:00Z'),
        summary: 'Summary',
        content: 'Content',
      };

      await storage.save([item]);

      // Update only title
      const updatedItem: FeedItem = {
        ...item,
        title: 'New Title',
      };

      await storage.save([updatedItem]);

      const recent = await storage.getRecent(1);
      expect(recent[0].url).toBe('https://example.com/article'); // unchanged
      expect(recent[0].publishedAt).toEqual(new Date('2024-09-06T09:00:00Z')); // unchanged
    });

    it('saves and retrieves imageUrl', async () => {
      const item: FeedItem = {
        id: 'image-test',
        source: 'https://example.com/feed.xml',
        title: 'Article with Image',
        url: 'https://example.com/article',
        publishedAt: new Date('2024-09-06T09:00:00Z'),
        summary: 'Summary',
        imageUrl: 'https://example.com/image.jpg',
      };

      await storage.save([item]);

      const recent = await storage.getRecent(1);
      expect(recent).toHaveLength(1);
      expect(recent[0].imageUrl).toBe('https://example.com/image.jpg');
    });

    it('updates imageUrl on upsert', async () => {
      const item: FeedItem = {
        id: 'image-update-test',
        source: 'https://example.com/feed.xml',
        title: 'Article',
        url: 'https://example.com/article',
        publishedAt: new Date('2024-09-06T09:00:00Z'),
        imageUrl: 'https://example.com/old-image.jpg',
      };

      await storage.save([item]);

      // Update with new image URL
      const updatedItem: FeedItem = {
        ...item,
        imageUrl: 'https://example.com/new-image.jpg',
      };

      await storage.save([updatedItem]);

      const recent = await storage.getRecent(1);
      expect(recent[0].imageUrl).toBe('https://example.com/new-image.jpg');
    });
  });

  describe('getRecent', () => {
    it('returns items ordered by published date desc', async () => {
      const items: FeedItem[] = [
        {
          id: 'old',
          source: 'https://example.com/feed.xml',
          title: 'Old Article',
          url: 'https://example.com/old',
          publishedAt: new Date('2024-09-05T09:00:00Z'),
        },
        {
          id: 'new',
          source: 'https://example.com/feed.xml',
          title: 'New Article',
          url: 'https://example.com/new',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
        },
      ];

      await storage.save(items);

      const recent = await storage.getRecent(10);
      expect(recent[0].title).toBe('New Article');
      expect(recent[1].title).toBe('Old Article');
    });

    it('respects limit parameter', async () => {
      const items: FeedItem[] = Array.from({ length: 5 }, (_, i) => ({
        id: `item${i}`,
        source: 'https://example.com/feed.xml',
        title: `Article ${i}`,
        url: `https://example.com/${i}`,
        publishedAt: new Date(`2024-09-0${i + 1}T09:00:00Z`),
      }));

      await storage.save(items);

      const recent = await storage.getRecent(2);
      expect(recent).toHaveLength(2);
    });

    it('returns empty array when no items', async () => {
      const recent = await storage.getRecent(10);
      expect(recent).toHaveLength(0);
    });
  });

  describe('getBySource', () => {
    it('returns items filtered by source', async () => {
      const items: FeedItem[] = [
        {
          id: 'source1-item',
          source: 'https://source1.com/feed.xml',
          title: 'Source 1 Article',
          url: 'https://source1.com/article',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
        },
        {
          id: 'source2-item',
          source: 'https://source2.com/feed.xml',
          title: 'Source 2 Article',
          url: 'https://source2.com/article',
          publishedAt: new Date('2024-09-06T10:00:00Z'),
        },
      ];

      await storage.save(items);

      const source1Items = await storage.getBySource('https://source1.com/feed.xml', 10);
      expect(source1Items).toHaveLength(1);
      expect(source1Items[0].title).toBe('Source 1 Article');
    });

    it('respects limit parameter', async () => {
      const items: FeedItem[] = Array.from({ length: 5 }, (_, i) => ({
        id: `item${i}`,
        source: 'https://example.com/feed.xml',
        title: `Article ${i}`,
        url: `https://example.com/${i}`,
        publishedAt: new Date(`2024-09-0${i + 1}T09:00:00Z`),
      }));

      await storage.save(items);

      const sourceItems = await storage.getBySource('https://example.com/feed.xml', 2);
      expect(sourceItems).toHaveLength(2);
    });

    it('returns empty array when no matching source', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'Article',
          url: 'https://example.com/article',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
        },
      ];

      await storage.save(items);

      const result = await storage.getBySource('https://other.com/feed.xml', 10);
      expect(result).toHaveLength(0);
    });
  });

  describe('search', () => {
    it('finds items by title', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'JavaScript Tutorial',
          url: 'https://example.com/js',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
        },
        {
          id: 'item2',
          source: 'https://example.com/feed.xml',
          title: 'Python Guide',
          url: 'https://example.com/py',
          publishedAt: new Date('2024-09-06T10:00:00Z'),
        },
      ];

      await storage.save(items);

      const results = await storage.search('JavaScript');
      expect(results).toHaveLength(1);
      expect(results[0].title).toBe('JavaScript Tutorial');
    });

    it('finds items by summary', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'Article One',
          url: 'https://example.com/1',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
          summary: 'Learn about async programming',
        },
        {
          id: 'item2',
          source: 'https://example.com/feed.xml',
          title: 'Article Two',
          url: 'https://example.com/2',
          publishedAt: new Date('2024-09-06T10:00:00Z'),
          summary: 'Introduction to types',
        },
      ];

      await storage.save(items);

      const results = await storage.search('async');
      expect(results).toHaveLength(1);
      expect(results[0].title).toBe('Article One');
    });

    it('finds items by content', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'Article One',
          url: 'https://example.com/1',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
          content: 'Detailed explanation of closures',
        },
        {
          id: 'item2',
          source: 'https://example.com/feed.xml',
          title: 'Article Two',
          url: 'https://example.com/2',
          publishedAt: new Date('2024-09-06T10:00:00Z'),
          content: 'Overview of interfaces',
        },
      ];

      await storage.save(items);

      const results = await storage.search('closures');
      expect(results).toHaveLength(1);
      expect(results[0].title).toBe('Article One');
    });

    it('returns multiple matching items', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'React Tutorial',
          url: 'https://example.com/react',
          publishedAt: new Date('2024-09-05T09:00:00Z'),
        },
        {
          id: 'item2',
          source: 'https://example.com/feed.xml',
          title: 'Vue Guide',
          url: 'https://example.com/vue',
          publishedAt: new Date('2024-09-06T10:00:00Z'),
          summary: 'A framework tutorial',
        },
      ];

      await storage.save(items);

      const results = await storage.search('Tutorial');
      expect(results).toHaveLength(2);
    });

    it('returns empty array when no matches', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'Article',
          url: 'https://example.com/article',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
        },
      ];

      await storage.save(items);

      const results = await storage.search('nonexistent');
      expect(results).toHaveLength(0);
    });

    it('performs case-sensitive search (as per SQL LIKE)', async () => {
      const items: FeedItem[] = [
        {
          id: 'item1',
          source: 'https://example.com/feed.xml',
          title: 'JavaScript',
          url: 'https://example.com/js',
          publishedAt: new Date('2024-09-06T09:00:00Z'),
        },
      ];

      await storage.save(items);

      // SQLite LIKE is case-insensitive by default for ASCII
      const results = await storage.search('javascript');
      expect(results).toHaveLength(1);
    });
  });

  describe('FeedItem properties preservation', () => {
    it('preserves all FeedItem fields', async () => {
      const item: FeedItem = {
        id: 'full-item',
        source: 'https://example.com/feed.xml',
        title: 'Complete Article',
        url: 'https://example.com/complete',
        publishedAt: new Date('2024-09-06T09:30:00Z'),
        summary: 'A brief summary',
        content: 'The full article content here',
      };

      await storage.save([item]);

      const recent = await storage.getRecent(1);
      const retrieved = recent[0];

      expect(retrieved.id).toBe(item.id);
      expect(retrieved.source).toBe(item.source);
      expect(retrieved.title).toBe(item.title);
      expect(retrieved.url).toBe(item.url);
      expect(retrieved.publishedAt).toEqual(item.publishedAt);
      expect(retrieved.summary).toBe(item.summary);
      expect(retrieved.content).toBe(item.content);
    });

    it('handles items without optional fields', async () => {
      const item: FeedItem = {
        id: 'minimal-item',
        source: 'https://example.com/feed.xml',
        title: 'Minimal Article',
        url: 'https://example.com/minimal',
        publishedAt: new Date('2024-09-06T09:00:00Z'),
      };

      await storage.save([item]);

      const recent = await storage.getRecent(1);
      expect(recent[0].summary).toBeUndefined();
      expect(recent[0].content).toBeUndefined();
    });
  });

  describe('Feed Source Management', () => {
    function createTestFeedSource(overrides: Partial<FeedSource> = {}): FeedSource {
      const now = new Date();
      return {
        id: 'test-source-1',
        url: 'https://example.com/feed.xml',
        name: 'Test Feed',
        format: 'rss',
        pollIntervalMs: 60000,
        isActive: true,
        lastFetchedAt: null,
        lastSuccessAt: null,
        consecutiveFailures: 0,
        createdAt: now,
        updatedAt: now,
        ...overrides,
      };
    }

    describe('saveFeedSource', () => {
      it('saves a new feed source', async () => {
        const source = createTestFeedSource();
        await storage.saveFeedSource(source);

        const retrieved = await storage.getFeedSourceById(source.id);
        expect(retrieved).toBeTruthy();
        expect(retrieved!.id).toBe(source.id);
        expect(retrieved!.url).toBe(source.url);
        expect(retrieved!.name).toBe(source.name);
        expect(retrieved!.format).toBe(source.format);
        expect(retrieved!.pollIntervalMs).toBe(source.pollIntervalMs);
        expect(retrieved!.isActive).toBe(source.isActive);
      });

      it('updates existing feed source on duplicate id (upsert)', async () => {
        const source = createTestFeedSource({ id: 'same-id', name: 'Original Name' });
        await storage.saveFeedSource(source);

        const updated = createTestFeedSource({
          id: 'same-id',
          name: 'Updated Name',
          pollIntervalMs: 120000,
        });
        await storage.saveFeedSource(updated);

        const retrieved = await storage.getFeedSourceById('same-id');
        expect(retrieved!.name).toBe('Updated Name');
        expect(retrieved!.pollIntervalMs).toBe(120000);
      });
    });

    describe('getFeedSources', () => {
      it('returns all feed sources', async () => {
        const source1 = createTestFeedSource({ id: 'source-1', url: 'https://example1.com/feed.xml' });
        const source2 = createTestFeedSource({ id: 'source-2', url: 'https://example2.com/feed.xml' });

        await storage.saveFeedSource(source1);
        await storage.saveFeedSource(source2);

        const sources = await storage.getFeedSources();
        expect(sources).toHaveLength(2);
        expect(sources.map(s => s.id)).toContain('source-1');
        expect(sources.map(s => s.id)).toContain('source-2');
      });

      it('returns only active sources when activeOnly is true', async () => {
        const active = createTestFeedSource({ id: 'active', url: 'https://active.com/feed.xml', isActive: true });
        const inactive = createTestFeedSource({ id: 'inactive', url: 'https://inactive.com/feed.xml', isActive: false });

        await storage.saveFeedSource(active);
        await storage.saveFeedSource(inactive);

        const sources = await storage.getFeedSources(true);
        expect(sources).toHaveLength(1);
        expect(sources[0]!.id).toBe('active');
      });
    });

    describe('getFeedSourceById', () => {
      it('returns feed source by id', async () => {
        const source = createTestFeedSource({ id: 'specific-id' });
        await storage.saveFeedSource(source);

        const retrieved = await storage.getFeedSourceById('specific-id');
        expect(retrieved).toBeTruthy();
        expect(retrieved!.id).toBe('specific-id');
      });

      it('returns null for non-existent id', async () => {
        const retrieved = await storage.getFeedSourceById('non-existent');
        expect(retrieved).toBeNull();
      });
    });

    describe('updateFeedSourceStatus', () => {
      it('updates lastFetchedAt', async () => {
        const source = createTestFeedSource();
        await storage.saveFeedSource(source);

        const fetchTime = new Date('2024-09-06T10:00:00Z');
        await storage.updateFeedSourceStatus(source.id, { lastFetchedAt: fetchTime });

        const retrieved = await storage.getFeedSourceById(source.id);
        expect(retrieved!.lastFetchedAt).toEqual(fetchTime);
      });

      it('updates lastSuccessAt', async () => {
        const source = createTestFeedSource();
        await storage.saveFeedSource(source);

        const successTime = new Date('2024-09-06T10:00:00Z');
        await storage.updateFeedSourceStatus(source.id, { lastSuccessAt: successTime });

        const retrieved = await storage.getFeedSourceById(source.id);
        expect(retrieved!.lastSuccessAt).toEqual(successTime);
      });

      it('updates consecutiveFailures', async () => {
        const source = createTestFeedSource();
        await storage.saveFeedSource(source);

        await storage.updateFeedSourceStatus(source.id, { consecutiveFailures: 5 });

        const retrieved = await storage.getFeedSourceById(source.id);
        expect(retrieved!.consecutiveFailures).toBe(5);
      });

      it('updates isActive', async () => {
        const source = createTestFeedSource({ isActive: true });
        await storage.saveFeedSource(source);

        await storage.updateFeedSourceStatus(source.id, { isActive: false });

        const retrieved = await storage.getFeedSourceById(source.id);
        expect(retrieved!.isActive).toBe(false);
      });

      it('updates multiple fields at once', async () => {
        const source = createTestFeedSource();
        await storage.saveFeedSource(source);

        const now = new Date();
        await storage.updateFeedSourceStatus(source.id, {
          lastFetchedAt: now,
          lastSuccessAt: now,
          consecutiveFailures: 0,
        });

        const retrieved = await storage.getFeedSourceById(source.id);
        expect(retrieved!.lastFetchedAt).toEqual(now);
        expect(retrieved!.lastSuccessAt).toEqual(now);
        expect(retrieved!.consecutiveFailures).toBe(0);
      });
    });

    describe('deleteFeedSource', () => {
      it('deletes feed source by id', async () => {
        const source = createTestFeedSource({ id: 'to-delete' });
        await storage.saveFeedSource(source);

        await storage.deleteFeedSource('to-delete');

        const retrieved = await storage.getFeedSourceById('to-delete');
        expect(retrieved).toBeNull();
      });

      it('does not throw when deleting non-existent id', async () => {
        await expect(storage.deleteFeedSource('non-existent')).resolves.not.toThrow();
      });
    });
  });
});