catalog

var (
	ErrDuckDB     = errors.NewKind("duckdb: %v")
	ErrTranspiler = errors.NewKind("transpiler: %v")
)

var (
	SchemaNameSYS           string = "__sys__"
	MacroNameMyListContains string = "my_list_contains"

	MacroNameMySplitListStr string = "my_split_list_str"

	MacroNameMyCRC32 string = "mysql_crc32"
)

var ErrSensitiveSQL = errors.New("statement uses a service-managed SQL feature")

var InitialDataTables = struct {
	PGNamespace [][]any
	PGRange     [][]any
}{
	PGNamespace: [][]any{
		{"99", "pg_toast", "10", ""},
		{"11", "pg_catalog", "10", "{postgres=UC/postgres,=U/postgres}"},
		{"2200", "public", "6171", "{pg_database_owner,=UC/pg_database_owner,=U/pg_database_owner}"},
		{"13219", "information_schema", "10", "{postgres=UC/postgres,=U/postgres}"},
		{"16395", "test_schema", "10", ""},
	},
	PGRange: [][]any{
		{"3904", "23", "4451", "0", "1978", "int4range_canonical", "int4range_subdiff"},
		{"3906", "1700", "4532", "0", "3125", "-", "numrange_subdiff"},
		{"3908", "1114", "4533", "0", "3128", "-", "tsrange_subdiff"},
		{"3910", "1184", "4534", "0", "3127", "-", "tstzrange_subdiff"},
		{"3912", "1082", "4535", "0", "3122", "daterange_canonical", "daterange_subdiff"},
		{"3926", "20", "4536", "0", "3124", "int8range_canonical", "int8range_subdiff"},
	},
}

var InternalMacros = []InternalMacro{
	{
		Schema:       "information_schema",
		Name:         "_pg_expandarray",
		IsTableMacro: true,
		Definitions: []MacroDefinition{
			{
				Params: []string{"a"},
				DDL: `SELECT STRUCT_PACK(
    x := unnest(a),
    n := generate_series(1, array_length(a))
) AS item`,
			},
		},
	},
	{
		Schema:       "pg_catalog",
		Name:         "pg_get_indexdef",
		IsTableMacro: false,
		Definitions: []MacroDefinition{
			{
				Params: []string{"index_oid"},

				DDL: `''`,
			},
			{
				Params: []string{"index_oid", "column_no", "pretty_bool"},

				DDL: `''`,
			},
		},
	},
	{
		Schema:       "pg_catalog",
		Name:         "pg_get_expr",
		IsTableMacro: false,
		Definitions: []MacroDefinition{
			{
				Params: []string{"pg_node_tree", "relation_oid"},

				DDL: `pg_catalog.pg_get_expr(pg_node_tree, relation_oid)`,
			},
			{
				Params: []string{"pg_node_tree", "relation_oid", "pretty_bool"},

				DDL: `pg_catalog.pg_get_expr(pg_node_tree, relation_oid)`,
			},
		},
	},
	{
		Schema:       SchemaNameSYS,
		Name:         MacroNameMyListContains,
		IsTableMacro: false,
		Definitions: []MacroDefinition{
			{
				Params: []string{"l", "v"},
				DDL: `CASE
    WHEN typeof(l) = 'VARCHAR' THEN
        list_contains(regexp_split_to_array(l::VARCHAR, '[{},\s]+'), v)
    ELSE
        list_contains(l::text[], v)
    END`,
			},
		},
	},
	{
		Schema:       SchemaNameSYS,
		Name:         MacroNameMySplitListStr,
		IsTableMacro: false,
		Definitions: []MacroDefinition{
			{
				Params: []string{"l"},
				DDL:    `regexp_split_to_array(l::VARCHAR, '[{},\s]+')`,
			},
		},
	},
	{
		Schema:       SchemaNameSYS,
		Name:         MacroNameMyCRC32,
		IsTableMacro: false,
		Definitions: []MacroDefinition{
			{
				Params: []string{"s"},
				DDL: `xor(
    list_reduce(
      list_prepend(
        4294967295,
        list_transform(
          range(1, length(CAST(s AS TEXT)) + 1),
          i -> ascii(substring(CAST(s AS TEXT), i, 1))
        )
      ),
      (crc, b) -> xor(
        crc // 256,
        [0,1996959894,3993919788,2567524794,124634137,1886057615,3915621685,2657392035,249268274,2044508324,3772115230,2547177864,162941995,2125561021,3887607047,2428444049,498536548,1789927666,4089016648,2227061214,450548861,1843258603,4107580753,2211677639,325883990,1684777152,4251122042,2321926636,335633487,1661365465,4195302755,2366115317,997073096,1281953886,3579855332,2724688242,1006888145,1258607687,3524101629,2768942443,901097722,1119000684,3686517206,2898065728,853044451,1172266101,3705015759,2882616665,651767980,1373503546,3369554304,3218104598,565507253,1454621731,3485111705,3099436303,671266974,1594198024,3322730930,2970347812,795835527,1483230225,3244367275,3060149565,1994146192,31158534,2563907772,4023717930,1907459465,112637215,2680153253,3904427059,2013776290,251722036,2517215374,3775830040,2137656763,141376813,2439277719,3865271297,1802195444,476864866,2238001368,4066508878,1812370925,453092731,2181625025,4111451223,1706088902,314042704,2344532202,4240017532,1658658271,366619977,2362670323,4224994405,1303535960,984961486,2747007092,3569037538,1256170817,1037604311,2765210733,3554079995,1131014506,879679996,2909243462,3663771856,1141124467,855842277,2852801631,3708648649,1342533948,654459306,3188396048,3373015174,1466479909,544179635,3110523913,3462522015,1591671054,702138776,2966460450,3352799412,1504918807,783551873,3082640443,3233442989,3988292384,2596254646,62317068,1957810842,3939845945,2647816111,81470997,1943803523,3814918930,2489596804,225274430,2053790376,3826175755,2466906013,167816743,2097651377,4027552580,2265490386,503444072,1762050814,4150417245,2154129355,426522225,1852507879,4275313526,2312317920,282753626,1742555852,4189708143,2394877945,397917763,1622183637,3604390888,2714866558,953729732,1340076626,3518719985,2797360999,1068828381,1219638859,3624741850,2936675148,906185462,1090812512,3747672003,2825379669,829329135,1181335161,3412177804,3160834842,628085408,1382605366,3423369109,3138078467,570562233,1426400815,3317316542,2998733608,733239954,1555261956,3268935591,3050360625,752459403,1541320221,2607071920,3965973030,1969922972,40735498,2617837225,3943577151,1913087877,83908371,2512341634,3803740692,2075208622,213261112,2463272603,3855990285,2094854071,198958881,2262029012,4057260610,1759359992,534414190,2176718541,4139329115,1873836001,414664567,2282248934,4279200368,1711684554,285281116,2405801727,4167216745,1634467795,376229701,2685067896,3608007406,1308918612,956543938,2808555105,3495958263,1231636301,1047427035,2932959818,3654703836,1088359270,936918000,2847714899,3736837829,1202900863,817233897,3183342108,3401237130,1404277552,615818150,3134207493,3453421203,1423857449,601450431,3009837614,3294710456,1567103746,711928724,3020668471,3272380065,1510334235,755167117][1 + (xor(crc, b) % 256)]
      )
    ),
    4294967295
  )`,
			},
		},
	},
}

var InternalSchemas = struct {
	SYS   InternalSchema
	MySQL InternalSchema
}{
	SYS: InternalSchema{
		Schema: "__sys__",
	},
	MySQL: InternalSchema{
		Schema: "mysql",
	},
}

var InternalTables = struct {
	PersistentVariable InternalTable
	BinlogPosition     InternalTable
	PgSubscription     InternalTable
	GlobalStatus       InternalTable
	// TODO(sean): This is a temporary work around for clients that query the 'pg_catalog.pg_stat_replication'.
	//             Once we add 'pg_catalog' and support views for PG, replace this by a view.
	//             https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW
	PGStatReplication InternalTable
	PGRange           InternalTable
	PGType            InternalTable
	PGProc            InternalTable
	PGClass           InternalTable
	PGNamespace       InternalTable
	PGMatViews        InternalTable
}{
	PersistentVariable: InternalTable{
		Schema:       "__sys__",
		Name:         "persistent_variable",
		KeyColumns:   []string{"name"},
		ValueColumns: []string{"value", "vtype"},
		DDL:          "name TEXT PRIMARY KEY, value TEXT, vtype TEXT",
	},
	BinlogPosition: InternalTable{
		Schema:       "__sys__",
		Name:         "binlog_position",
		KeyColumns:   []string{"channel"},
		ValueColumns: []string{"position"},
		DDL:          "channel TEXT PRIMARY KEY, position TEXT",
	},
	PgSubscription: InternalTable{
		Schema:       "__sys__",
		Name:         "pg_subscription",
		KeyColumns:   []string{"subname"},
		ValueColumns: []string{"subconninfo", "subpublication", "subskiplsn", "subenabled"},
		DDL:          "subname TEXT PRIMARY KEY, subconninfo TEXT, subpublication TEXT, subskiplsn TEXT, subenabled BOOLEAN",
	},
	GlobalStatus: InternalTable{
		Schema:       "performance_schema",
		Name:         "global_status",
		KeyColumns:   []string{"VARIABLE_NAME"},
		ValueColumns: []string{"VARIABLE_VALUE"},
		DDL:          "VARIABLE_NAME TEXT PRIMARY KEY, VARIABLE_VALUE TEXT",
		InitialData: [][]any{
			{"Innodb_redo_log_enabled", "OFF"},
		},
	},

	PGStatReplication: InternalTable{

		Schema: "__sys__",
		Name:   "pg_stat_replication",
		KeyColumns: []string{
			"pid",
		},
		ValueColumns: []string{
			"usesysid",
			"usename",
			"application_name",
			"client_addr",
			"client_hostname",
			"client_port",
			"backend_start",
			"backend_xmin",
			"state",
			"sent_lsn",
			"write_lsn",
			"flush_lsn",
			"replay_lsn",
			"write_lag",
			"flush_lag",
			"replay_lag",
			"sync_priority",
			"sync_state",
			"reply_time",
		},
		DDL: "pid INTEGER PRIMARY KEY, usesysid TEXT, usename TEXT, application_name TEXT, client_addr TEXT, client_hostname TEXT, client_port INTEGER, backend_start TIMESTAMP, backend_xmin INTEGER, state TEXT, sent_lsn TEXT, write_lsn TEXT, flush_lsn TEXT, replay_lsn TEXT, write_lag INTERVAL, flush_lag INTERVAL, replay_lag INTERVAL, sync_priority INTEGER, sync_state TEXT, reply_time TIMESTAMP",
	},
	PGRange: InternalTable{
		Schema:       "__sys__",
		Name:         "pg_range",
		KeyColumns:   []string{"rngtypid"},
		ValueColumns: []string{"rngsubtype", "rngmultitypid", "rngcollation", "rngsubopc", "rngcanonical", "rngsubdiff"},
		DDL:          "rngtypid BIGINT PRIMARY KEY, rngsubtype BIGINT, rngmultitypid BIGINT, rngcollation BIGINT, rngsubopc BIGINT, rngcanonical VARCHAR, rngsubdiff VARCHAR",
		InitialData:  InitialDataTables.PGRange,
	},

	PGType: InternalTable{
		Schema: "__sys__",
		Name:   "pg_type",
		KeyColumns: []string{
			"oid",
		},
		ValueColumns: []string{
			"typname", "typnamespace", "typowner", "typlen", "typbyval",
			"typtype", "typcategory", "typispreferred", "typisdefined", "typdelim",
			"typrelid", "typsubscript", "typelem", "typarray", "typinput",
			"typoutput", "typreceive", "typsend", "typmodin", "typmodout",
			"typanalyze", "typalign", "typstorage", "typnotnull", "typbasetype",
			"typtypmod", "typndims", "typcollation", "typdefaultbin", "typdefault",
			"typacl",
		},
		DDL: "oid BIGINT NOT NULL PRIMARY KEY, " +
			"typname VARCHAR , " +
			"typnamespace BIGINT , " +
			"typowner BIGINT , " +
			"typlen SMALLINT , " +
			"typbyval BOOLEAN , " +
			"typtype CHAR , " +
			"typcategory CHAR , " +
			"typispreferred BOOLEAN , " +
			"typisdefined BOOLEAN , " +
			"typdelim CHAR , " +
			"typrelid BIGINT , " +
			"typsubscript BIGINT , " +
			"typelem BIGINT , " +
			"typarray BIGINT , " +
			"typinput BIGINT , " +
			"typoutput BIGINT , " +
			"typreceive BIGINT , " +
			"typsend BIGINT , " +
			"typmodin BIGINT , " +
			"typmodout BIGINT , " +
			"typanalyze BIGINT , " +
			"typalign CHAR , " +
			"typstorage CHAR , " +
			"typnotnull BOOLEAN , " +
			"typbasetype BIGINT , " +
			"typtypmod INTEGER , " +
			"typndims INTEGER , " +
			"typcollation BIGINT , " +
			"typdefaultbin VARCHAR, " +
			"typdefault TEXT, " +
			"typacl TEXT",
	},

	PGProc: InternalTable{
		Schema: "__sys__",
		Name:   "pg_proc",
		KeyColumns: []string{
			"oid",
		},
		ValueColumns: []string{
			"proname",
			"pronamespace",
			"proowner",
			"prolang",
			"procost",
			"prorows",
			"provariadic",
			"prosupport",
			"prokind",
			"prosecdef",
			"proleakproof",
			"proisstrict",
			"proretset",
			"provolatile",
			"proparallel",
			"pronargs",
			"pronargdefaults",
			"prorettype",
			"proargtypes",
			"proallargtypes",
			"proargmodes",
			"proargnames",
			"proargdefaults",
			"protrftypes",
			"prosrc",
			"probin",
			"prosqlbody",
			"proconfig",
			"proacl",
		},
		DDL: "oid BIGINT NOT NULL PRIMARY KEY," +
			"proname VARCHAR ," +
			"pronamespace BIGINT ," +
			"proowner BIGINT ," +
			"prolang BIGINT ," +
			"procost FLOAT ," +
			"prorows FLOAT ," +
			"provariadic BIGINT ," +
			"prosupport BIGINT ," +
			"prokind CHAR ," +
			"prosecdef BOOLEAN ," +
			"proleakproof BOOLEAN ," +
			"proisstrict BOOLEAN ," +
			"proretset BOOLEAN ," +
			"provolatile CHAR ," +
			"proparallel CHAR ," +
			"pronargs SMALLINT ," +
			"pronargdefaults SMALLINT ," +
			"prorettype BIGINT ," +
			"proargtypes VARCHAR ," +
			"proallargtypes VARCHAR," +
			"proargmodes VARCHAR," +
			"proargnames VARCHAR," +
			"proargdefaults TEXT," +
			"protrftypes VARCHAR," +
			"prosrc TEXT ," +
			"probin TEXT," +
			"prosqlbody TEXT," +
			"proconfig VARCHAR," +
			"proacl VARCHAR",
	},

	PGClass: InternalTable{
		Schema: "__sys__",
		Name:   "pg_class",
		KeyColumns: []string{
			"oid",
		},
		ValueColumns: []string{
			"relname",
			"relnamespace",
			"reltype",
			"reloftype",
			"relowner",
			"relam",
			"relfilenode",
			"reltablespace",
			"relpages",
			"reltuples",
			"relallvisible",
			"reltoastrelid",
			"relhasindex",
			"relisshared",
			"relpersistence",
			"relkind",
			"relnatts",
			"relchecks",
			"relhasrules",
			"relhastriggers",
			"relhassubclass",
			"relrowsecurity",
			"relforcerowsecurity",
			"relispopulated",
			"relreplident",
			"relispartition",
			"relrewrite",
			"relfrozenxid",
			"relminmxid",
			"relacl",
			"reloptions",
			"relpartbound",
		},
		DDL: "oid BIGINT NOT NULL PRIMARY KEY," +
			"relname VARCHAR ," +
			"relnamespace BIGINT ," +
			"reltype BIGINT ," +
			"reloftype BIGINT ," +
			"relowner BIGINT ," +
			"relam BIGINT ," +
			"relfilenode BIGINT ," +
			"reltablespace BIGINT ," +
			"relpages INTEGER ," +
			"reltuples FLOAT ," +
			"relallvisible INTEGER ," +
			"reltoastrelid BIGINT ," +
			"relhasindex BOOLEAN ," +
			"relisshared BOOLEAN ," +
			"relpersistence CHAR ," +
			"relkind CHAR ," +
			"relnatts SMALLINT ," +
			"relchecks SMALLINT ," +
			"relhasrules BOOLEAN ," +
			"relhastriggers BOOLEAN ," +
			"relhassubclass BOOLEAN ," +
			"relrowsecurity BOOLEAN ," +
			"relforcerowsecurity BOOLEAN ," +
			"relispopulated BOOLEAN ," +
			"relreplident CHAR ," +
			"relispartition BOOLEAN ," +
			"relrewrite BIGINT ," +
			"relfrozenxid BIGINT ," +
			"relminmxid BIGINT ," +
			"relacl TEXT," +
			"reloptions TEXT," +
			"relpartbound TEXT",
	},

	PGNamespace: InternalTable{
		Schema: "__sys__",
		Name:   "pg_namespace",
		KeyColumns: []string{
			"oid",
		},
		ValueColumns: []string{
			"nspname",
			"nspowner",
			"nspacl",
		},
		DDL: "oid BIGINT NOT NULL PRIMARY KEY," +
			"nspname VARCHAR NOT NULL," +
			"nspowner BIGINT NOT NULL," +
			"nspacl TEXT",
		InitialData: InitialDataTables.PGNamespace,
	},

	PGMatViews: InternalTable{
		Schema: "__sys__",
		Name:   "pg_matviews",
		KeyColumns: []string{
			"schemaname",
			"matviewname",
		},
		ValueColumns: []string{
			"matviewowner",
			"tablespace",
			"hasindexes",
			"ispopulated",
			"definition",
		},
		DDL: "schemaname VARCHAR NOT NULL, " +
			"matviewname VARCHAR NOT NULL, " +
			"matviewowner VARCHAR, " +
			"tablespace VARCHAR, " +
			"hasindexes BOOLEAN, " +
			"ispopulated BOOLEAN, " +
			"definition TEXT",
	},
}

var InternalViews = []InternalView{
	{
		Schema: "__sys__",
		Name:   "pg_stat_user_tables",
		DDL: `SELECT
    t.table_schema || '.' || t.table_name AS relid, -- Create a unique ID for the table
    t.table_schema AS schemaname,                  -- Schema name
    t.table_name AS relname,                       -- Table name
    0 AS seq_scan,                                 -- Default to 0 (DuckDB doesn't track this)
    NULL AS last_seq_scan,                         -- Placeholder (DuckDB doesn't track this)
    0 AS seq_tup_read,                             -- Default to 0
    0 AS idx_scan,                                 -- Default to 0
    NULL AS last_idx_scan,                         -- Placeholder
    0 AS idx_tup_fetch,                            -- Default to 0
    0 AS n_tup_ins,                                -- Default to 0 (inserted tuples not tracked)
    0 AS n_tup_upd,                                -- Default to 0 (updated tuples not tracked)
    0 AS n_tup_del,                                -- Default to 0 (deleted tuples not tracked)
    0 AS n_tup_hot_upd,                            -- Default to 0 (HOT updates not tracked)
    0 AS n_tup_newpage_upd,                        -- Default to 0 (new page updates not tracked)
    0 AS n_live_tup,                               -- Default to 0 (live tuples not tracked)
    0 AS n_dead_tup,                               -- Default to 0 (dead tuples not tracked)
    0 AS n_mod_since_analyze,                      -- Default to 0
    0 AS n_ins_since_vacuum,                       -- Default to 0
    NULL AS last_vacuum,                           -- Placeholder
    NULL AS last_autovacuum,                       -- Placeholder
    NULL AS last_analyze,                          -- Placeholder
    NULL AS last_autoanalyze,                      -- Placeholder
    0 AS vacuum_count,                             -- Default to 0
    0 AS autovacuum_count,                         -- Default to 0
    0 AS analyze_count,                            -- Default to 0
    0 AS autoanalyze_count                         -- Default to 0
FROM
    information_schema.tables t
WHERE
    t.table_type = 'BASE TABLE'; -- Include only base tables (not views)`,
	},
	{
		Schema: "__sys__",
		Name:   "pg_index",
		DDL: `SELECT
    ROW_NUMBER() OVER () AS indexrelid,                -- Simulated unique ID for the index
    t.table_oid AS indrelid,                          -- OID of the table
    COUNT(k.column_name) AS indnatts,                 -- Number of columns included in the index
    COUNT(k.column_name) AS indnkeyatts,              -- Number of key columns in the index (same as indnatts here)
    CASE
        WHEN c.constraint_type = 'UNIQUE' THEN TRUE
        ELSE FALSE
    END AS indisunique,                               -- Indicates if the index is unique
    CASE
        WHEN c.constraint_type = 'PRIMARY KEY' THEN TRUE
        ELSE FALSE
    END AS indisprimary,                              -- Indicates if the index is a primary key
    ARRAY_AGG(k.ordinal_position ORDER BY k.ordinal_position) AS indkey,  -- Array of column positions
    ARRAY[]::BIGINT[] AS indcollation,                -- DuckDB does not support collation, set to default
    ARRAY[]::BIGINT[] AS indclass,                    -- DuckDB does not support index class, set to default
    ARRAY[]::INTEGER[] AS indoption,                  -- DuckDB does not support index options, set to default
    NULL AS indexprs,                                 -- DuckDB does not support expression indexes, set to NULL
    NULL AS indpred                                   -- DuckDB does not support partial indexes, set to NULL
FROM
    information_schema.key_column_usage k
JOIN
    information_schema.table_constraints c
    ON k.constraint_name = c.constraint_name
    AND k.table_name = c.table_name
JOIN
    duckdb_tables() t
    ON k.table_name = t.table_name
    AND k.table_schema = t.schema_name
WHERE
    c.constraint_type IN ('PRIMARY KEY', 'UNIQUE')    -- Only select primary key and unique constraints
GROUP BY
    t.table_oid, c.constraint_type, c.constraint_name
ORDER BY
    t.table_oid;`,
	},
}

var SystemViews = map[string]struct{}{
	"duckdb_columns":       {},
	"duckdb_constraints":   {},
	"duckdb_databases":     {},
	"duckdb_indexes":       {},
	"duckdb_schemas":       {},
	"duckdb_tables":        {},
	"duckdb_types":         {},
	"duckdb_views":         {},
	"pragma_database_list": {},
	"sqlite_master":        {},
	"sqlite_schema":        {},
	"sqlite_temp_master":   {},
	"sqlite_temp_schema":   {},
}