import { spawnSync } from 'node:child_process';
import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import { describe, expect, it } from 'vitest';

const root = process.cwd();
const migrationSql = () => readFileSync(join(root, 'migrations', '0001_init.sql'), 'utf8');

describe('initial D1 schema migration', () => {
  it('creates the required application tables', () => {
    const sql = migrationSql();

    for (const table of [
      'users',
      'oauth_accounts',
      'sessions',
      'links',
      'tags',
      'link_tags',
      'promotion_submissions',
      'click_daily',
    ]) {
      expect(sql).toContain(`CREATE TABLE ${table} (`);
    }
  });

  it('defines scoped alias and lookup indexes for links and promotions', () => {
    const sql = migrationSql();

    expect(sql).toContain('CREATE UNIQUE INDEX links_public_alias_unique');
    expect(sql).toContain('CREATE UNIQUE INDEX links_private_owner_alias_unique');
    expect(sql).toContain('CREATE INDEX links_owner_idx ON links(owner_user_id, updated_at DESC);');
    expect(sql).toContain('CREATE INDEX links_scope_updated_idx ON links(scope, updated_at DESC);');
    expect(sql).toContain('CREATE INDEX promotion_status_idx ON promotion_submissions(status, created_at DESC);');
  });

  it('enforces non-deleted link alias uniqueness in SQLite', () => {
    const migrationPath = join(root, 'migrations', '0001_init.sql');
    const pythonCheck = spawnSync('python3', ['-c', 'import sqlite3'], { encoding: 'utf8' });
    if (pythonCheck.error || pythonCheck.status !== 0) {
      console.warn('Skipping SQLite schema-backed test: python3 sqlite3 module is unavailable');
      return;
    }

    const script = String.raw`
import sqlite3
import sys

con = sqlite3.connect(':memory:')
con.executescript(open(sys.argv[1], encoding='utf-8').read())
con.execute("INSERT INTO users (id, email) VALUES ('user_1', 'user_1@example.com')")

def insert_link(id, scope, owner_user_id, alias, status):
    con.execute(
        '''INSERT INTO links (id, scope, owner_user_id, alias, link_type, target_url, content_markdown, status)
           VALUES (?, ?, ?, ?, 'redirect', 'https://example.com', NULL, ?)''',
        (id, scope, owner_user_id, alias, status),
    )

def expect_integrity(fn):
    try:
        fn()
    except sqlite3.IntegrityError:
        return
    raise AssertionError('expected sqlite3.IntegrityError')

insert_link('private_archived', 'private', 'user_1', 'docs', 'archived')
expect_integrity(lambda: insert_link('private_active_conflict', 'private', 'user_1', 'docs', 'active'))
insert_link('private_deleted', 'private', 'user_1', 'gone', 'deleted')
insert_link('private_active_reuse', 'private', 'user_1', 'gone', 'active')

insert_link('public_archived', 'public', None, 'pub', 'archived')
expect_integrity(lambda: insert_link('public_active_conflict', 'public', None, 'pub', 'active'))
insert_link('public_deleted', 'public', None, 'oldpub', 'deleted')
insert_link('public_active_reuse', 'public', None, 'oldpub', 'active')

print('ok')
`;
    const result = spawnSync('python3', ['-c', script, migrationPath], { encoding: 'utf8' });

    expect(result.stderr).toBe('');
    expect(result.status).toBe(0);
    expect(result.stdout.trim()).toBe('ok');
  });
});