import { describe, expect, it } from 'vitest';
import {
  parseSqlObjectHeader,
  objectKey,
  qualifiedName,
  buildDropStatement,
  toVerbatimLiteral,
  normalizeSql,
  matchBrace,
  injectSqlObjects,
  extractInlinedSql,
  computeChangedObjects,
  type SqlObject,
} from '../sql-objects.js';

function mk(sql: string, relPath = 'Functions/x.sql'): SqlObject {
  const header = parseSqlObjectHeader(sql)!;
  return { key: objectKey(header), header, sql, relPath };
}

const FN = `-- a function
CREATE OR ALTER FUNCTION [core].[fn_GetUserGroupHierarchy](@UserId UNIQUEIDENTIFIER)
RETURNS TABLE
AS
RETURN ( SELECT 1 AS GroupId );`;

// A realistic EF-generated migration with the two classic brace traps:
//   - a string default value "{}"
//   - an inline lambda block { ... } inside Up()
const MIG = `using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace App.Persistence.Migrations
{
    public partial class Init : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "auth_Groups",
                schema: "core",
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
                    ExtensionData = table.Column<string>(nullable: false, defaultValue: "{}")
                },
                constraints: table => { table.PrimaryKey("PK_auth_Groups", x => x.Id); });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(name: "auth_Groups", schema: "core");
        }
    }
}
`;

describe('parseSqlObjectHeader', () => {
  it('parses CREATE OR ALTER FUNCTION with [schema].[name]', () => {
    expect(parseSqlObjectHeader(FN)).toEqual({
      kind: 'FUNCTION',
      schema: 'core',
      name: 'fn_GetUserGroupHierarchy',
    });
  });

  it('parses a VIEW with dotted unbracketed identifiers', () => {
    expect(parseSqlObjectHeader('CREATE OR ALTER VIEW dbo.v_Active AS SELECT 1')).toEqual({
      kind: 'VIEW',
      schema: 'dbo',
      name: 'v_Active',
    });
  });

  it('normalizes PROC → PROCEDURE and tolerates plain CREATE (no OR ALTER)', () => {
    expect(parseSqlObjectHeader('CREATE PROC core.usp_Do AS SELECT 1')).toMatchObject({
      kind: 'PROCEDURE',
      schema: 'core',
      name: 'usp_Do',
    });
  });

  it('handles a missing schema', () => {
    expect(parseSqlObjectHeader('CREATE OR ALTER FUNCTION fn_NoSchema() RETURNS INT AS BEGIN RETURN 1 END')).toEqual({
      kind: 'FUNCTION',
      schema: null,
      name: 'fn_NoSchema',
    });
  });

  it('returns null for a file with no programmable-object header', () => {
    expect(parseSqlObjectHeader('-- just a comment\nSELECT 1;')).toBeNull();
  });
});

describe('identifiers & drops', () => {
  it('quotes schema-qualified names', () => {
    const h = parseSqlObjectHeader(FN)!;
    expect(qualifiedName(h)).toBe('[core].[fn_GetUserGroupHierarchy]');
    expect(buildDropStatement(h)).toBe('DROP FUNCTION IF EXISTS [core].[fn_GetUserGroupHierarchy];');
  });

  it('omits the schema when there is none', () => {
    const h = parseSqlObjectHeader('CREATE OR ALTER VIEW v_X AS SELECT 1')!;
    expect(qualifiedName(h)).toBe('[v_X]');
    expect(buildDropStatement(h)).toBe('DROP VIEW IF EXISTS [v_X];');
  });

  it('keys are case-insensitive', () => {
    expect(objectKey({ kind: 'VIEW', schema: 'Core', name: 'V_X' })).toBe('core.v_x');
  });
});

describe('toVerbatimLiteral', () => {
  it('wraps in @"" and doubles embedded quotes', () => {
    expect(toVerbatimLiteral('SELECT "x"')).toBe('@"SELECT ""x"""');
  });
  it('normalizes CRLF to LF and trims trailing whitespace', () => {
    expect(toVerbatimLiteral('A\r\nB   ')).toBe('@"A\nB"');
  });
});

describe('matchBrace — string/comment aware', () => {
  it('matches a simple block', () => {
    const s = '{ a }';
    expect(matchBrace(s, 0)).toBe(s.length - 1);
  });
  it('matches nested blocks', () => {
    const s = 'x { a { b } c } y';
    expect(s[matchBrace(s, 2)]).toBe('}');
    expect(matchBrace(s, 2)).toBe(14); // the outer closing brace
  });
  it('ignores braces inside a regular string ("{}" default value)', () => {
    const s = '{ var x = "{}"; }';
    expect(matchBrace(s, 0)).toBe(s.length - 1);
  });
  it('ignores braces inside a verbatim string', () => {
    const s = '{ var x = @"a { b } c"; }';
    expect(matchBrace(s, 0)).toBe(s.length - 1);
  });
  it('ignores braces inside line and block comments', () => {
    const s = '{ // }\n /* } */ x }';
    expect(matchBrace(s, 0)).toBe(s.length - 1);
  });
});

describe('injectSqlObjects', () => {
  const out = injectSqlObjects(MIG, [mk(FN)]);

  it('inlines the Sql() call INSIDE Up(), not after the class (brace trap survived)', () => {
    const marker = out.indexOf('[smartstack:sqlobject] core.fn_getusergrouphierarchy');
    const down = out.indexOf('void Down');
    expect(marker).toBeGreaterThan(-1);
    expect(marker).toBeLessThan(down); // landed in Up(), before Down()
    expect(out).toContain('migrationBuilder.Sql(@"-- a function');
  });

  it('adds the DROP at the START of Down(), before EF drops the table', () => {
    const drop = out.indexOf('DROP FUNCTION IF EXISTS [core].[fn_GetUserGroupHierarchy]');
    const dropTable = out.indexOf('DropTable');
    expect(drop).toBeGreaterThan(-1);
    expect(drop).toBeLessThan(dropTable);
  });

  it('uses the detected MigrationBuilder parameter name', () => {
    const renamed = MIG.replace(/migrationBuilder/g, 'mb');
    const r = injectSqlObjects(renamed, [mk(FN)]);
    expect(r).toContain('mb.Sql(@"-- a function');
    expect(r).not.toContain('migrationBuilder.Sql');
  });

  it('is a no-op when there are no objects', () => {
    expect(injectSqlObjects(MIG, [])).toBe(MIG);
  });

  it('leaves the source untouched when Up() cannot be found', () => {
    const noUp = 'public class X {}';
    expect(injectSqlObjects(noUp, [mk(FN)])).toBe(noUp);
  });

  it('produces output whose Up() block still brace-matches (re-injectable)', () => {
    // The injected text keeps the file structurally valid: Up() still closes.
    const open = out.indexOf('{', out.indexOf('void Up'));
    expect(matchBrace(out, open)).toBeGreaterThan(open);
  });
});

describe('extractInlinedSql + computeChangedObjects (round-trip)', () => {
  const injected = injectSqlObjects(MIG, [mk(FN)]);

  it('extracts the inlined SQL keyed by schema.name', () => {
    const map = extractInlinedSql(injected);
    expect(map.has('core.fn_getusergrouphierarchy')).toBe(true);
    expect(map.get('core.fn_getusergrouphierarchy')).toBe(normalizeSql(FN));
  });

  it('treats an unchanged object as NOT changed (idempotent)', () => {
    expect(computeChangedObjects([mk(FN)], [injected])).toEqual([]);
  });

  it('treats a brand-new object (never inlined) as changed', () => {
    const view = mk('CREATE OR ALTER VIEW core.v_New AS SELECT 1', 'Views/v_New.sql');
    expect(computeChangedObjects([view], [injected]).map((o) => o.key)).toEqual(['core.v_new']);
  });

  it('detects a real body change', () => {
    const changed = mk(FN.replace('SELECT 1', 'SELECT 2'));
    expect(computeChangedObjects([changed], [injected]).map((o) => o.key)).toEqual([
      'core.fn_getusergrouphierarchy',
    ]);
  });

  it('ignores trailing-whitespace and CRLF-only differences (git/editor noise)', () => {
    const reformatted = mk(FN.split('\n').map((l) => `${l}   `).join('\r\n'));
    expect(computeChangedObjects([reformatted], [injected])).toEqual([]);
  });

  it('uses the LATEST inlined copy when an object appears in several migrations', () => {
    const older = injectSqlObjects(MIG, [mk(FN)]);
    const newer = injectSqlObjects(MIG, [mk(FN.replace('SELECT 1', 'SELECT 99'))]);
    // current == newest → no change; current == oldest → changed
    const current = mk(FN.replace('SELECT 1', 'SELECT 99'));
    expect(computeChangedObjects([current], [older, newer])).toEqual([]);
    expect(computeChangedObjects([mk(FN)], [older, newer]).map((o) => o.key)).toEqual([
      'core.fn_getusergrouphierarchy',
    ]);
  });
});