namespace GB5Shared.Query.Framework.SchemaIntrospection
{
    /// <summary>
    /// Introspection SQL for the "own DB" path of the Analytics Catalog Wizard (Phase 1).
    /// Queries the SAME database the current request is already connected to via IQueryExecutor —
    /// no connection string involved. Branches on LoginDTO.DatabaseType (SQL Server vs PostgreSQL).
    /// </summary>
    public static class SchemaIntrospectionQB
    {
        // Index note: relies on system catalog views (INFORMATION_SCHEMA.*), no user-table indexes required.
        public const string GET_OWN_DB_COLUMNS_SQLSERVER = @"
SELECT t.TABLE_SCHEMA AS SchemaName, t.TABLE_NAME AS TableName,
       c.COLUMN_NAME AS ColumnName, c.DATA_TYPE AS DataType,
       c.CHARACTER_MAXIMUM_LENGTH AS MaxLength, c.NUMERIC_PRECISION AS NumericPrecision,
       c.NUMERIC_SCALE AS NumericScale, c.IS_NULLABLE AS IsNullable,
       c.ORDINAL_POSITION AS OrdinalPosition,
       CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
LEFT JOIN (
    SELECT ku.TABLE_SCHEMA, ku.TABLE_NAME, ku.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
      ON tc.CONSTRAINT_NAME=ku.CONSTRAINT_NAME AND tc.TABLE_SCHEMA=ku.TABLE_SCHEMA
    WHERE tc.CONSTRAINT_TYPE='PRIMARY KEY'
) pk ON pk.TABLE_SCHEMA=t.TABLE_SCHEMA AND pk.TABLE_NAME=t.TABLE_NAME AND pk.COLUMN_NAME=c.COLUMN_NAME
WHERE t.TABLE_TYPE='BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, c.ORDINAL_POSITION;";

        public const string GET_OWN_DB_COLUMNS_POSTGRES = @"
SELECT t.table_schema AS ""SchemaName"", t.table_name AS ""TableName"",
       c.column_name AS ""ColumnName"", c.data_type AS ""DataType"",
       c.character_maximum_length AS ""MaxLength"", c.numeric_precision AS ""NumericPrecision"",
       c.numeric_scale AS ""NumericScale"", c.is_nullable AS ""IsNullable"",
       c.ordinal_position AS ""OrdinalPosition"",
       CASE WHEN pk.column_name IS NOT NULL THEN 1 ELSE 0 END AS ""IsPrimaryKey""
FROM information_schema.tables t
JOIN information_schema.columns c ON c.table_schema=t.table_schema AND c.table_name=t.table_name
LEFT JOIN (
    SELECT ku.table_schema, ku.table_name, ku.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage ku
      ON tc.constraint_name=ku.constraint_name AND tc.table_schema=ku.table_schema
    WHERE tc.constraint_type='PRIMARY KEY'
) pk ON pk.table_schema=t.table_schema AND pk.table_name=t.table_name AND pk.column_name=c.column_name
WHERE t.table_type='BASE TABLE' AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY t.table_schema, t.table_name, c.ordinal_position;";

        // Index note: relies on sys.foreign_keys / sys.foreign_key_columns system catalog views.
        public const string GET_OWN_DB_FOREIGNKEYS_SQLSERVER = @"
SELECT fk.name AS ConstraintName,
       sch1.name AS FromSchema, tp.name AS FromTable, cp.name AS FromColumn,
       sch2.name AS ToSchema,   tr.name AS ToTable,   cr.name AS ToColumn
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables  tp  ON tp.object_id = fkc.parent_object_id
JOIN sys.schemas sch1 ON sch1.schema_id = tp.schema_id
JOIN sys.columns cp  ON cp.object_id = fkc.parent_object_id AND cp.column_id = fkc.parent_column_id
JOIN sys.tables  tr  ON tr.object_id = fkc.referenced_object_id
JOIN sys.schemas sch2 ON sch2.schema_id = tr.schema_id
JOIN sys.columns cr  ON cr.object_id = fkc.referenced_object_id AND cr.column_id = fkc.referenced_column_id
ORDER BY tp.name, fk.name, fkc.constraint_column_id;";

        public const string GET_OWN_DB_FOREIGNKEYS_POSTGRES = @"
SELECT tc.constraint_name AS ""ConstraintName"",
       kcu.table_schema AS ""FromSchema"", kcu.table_name AS ""FromTable"", kcu.column_name AS ""FromColumn"",
       ccu.table_schema AS ""ToSchema"",   ccu.table_name AS ""ToTable"",   ccu.column_name AS ""ToColumn""
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name AND kcu.table_schema = tc.table_schema
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY kcu.table_name, tc.constraint_name, kcu.ordinal_position;";
    }
}