Queries

SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*application='PgHero'*/

WITH project_cte AS (
                SELECT id FROM project
                    WHERE is_active = $1
                        AND last_scheduler_access <= NOW() - INTERVAL $2
                    ORDER BY last_scheduler_access ASC
                    LIMIT $3
                    FOR UPDATE SKIP LOCKED
            )
            UPDATE project 
            SET last_scheduler_access = NOW()
            WHERE id = (SELECT id FROM project_cte)
            RETURNING 
                id, 
                EXISTS (SELECT $4 FROM compute_spawn
                    WHERE project_id = project.id
                        AND created_at >= NOW() - INTERVAL $5
                        AND state = $6
                ) AS compute_spawn_requested

WITH cs_cte AS (
                SELECT id FROM compute_spawn
                WHERE project_id IS NOT NULL
                    AND state = $1
                    AND ((termination_queued_at IS NULL AND updated_at < NOW() - INTERVAL $2)
                        OR termination_queued_at < NOW() - INTERVAL $3)
                ORDER BY termination_queued_at ASC NULLS FIRST
                LIMIT $4
                FOR UPDATE SKIP LOCKED
            )
            UPDATE compute_spawn
            SET termination_queued_at = NOW(),
                termination_enqueue_count = termination_enqueue_count + $5
            WHERE id = (SELECT id FROM cs_cte)
            RETURNING id, project_id AS "project_id!", token_hash, compute_env_id,
                compute_type, instance_id, state, termination_enqueue_count

SELECT sn.nspname AS schema, s.relname AS sequence, n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_depend dep ON dep.refclassid = $1::regclass AND dep.refobjid = a.attrelid AND dep.refobjsubid = a.attnum AND dep.classid = $2::regclass AND dep.objsubid = $3 AND dep.deptype IN ($4, $5) AND dep.objid IN (SELECT oid FROM pg_class WHERE relkind = $6) LEFT JOIN pg_catalog.pg_class s ON s.oid = dep.objid LEFT JOIN pg_catalog.pg_namespace sn ON sn.oid = s.relnamespace LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum AND s.relkind IS NULL WHERE NOT a.attisdropped AND a.attnum > $7 AND (pg_get_expr(d.adbin, d.adrelid) LIKE $8 OR s.relname IS NOT NULL) AND c.relpersistence <> $9 /*application='PgHero'*/

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "total_time" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

SELECT
            cj.id cron_job_id,
            cj.name cron_job_name,
            cj.project_id,
            p.code project_code,
            jd.name job_definition_name,
            jdv.version job_definition_version,
            cj.cron_expression,
            cj.next_trigger_at,
            jq.name AS job_queue_name,
            cj.pending_job_expiry_seconds
        FROM cron_job cj
        INNER JOIN project p ON p.id = cj.project_id
        INNER JOIN job_definition_version jdv ON jdv.id = cj.job_definition_version_id
        INNER JOIN job_definition jd ON jd.id = jdv.job_definition_id
        INNER JOIN job_queue jq ON jq.id = cj.job_queue_id
        WHERE cj.next_trigger_at <= NOW()
        AND cj.is_active = $1
        ORDER BY cj.next_trigger_at ASC
        LIMIT $2
        FOR UPDATE SKIP LOCKED

SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6, $7) AND n.nspname !~ $8 AND c.relkind IN ($9, $10, $11) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*application='PgHero'*/

WITH job_run_ids AS (
            SELECT id FROM job_run
            WHERE state = $3
                AND ((last_ping_at IS NOT NULL AND last_ping_at < NOW() - INTERVAL $6)
                    OR (last_ping_at IS NULL AND started_on < NOW() - INTERVAL $7))
            ORDER BY id ASC
            LIMIT $8
            FOR UPDATE SKIP LOCKED
        ),
        job_run_update AS (
            UPDATE job_run
            SET state = $1,
                log = ARRAY_APPEND(COALESCE(log, $9), $2),
                completed_on = last_ping_at
            WHERE id IN (SELECT id FROM job_run_ids)
            RETURNING id
        )
        UPDATE job j
        SET state = $4
        FROM job_run jr
        WHERE
            jr.job_id = j.id
            AND jr.id = ANY(SELECT id FROM job_run_update)
            AND j.state != ALL($5)

WITH cs_cte AS (
            SELECT cs.id 
            FROM compute_spawn cs
            INNER JOIN job_queue jq ON jq.id = cs.job_queue_id
            WHERE cs.state = $3
                AND cs.compute_type = $4
                AND cs.state_updated_at IS NOT NULL
                AND cs.state_updated_at < NOW() - (jq.max_idle_second * INTERVAL $5)
                AND NOT EXISTS (
                    SELECT $6 FROM job_run jr
                    WHERE jr.compute_spawn_id = cs.id
                    AND jr.state IN ($7, $8, $9)
                )
            ORDER BY cs.updated_at DESC
            LIMIT $10
            FOR UPDATE SKIP LOCKED
        )
        UPDATE compute_spawn
        SET state = $1,
            state_updated_at = NOW(),
            log = ARRAY_APPEND(COALESCE(log, $11), $2)
        WHERE id = ANY(SELECT id FROM cs_cte)
        RETURNING id

UPDATE compute_spawn SET last_ping_at = NOW()
                WHERE id = ANY($1) AND state = ANY($2)

WITH cs_cte AS (
            SELECT id FROM compute_spawn
            WHERE state = $3
                AND compute_type = $4
                AND (updated_at IS NULL 
                    OR updated_at < NOW() - INTERVAL $5)
            ORDER BY updated_at ASC
            LIMIT $6
            FOR UPDATE SKIP LOCKED
        )
        UPDATE compute_spawn
        SET state = $1,
            state_updated_at = NOW(),
            log = ARRAY_APPEND(COALESCE(log, $7), $2)
        WHERE id = ANY(SELECT id FROM cs_cte)
        RETURNING id

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_time, (total_plan_time + total_exec_time) / calls AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "average_time" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

SELECT pg_database_size(current_database()) /*application='PgHero'*/

WITH job_cte AS (
                SELECT id FROM job
                WHERE state IN ($2, $3)
                    AND enqueue_count >= $4
                ORDER BY id ASC
                LIMIT $5
                FOR UPDATE SKIP LOCKED
            )
            UPDATE job
            SET state = $6,
                system_note = $7,
                log = ARRAY_APPEND(COALESCE(log, $8), $1),
                internal_log = ARRAY_APPEND(COALESCE(internal_log, $9), $1)
            WHERE id IN (SELECT id FROM job_cte)

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "calls" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

WITH cte AS (
                    SELECT
                        j.id AS job_id
                    FROM compute_spawn cs
                    INNER JOIN job j ON j.job_queue_id = cs.job_queue_id
                    WHERE cs.id = $1
                        AND CASE 
                            WHEN NOT $2 THEN cs.state IN ($4, $5) 
                            ELSE cs.state IN ($6, $7, $8) 
                        END
                        AND j.project_id IS NOT NULL
                        AND j.state IN ($9, $10)
                        AND COALESCE(j.schedule_at, j.created_at) <= NOW()
                        AND ($2 OR (
                                (j.cpu_quota IS NOT NULL
                                    AND j.memory_mb_quota IS NOT NULL
                                    AND j.cpu_quota <= cs.available_cpu_quota
                                    AND j.memory_mb_quota <= cs.available_memory_mb_quota)
                                OR
                                (j.cpu_quota IS NULL
                                    AND j.memory_mb_quota IS NULL
                                    AND COALESCE(cs.used_cpu_quota, $11) = $12
                                    AND COALESCE(cs.used_memory_mb_quota, $13) = $14)
                            )
                        )
                    ORDER BY COALESCE(j.schedule_at, j.created_at) ASC, j.id ASC
                    FOR UPDATE OF j SKIP LOCKED
                    LIMIT $15
                )
                UPDATE job
                SET enqueue_count = enqueue_count + $16, state = $3
                FROM cte WHERE job.id = cte.job_id 
                RETURNING
                    job.id,
                    job.project_id,
                    job.job_queue_id

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40)
 /*application='PgHero'*/

SELECT n.nspname AS schema, c.relname AS table, $1 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = $2 AND ($3 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < $4 ORDER BY 3, 1, 2 /*application='PgHero'*/

INSERT INTO "country" ("code", "continent_code", "name", "iso3", "iso_numeric", "full_name") VALUES
  ($1, $2, $3, $4, $5, $6),
  ($7, $8, $9, $10, $11, $12),
  ($13, $14, $15, $16, $17, $18),
  ($19, $20, $21, $22, $23, $24),
  ($25, $26, $27, $28, $29, $30),
  ($31, $32, $33, $34, $35, $36),
  ($37, $38, $39, $40, $41, $42),
  ($43, $44, $45, $46, $47, $48),
  ($49, $50, $51, $52, $53, $54),
  ($55, $56, $57, $58, $59, $60),
  ($61, $62, $63, $64, $65, $66),
  ($67, $68, $69, $70, $71, $72),
  ($73, $74, $75, $76, $77, $78),
  ($79, $80, $81, $82, $83, $84),
  ($85, $86, $87, $88, $89, $90),
  ($91, $92, $93, $94, $95, $96),
  ($97, $98, $99, $100, $101, $102),
  ($103, $104, $105, $106, $107, $108),
  ($109, $110, $111, $112, $113, $114),
  ($115, $116, $117, $118, $119, $120),
  ($121, $122, $123, $124, $125, $126),
  ($127, $128, $129, $130, $131, $132),
  ($133, $134, $135, $136, $137, $138),
  ($139, $140, $141, $142, $143, $144),
  ($145, $146, $147, $148, $149, $150),
  ($151, $152, $153, $154, $155, $156),
  ($157, $158, $159, $160, $161, $162),
  ($163, $164, $165, $166, $167, $168),
  ($169, $170, $171, $172, $173, $174),
  ($175, $176, $177, $178, $179, $180),
  ($181, $182, $183, $184, $185, $186),
  ($187, $188, $189, $190, $191, $192),
  ($193, $194, $195, $196, $197, $198),
  ($199, $200, $201, $202, $203, $204),
  ($205, $206, $207, $208, $209, $210),
  ($211, $212, $213, $214, $215, $216),
  ($217, $218, $219, $220, $221, $222),
  ($223, $224, $225, $226, $227, $228),
  ($229, $230, $231, $232, $233, $234),
  ($235, $236, $237, $238, $239, $240),
  ($241, $242, $243, $244, $245, $246),
  ($247, $248, $249, $250, $251, $252),
  ($253, $254, $255, $256, $257, $258),
  ($259, $260, $261, $262, $263, $264),
  ($265, $266, $267, $268, $269, $270),
  ($271, $272, $273, $274, $275, $276),
  ($277, $278, $279, $280, $281, $282),
  ($283, $284, $285, $286, $287, $288),
  ($289, $290, $291, $292, $293, $294),
  ($295, $296, $297, $298, $299, $300),
  ($301, $302, $303, $304, $305, $306),
  ($307, $308, $309, $310, $311, $312),
  ($313, $314, $315, $316, $317, $318),
  ($319, $320, $321, $322, $323, $324),
  ($325, $326, $327, $328, $329, $330),
  ($331, $332, $333, $334, $335, $336),
  ($337, $338, $339, $340, $341, $342),
  ($343, $344, $345, $346, $347, $348),
  ($349, $350, $351, $352, $353, $354),
  ($355, $356, $357, $358, $359, $360),
  ($361, $362, $363, $364, $365, $366),
  ($367, $368, $369, $370, $371, $372),
  ($373, $374, $375, $376, $377, $378),
  ($379, $380, $381, $382, $383, $384),
  ($385, $386, $387, $388, $389, $390),
  ($391, $392, $393, $394, $395, $396),
  ($397, $398, $399, $400, $401, $402),
  ($403, $404, $405, $406, $407, $408),
  ($409, $410, $411, $412, $413, $414),
  ($415, $416, $417, $418, $419, $420),
  ($421, $422, $423, $424, $425, $426),
  ($427, $428, $429, $430, $431, $432),
  ($433, $434, $435, $436, $437, $438),
  ($439, $440, $441, $442, $443, $444),
  ($445, $446, $447, $448, $449, $450),
  ($451, $452, $453, $454, $455, $456),
  ($457, $458, $459, $460, $461, $462),
  ($463, $464, $465, $466, $467, $468),
  ($469, $470, $471, $472, $473, $474),
  ($475, $476, $477, $478, $479, $480),
  ($481, $482, $483, $484, $485, $486),
  ($487, $488, $489, $490, $491, $492),
  ($493, $494, $495, $496, $497, $498),
  ($499, $500, $501, $502, $503, $504),
  ($505, $506, $507, $508, $509, $510),
  ($511, $512, $513, $514, $515, $516),
  ($517, $518, $519, $520, $521, $522),
  ($523, $524, $525, $526, $527, $528),
  ($529, $530, $531, $532, $533, $534),
  ($535, $536, $537, $538, $539, $540),
  ($541, $542, $543, $544, $545, $546),
  ($547, $548, $549, $550, $551, $552),
  ($553, $554, $555, $556, $557, $558),
  ($559, $560, $561, $562, $563, $564),
  ($565, $566, $567, $568, $569, $570),
  ($571, $572, $573, $574, $575, $576),
  ($577, $578, $579, $580, $581, $582),
  ($583, $584, $585, $586, $587, $588),
  ($589, $590, $591, $592, $593, $594),
  ($595, $596, $597, $598, $599, $600),
  ($601, $602, $603, $604, $605, $606),
  ($607, $608, $609, $610, $611, $612),
  ($613, $614, $615, $616, $617, $618),
  ($619, $620, $621, $622, $623, $624),
  ($625, $626, $627, $628, $629, $630),
  ($631, $632, $633, $634, $635, $636),
  ($637, $638, $639, $640, $641, $642),
  ($643, $644, $645, $646, $647, $648),
  ($649, $650, $651, $652, $653, $654),
  ($655, $656, $657, $658, $659, $660),
  ($661, $662, $663, $664, $665, $666),
  ($667, $668, $669, $670, $671, $672),
  ($673, $674, $675, $676, $677, $678),
  ($679, $680, $681, $682, $683, $684),
  ($685, $686, $687, $688, $689, $690),
  ($691, $692, $693, $694, $695, $696),
  ($697, $698, $699, $700, $701, $702),
  ($703, $704, $705, $706, $707, $708),
  ($709, $710, $711, $712, $713, $714),
  ($715, $716, $717, $718, $719, $720),
  ($721, $722, $723, $724, $725, $726),
  ($727, $728, $729, $730, $731, $732),
  ($733, $734, $735, $736, $737, $738),
  ($739, $740, $741, $742, $743, $744),
  ($745, $746, $747, $748, $749, $750),
  ($751, $752, $753, $754, $755, $756),
  ($757, $758, $759, $760, $761, $762),
  ($763, $764, $765, $766, $767, $768),
  ($769, $770, $771, $772, $773, $774),
  ($775, $776, $777, $778, $779, $780),
  ($781, $782, $783, $784, $785, $786),
  ($787, $788, $789, $790, $791, $792),
  ($793, $794, $795, $796, $797, $798),
  ($799, $800, $801, $802, $803, $804),
  ($805, $806, $807, $808, $809, $810),
  ($811, $812, $813, $814, $815, $816),
  ($817, $818, $819, $820, $821, $822),
  ($823, $824, $825, $826, $827, $828),
  ($829, $830, $831, $832, $833, $834),
  ($835, $836, $837, $838, $839, $840),
  ($841, $842, $843, $844, $845, $846),
  ($847, $848, $849, $850, $851, $852),
  ($853, $854, $855, $856, $857, $858),
  ($859, $860, $861, $862, $863, $864),
  ($865, $866, $867, $868, $869, $870),
  ($871, $872, $873, $874, $875, $876),
  ($877, $878, $879, $880, $881, $882),
  ($883, $884, $885, $886, $887, $888),
  ($889, $890, $891, $892, $893, $894),
  ($895, $896, $897, $898, $899, $900),
  ($901, $902, $903, $904, $905, $906),
  ($907, $908, $909, $910, $911, $912),
  ($913, $914, $915, $916, $917, $918),
  ($919, $920, $921, $922, $923, $924),
  ($925, $926, $927, $928, $929, $930),
  ($931, $932, $933, $934, $935, $936),
  ($937, $938, $939, $940, $941, $942),
  ($943, $944, $945, $946, $947, $948),
  ($949, $950, $951, $952, $953, $954),
  ($955, $956, $957, $958, $959, $960),
  ($961, $962, $963, $964, $965, $966),
  ($967, $968, $969, $970, $971, $972),
  ($973, $974, $975, $976, $977, $978),
  ($979, $980, $981, $982, $983, $984),
  ($985, $986, $987, $988, $989, $990),
  ($991, $992, $993, $994, $995, $996),
  ($997, $998, $999, $1000, $1001, $1002),
  ($1003, $1004, $1005, $1006, $1007, $1008),
  ($1009, $1010, $1011, $1012, $1013, $1014),
  ($1015, $1016, $1017, $1018, $1019, $1020),
  ($1021, $1022, $1023, $1024, $1025, $1026),
  ($1027, $1028, $1029, $1030, $1031, $1032),
  ($1033, $1034, $1035, $1036, $1037, $1038),
  ($1039, $1040, $1041, $1042, $1043, $1044),
  ($1045, $1046, $1047, $1048, $1049, $1050),
  ($1051, $1052, $1053, $1054, $1055, $1056),
  ($1057, $1058, $1059, $1060, $1061, $1062),
  ($1063, $1064, $1065, $1066, $1067, $1068),
  ($1069, $1070, $1071, $1072, $1073, $1074),
  ($1075, $1076, $1077, $1078, $1079, $1080),
  ($1081, $1082, $1083, $1084, $1085, $1086),
  ($1087, $1088, $1089, $1090, $1091, $1092),
  ($1093, $1094, $1095, $1096, $1097, $1098),
  ($1099, $1100, $1101, $1102, $1103, $1104),
  ($1105, $1106, $1107, $1108, $1109, $1110),
  ($1111, $1112, $1113, $1114, $1115, $1116),
  ($1117, $1118, $1119, $1120, $1121, $1122),
  ($1123, $1124, $1125, $1126, $1127, $1128),
  ($1129, $1130, $1131, $1132, $1133, $1134),
  ($1135, $1136, $1137, $1138, $1139, $1140),
  ($1141, $1142, $1143, $1144, $1145, $1146),
  ($1147, $1148, $1149, $1150, $1151, $1152),
  ($1153, $1154, $1155, $1156, $1157, $1158),
  ($1159, $1160, $1161, $1162, $1163, $1164),
  ($1165, $1166, $1167, $1168, $1169, $1170),
  ($1171, $1172, $1173, $1174, $1175, $1176),
  ($1177, $1178, $1179, $1180, $1181, $1182),
  ($1183, $1184, $1185, $1186, $1187, $1188),
  ($1189, $1190, $1191, $1192, $1193, $1194),
  ($1195, $1196, $1197, $1198, $1199, $1200),
  ($1201, $1202, $1203, $1204, $1205, $1206),
  ($1207, $1208, $1209, $1210, $1211, $1212),
  ($1213, $1214, $1215, $1216, $1217, $1218),
  ($1219, $1220, $1221, $1222, $1223, $1224),
  ($1225, $1226, $1227, $1228, $1229, $1230),
  ($1231, $1232, $1233, $1234, $1235, $1236),
  ($1237, $1238, $1239, $1240, $1241, $1242),
  ($1243, $1244, $1245, $1246, $1247, $1248),
  ($1249, $1250, $1251, $1252, $1253, $1254),
  ($1255, $1256, $1257, $1258, $1259, $1260),
  ($1261, $1262, $1263, $1264, $1265, $1266),
  ($1267, $1268, $1269, $1270, $1271, $1272),
  ($1273, $1274, $1275, $1276, $1277, $1278),
  ($1279, $1280, $1281, $1282, $1283, $1284),
  ($1285, $1286, $1287, $1288, $1289, $1290),
  ($1291, $1292, $1293, $1294, $1295, $1296),
  ($1297, $1298, $1299, $1300, $1301, $1302),
  ($1303, $1304, $1305, $1306, $1307, $1308),
  ($1309, $1310, $1311, $1312, $1313, $1314),
  ($1315, $1316, $1317, $1318, $1319, $1320),
  ($1321, $1322, $1323, $1324, $1325, $1326),
  ($1327, $1328, $1329, $1330, $1331, $1332),
  ($1333, $1334, $1335, $1336, $1337, $1338),
  ($1339, $1340, $1341, $1342, $1343, $1344),
  ($1345, $1346, $1347, $1348, $1349, $1350),
  ($1351, $1352, $1353, $1354, $1355, $1356),
  ($1357, $1358, $1359, $1360, $1361, $1362),
  ($1363, $1364, $1365, $1366, $1367, $1368),
  ($1369, $1370, $1371, $1372, $1373, $1374),
  ($1375, $1376, $1377, $1378, $1379, $1380),
  ($1381, $1382, $1383, $1384, $1385, $1386),
  ($1387, $1388, $1389, $1390, $1391, $1392),
  ($1393, $1394, $1395, $1396, $1397, $1398),
  ($1399, $1400, $1401, $1402, $1403, $1404),
  ($1405, $1406, $1407, $1408, $1409, $1410),
  ($1411, $1412, $1413, $1414, $1415, $1416),
  ($1417, $1418, $1419, $1420, $1421, $1422),
  ($1423, $1424, $1425, $1426, $1427, $1428),
  ($1429, $1430, $1431, $1432, $1433, $1434),
  ($1435, $14

SELECT schemaname AS schema, relname AS table, indexrelname AS index, pg_relation_size(i.indexrelid) AS size_bytes, idx_scan as index_scans FROM pg_stat_user_indexes ui INNER JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan <= $1 AND pg_relation_size(i.indexrelid) >= $2 ORDER BY pg_relation_size(i.indexrelid) DESC, relname ASC /*application='PgHero'*/

CREATE EXTENSION IF NOT EXISTS pg_stat_statements

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) ORDER BY 1, 2 /*application='PgHero'*/

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typelem IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48)
 /*application='PgHero'*/

WITH target_queues AS (
            SELECT id, max_concurrency, max_compute_spawn, priority, created_at
            FROM job_queue
            WHERE project_id = $1
            AND disable_auto_compute_spawn = $2
        ),
        job_stats AS (
            SELECT j.job_queue_id, COUNT(*)::INTEGER AS active_job
            FROM job j
            JOIN target_queues tq ON j.job_queue_id = tq.id
            WHERE j.state IN ($3, $4, $5)
            GROUP BY j.job_queue_id
        ),
        spawn_stats AS (
            SELECT cs.job_queue_id, COUNT(*)::INTEGER AS active_compute_spawn
            FROM compute_spawn cs
            JOIN target_queues tq ON cs.job_queue_id = tq.id
            WHERE cs.state IN ($6, $7, $8, $9, $10)
            GROUP BY cs.job_queue_id
        )
        SELECT
            q.id,
            q.max_concurrency,
            q.max_compute_spawn,
            COALESCE(js.active_job, $11) AS active_job,
            COALESCE(ss.active_compute_spawn, $12) AS active_compute_spawn,
            (next_job.id IS NOT NULL) AS job_exists
        FROM target_queues q
        LEFT JOIN job_stats js ON q.id = js.job_queue_id
        LEFT JOIN spawn_stats ss ON q.id = ss.job_queue_id
        LEFT JOIN LATERAL (
            SELECT j.id FROM job j
            WHERE j.job_queue_id = q.id
                AND COALESCE(j.schedule_at, j.created_at) <= NOW()
                AND j.state IN ($13, $14)
            ORDER BY j.job_queue_id, COALESCE(j.schedule_at, j.created_at), j.id
            LIMIT $15
        ) next_job ON $16
        ORDER BY q.priority ASC, q.id ASC

UPDATE compute_spawn
        SET
            state = $2,
            state_updated_at = CURRENT_TIMESTAMP,
            log = ARRAY_CAT(COALESCE(log, $9), $3),
            internal_log = ARRAY_CAT(COALESCE(internal_log, $10), $4),
            used_cpu_quota = COALESCE($5, used_cpu_quota),
            available_cpu_quota = COALESCE($6, available_cpu_quota),
            used_memory_mb_quota = COALESCE($7, used_memory_mb_quota),
            available_memory_mb_quota = COALESCE($8, available_memory_mb_quota)
        WHERE
            id = $1

UPDATE job
        SET
            state=COALESCE($2, state),
            log=ARRAY_CAT(COALESCE(log, $9), $3),
            internal_log=ARRAY_CAT(COALESCE(internal_log, $10), $4),
            submitted_at=COALESCE($5, submitted_at),
            system_note=$6,
            spawned_new_compute=COALESCE($7, spawned_new_compute),
            enqueue_count = COALESCE($8, enqueue_count),
            pending_expires_at = CASE WHEN $2 = $11 THEN $12 ELSE pending_expires_at END
        WHERE
            id=$1

INSERT INTO "location" (
        "id",
        "country_code",
        "name",
        "slug"
    )
    VALUES (
        $1, 
        $2,
        $3,
        $4
    )

INSERT INTO "staging_logs" (
                id,
                object_id,
                purpose,
                text_data,
                text_array,
                json_data,
                json_array
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7)

-- trigger
SELECT trigger_updated_at($1)

SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, wait_event IS NOT NULL AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() AND NOW() - COALESCE(query_start, xact_start) > interval $4 AND query <> $5 ORDER BY COALESCE(query_start, xact_start) DESC /*application='PgHero'*/

SELECT schemaname AS schema, sequencename AS sequence, COALESCE(last_value, start_value) AS last_value FROM pg_sequences /*application='PgHero'*/

SELECT
                        COUNT(id) AS count
                    FROM
                        project
                    WHERE
                        id = $1
                    AND
                        user_id = $2

INSERT INTO
            job_run (
                id,
                project_id,
                job_id,
                compute_spawn_id,
                state,
                log,
                internal_log,
                log_job_id,
                log_compute_spawn_id,
                log_job_name
            )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $3, $4, $8)
        RETURNING id

CREATE TABLE IF NOT EXISTS _sqlx_migrations (
    version BIGINT PRIMARY KEY,
    description TEXT NOT NULL,
    installed_on TIMESTAMPTZ NOT NULL DEFAULT now(),
    success BOOLEAN NOT NULL,
    checksum BYTEA NOT NULL,
    execution_time BIGINT NOT NULL
)

CREATE TABLE "action_log" (
    id UUID PRIMARY KEY,
    project_id UUID NULL,
    user_id UUID NULL,
    db_table_name VARCHAR(255) NULL,
    object_id VARCHAR(255) NULL,
    object_name VARCHAR(255) NULL,
    action_name VARCHAR(255) NULL,
    action_type VARCHAR(255),
    action_time TIMESTAMPTZ NOT NULL,
    "status" BOOLEAN NOT NULL DEFAULT FALSE,
    "description" TEXT NULL,
    log TEXT NULL,
    is_private BOOLEAN NOT NULL DEFAULT FALSE
)

SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AND (c.relpersistence <> $2 OR NOT pg_is_in_recovery()) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $3 AND n.nspname NOT IN ($4, $5) /*application='PgHero'*/

SELECT state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1 /*application='PgHero'*/

UPDATE job_run SET last_ping_at=NOW() WHERE id = ANY($1)

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typtype IN ($1, $2, $3)
 /*application='PgHero'*/

SELECT
                (p.resource_limit->>$2)::INTEGER AS max_allowed_resource,
                (
                    SELECT COUNT(*) FROM compute_spawn
                    WHERE project_id = $1
                        AND state IN ($3, $4, $5, $6, $7)
                )::INTEGER AS total_resource_count
            FROM project p WHERE p.id=$1

INSERT INTO job (
                    id,
                    project_id,
                    job_definition_version_id,
                    job_queue_id,
                    job_queue_priority,
                    disable_auto_compute_spawn,
                    "name",
                    container_cmd,
                    container_cmd_parameter_json,
                    container_env_json,
                    cpu_quota,
                    memory_mb_quota,
                    state,
                    log_project_code,
                    log_job_definition_name,
                    log_job_definition_version,
                    log_job_queue_name,
                    schedule_at,
                    cron_job_id,
                    pending_expires_at,
                    log
                ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21)

SELECT
                (p.resource_limit->>$2)::INTEGER AS max_allowed_resource,
                (
                    SELECT COUNT(*) FROM job
                    WHERE project_id = $1
                        AND state IN ($3, $4, $5)
                )::INTEGER AS total_resource_count
            FROM project p WHERE p.id = $1

CREATE TABLE "staging_logs" (
    id UUID PRIMARY KEY,
    object_id VARCHAR(255) NOT NULL,
    purpose VARCHAR(255) NULL,
    text_data TEXT NULL,
    text_array TEXT[] NULL,
    json_data JSONB NULL,
    json_array JSONB[] NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

UPDATE job_queue
        SET
            description = COALESCE($1, description),
            priority = COALESCE($2, priority),
            max_concurrency = COALESCE($3, max_concurrency),
            max_idle_second = COALESCE($4, max_idle_second),
            is_active = COALESCE($5, is_active),
            max_compute_spawn = COALESCE($8, max_compute_spawn),
            ssh_public_key = COALESCE($9, ssh_public_key)
        WHERE
            id = $6 AND project_id = $7
        RETURNING name

SELECT
            (SELECT COUNT(*) FROM job
                WHERE project_id = $1
                AND state IN ($3,$4,$5,$6)) AS queued,
            (SELECT COUNT(*) FROM job
                WHERE project_id = $1 AND state = $7) AS running,
            (SELECT COUNT(*) FROM job
                WHERE project_id = $1 AND state = $8
                AND updated_at >= COALESCE($2::TIMESTAMPTZ, NOW() - INTERVAL $9)) AS completed,
            (SELECT COUNT(*) FROM job
                WHERE project_id = $1 AND state IN ($10,$11)
                AND updated_at >= COALESCE($2::TIMESTAMPTZ, NOW() - INTERVAL $12)) AS failed

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) ORDER BY 1, 2, 3 /*application='PgHero'*/

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"

WITH batch AS (
                SELECT id FROM compute_spawn
                WHERE state = ANY($1)
                    AND COALESCE(last_ping_at, state_updated_at, created_at) < NOW() - INTERVAL $3
                LIMIT $4
                FOR UPDATE SKIP LOCKED
            )
            UPDATE compute_spawn
            SET state = $5,
                state_updated_at = NOW(),
                log=ARRAY_APPEND(COALESCE(log, $6), $2)
            WHERE id IN (SELECT id FROM batch)
            RETURNING id

WITH runnable_jobs_cte AS (
            SELECT id FROM job
            WHERE state = $4
                AND updated_at < NOW() - INTERVAL $5
            ORDER BY id ASC LIMIT $6
            FOR UPDATE SKIP LOCKED
        )
        UPDATE job
        SET state=$1,
            log=ARRAY_APPEND(COALESCE(log, $7), $2),
            system_note=$3,
            spawned_new_compute=$8
        WHERE id IN (SELECT id FROM runnable_jobs_cte)

SELECT t.oid, t.typname
FROM pg_type as t
WHERE t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
 /*application='PgHero'*/

WITH batch AS (
                SELECT id FROM compute_spawn
                WHERE state = $2 AND compute_type = $3
                    AND created_at < NOW() - INTERVAL $4
                LIMIT $5
                FOR UPDATE SKIP LOCKED
            )
            UPDATE compute_spawn
                SET state = $6,
                    state_updated_at = NOW(),
                    log=ARRAY_APPEND(COALESCE(log, $7), $1)
                WHERE id IN (SELECT id FROM batch)
                RETURNING id

SELECT schemaname AS schema, relname AS table, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, n_dead_tup AS dead_rows, n_live_tup AS live_rows FROM pg_stat_user_tables ORDER BY 1, 2 /*application='PgHero'*/

CREATE TABLE "compute_spawn" (
    id UUID PRIMARY KEY,
    project_id UUID NULL,
    group_id UUID NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    compute_env_id UUID NULL,
    job_queue_id UUID NULL,
    compute_type VARCHAR(255) NOT NULL,
    initial_job_id UUID NULL,
    instance_id VARCHAR(255) NULL,
    instance_created_at TIMESTAMPTZ NULL,
    instance_destroyed_at TIMESTAMPTZ NULL,
    "state" VARCHAR(255) NOT NULL,
    state_updated_at TIMESTAMPTZ NULL,
    last_ping_at TIMESTAMPTZ NULL,
    log TEXT[] NULL,
    internal_log TEXT[] NULL,
    termination_queued_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL,
    -- extra
    log_compute_env_name VARCHAR(255) NULL,
    log_job_queue_name VARCHAR(255) NULL,
    log_initial_job_name VARCHAR(255) NULL
    -- END extra
)

UPDATE job_run
        SET
            state = COALESCE($1, state),
            started_on = COALESCE($2, started_on),
            completed_on = COALESCE($3, completed_on),
            log = ARRAY_CAT(COALESCE(log, $8), $6),
            container_exit_code = COALESCE($7, container_exit_code),
            time_taken_in_seconds = CASE
                WHEN COALESCE($2, started_on) IS NOT NULL 
                    AND COALESCE($3, completed_on) IS NOT NULL
                THEN FLOOR(EXTRACT($9 FROM (COALESCE($3, completed_on) - COALESCE($2, started_on))))::INTEGER
                ELSE time_taken_in_seconds
            END
        WHERE
            id = $4
        AND
            compute_spawn_id = $5
        RETURNING job_id

WITH assigned_job_runs_cte AS (
            SELECT id FROM job_run
            WHERE state = $1
                AND created_at < NOW() - INTERVAL $8
            ORDER BY id ASC LIMIT $9
            FOR UPDATE SKIP LOCKED
        ),
        updated_job_runs_cte AS (
            UPDATE job_run
            SET state = $2,
                log = ARRAY_APPEND(COALESCE(log, $10), $3)
            WHERE id IN (SELECT id FROM assigned_job_runs_cte)
            RETURNING id
        )
        UPDATE job j
        SET state = $4,
            system_note = $5,
            log = ARRAY_APPEND(COALESCE(j.log, $11), $6)
        FROM job_run jr
        WHERE j.state = $7
            AND jr.job_id = j.id
            AND jr.id = ANY(SELECT id FROM updated_job_runs_cte)

UPDATE "user" SET last_login=NOW() WHERE id=$1

SELECT * FROM pg_stat_statements LIMIT $1 /*application='PgHero'*/

-- Add migration script here

-- TABLE location
CREATE TABLE "location" (
    id UUID PRIMARY KEY,
    country_code VARCHAR(2) NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3,$4,$5,$6,$7,$8,$9,$10) ORDER BY 1, 2 /*application='PgHero'*/

-- index
CREATE INDEX "idx_stagingLogs_objectIdPurpose"
    ON "staging_logs" (object_id, purpose)

-- Add migration script here

-- TABLE country
CREATE TABLE "country" (
  id SERIAL PRIMARY KEY,
  code VARCHAR(2) NOT NULL,
  -- fk_country_continentcode
  continent_code VARCHAR(2),
  -- unique_country_name
  "name" VARCHAR(255) NOT NULL,
  full_name VARCHAR(255) NULL,
  -- unique_country_isoalpha3
  iso3 VARCHAR(3) NOT NULL,
  -- unique_country_isonumeric
  iso_numeric INTEGER NOT NULL,
  flag_icon VARCHAR(255) NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NULL
)

WITH pending_jobs AS (
                    SELECT id FROM job
                    WHERE state IN ($2, $3)
                        AND pending_expires_at < NOW()
                    ORDER BY pending_expires_at ASC
                    LIMIT $4
                    FOR UPDATE SKIP LOCKED
                )
                UPDATE job
                SET
                    state = $5,
                    system_note = $6,
                    log = ARRAY_APPEND(COALESCE(log, $7), $1),
                    internal_log = ARRAY_APPEND(COALESCE(internal_log, $8), $1)
                WHERE id IN (SELECT id FROM pending_jobs)

SELECT pg_stat_activity.pid, datname AS database, usename AS user, application_name AS source, client_addr AS ip, state, ssl FROM pg_stat_activity LEFT JOIN pg_stat_ssl ON pg_stat_activity.pid = pg_stat_ssl.pid ORDER BY pg_stat_activity.pid /*application='PgHero'*/

-- Add migration script here

-- TABLE cloud_provider
CREATE TABLE "cloud_provider" (
    id UUID PRIMARY KEY,
    "name" VARCHAR(255) NOT NULL,
    code VARCHAR(255) NOT NULL,
    logo VARCHAR(500) NULL,
    content VARCHAR(5000) NULL,
    official_link_home TEXT NULL,
    official_link_pricing TEXT NULL,
    help_link_api_key TEXT NULL,
    help_link_rate_limit TEXT NULL,
    require_access_token BOOLEAN NOT NULL DEFAULT FALSE,
    require_access_key_id BOOLEAN NOT NULL DEFAULT FALSE,
    require_secret_access_key BOOLEAN NOT NULL DEFAULT FALSE,
    public_message VARCHAR(1024) NULL,
    private_message VARCHAR(1024) NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

-- Add migration script here

-- TABLE instance family
CREATE TABLE "instance_family" (
    id UUID PRIMARY KEY,
    cloud_provider_id UUID NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    -- identifier from cloud provider (system or provider generated)
    code VARCHAR(255) NULL,
    "description" VARCHAR(5000) NULL,
    billed_by VARCHAR(10) NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

-- Add migration script here

-- TABLE user
CREATE TABLE "user" (
    id UUID PRIMARY KEY,
    -- idx_email
    email VARCHAR(255) NOT NULL,
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    "password" VARCHAR(255) NULL,
    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    -- idx_date_joined
    date_joined TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    -- staff user can have limited access to super-admin dash
    is_staff BOOLEAN NOT NULL DEFAULT FALSE,
    -- superuser has full access i.e. can see payment history etc.
    is_superuser BOOLEAN NOT NULL DEFAULT FALSE,
    last_login TIMESTAMPTZ NULL,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

CREATE TABLE "job" (
    id UUID PRIMARY KEY,
    "name" VARCHAR(255) NOT NULL,
    -- fk_job_projectid
    project_id UUID NULL,
    -- fk_job_jobdefinitionversionid
    job_definition_version_id UUID NULL,
    -- fk_job_jobqueueid
    job_queue_id UUID NULL,
    job_queue_priority INTEGER NOT NULL DEFAULT 1,
    container_cmd TEXT[] NULL,
    container_cmd_parameter_json JSONB NULL,
    container_env_json JSONB NULL,
    "state" VARCHAR(16) NOT NULL,
    submitted_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    enqueue_count INTEGER NOT NULL DEFAULT 0,
    log TEXT[] NULL,
    internal_log TEXT[] NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL,
    -- extra
    log_project_code VARCHAR(255) NULL,
    log_job_definition_name VARCHAR(255) NULL,
    log_job_definition_version INTEGER NULL,
    log_job_queue_name VARCHAR(255) NULL
    -- END extra
)

-- Add migration script here

CREATE TABLE "instance_type" (
    id UUID PRIMARY KEY,
    cloud_provider_id UUID NOT NULL,
    instance_family_id UUID NULL,
    "code" VARCHAR(255) NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "description" VARCHAR(5000) NULL,
    help_link VARCHAR(1000) NULL,
    cpu_count INTEGER NOT NULL,
    cpu_type VARCHAR(255) NULL,
    memory_mb INTEGER NOT NULL,
    gpu_count INTEGER NULL,
    gpu_vram_gb INTEGER NULL,
    gpu_type VARCHAR(255) NULL,
    total_storage_gb INTEGER NULL,
    storage_count INTEGER NULL,
    storage_type VARCHAR(255) NULL,
    free_transfer_gb INTEGER NULL,
    networking_throughput_mbps INTEGER NULL,
    price_monthly FLOAT NULL,
    price_hourly FLOAT NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    public_message VARCHAR(1024) NULL,
    private_message VARCHAR(1024) NULL,
    api_response_body VARCHAR(8192) NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

WITH temp_compute_env AS (
            SELECT
                ce.name,
                cp.code AS "cloud_provider_code",
                it.code AS "instance_type_code",
                itl.log_location_name AS "location_name"
            FROM compute_env ce
            LEFT JOIN instance_type it ON ce.instance_type_id = it.id
            LEFT JOIN cloud_provider cp ON it.cloud_provider_id = cp.id
            LEFT JOIN instance_type_location itl ON ce.instance_type_location_id = itl.id
            WHERE ce.id = $6
        )
        INSERT INTO
            compute_spawn (
                id,
                name,
                project_id,
                group_id,
                token_hash,
                compute_env_id,
                job_queue_id,
                compute_type,
                initial_job_id,
                state,
                internal_log,
                log_compute_env_name,
                log_job_queue_name,
                log_initial_job_name,
                log_cloud_provider_code,
                log_instance_type_code,
                log_location_name
            )
        SELECT
            $1,
            $2,
            $3,
            $4,
            $5,
            $6,
            $7,
            $8,
            $9,
            $10,
            $11,
            (SELECT name FROM temp_compute_env),
            (SELECT name FROM job_queue WHERE id = $7),
            (SELECT name FROM job WHERE id = $9),
            (SELECT cloud_provider_code FROM temp_compute_env),
            (SELECT instance_type_code FROM temp_compute_env),
            (SELECT location_name FROM temp_compute_env)
        RETURNING id

CREATE TABLE "job_definition_version" (
    id UUID PRIMARY KEY,
    job_definition_id UUID NOT NULL,
    "version" INTEGER NOT NULL,
    execution_timeout INTEGER NULL,
    max_retry INTEGER NOT NULL,
    -- docker credentials
    docker_image VARCHAR(2048) NOT NULL,
    container_registry_auth_id UUID NULL,
    -- docker container options
    container_cmd TEXT[] NULL,
    container_cmd_parameter_json JSONB NULL,
    container_env_json JSONB NULL,
    container_cpu_count FLOAT NULL,
    container_memory_mb INTEGER NULL,
    container_memory_swap_mb INTEGER NULL,
    container_user VARCHAR(255) NULL,
    container_privileged BOOLEAN NOT NULL DEFAULT FALSE,
    container_enable_init_process BOOLEAN NOT NULL DEFAULT FALSE,
    container_readonly_filesystem BOOLEAN NOT NULL DEFAULT FALSE,
    container_shared_memory_size_mb INTEGER NULL,
    container_swappiness INTEGER NULL,
    container_workdir VARCHAR(255) NULL,
    container_ulimit VARCHAR(255) NULL,
    -- end of docker container options
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL,
    -- extra
    log_job_definition_name VARCHAR(255) NULL
    -- END extra
)

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2,$3,$4) ORDER BY 1, 2, 3 /*application='PgHero'*/

CREATE TABLE "project" (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    code VARCHAR(255) NOT NULL,
    "description" VARCHAR(5000) NULL,
    logo VARCHAR(2048) NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    -- system limits
    resource_limit JSONB NULL,
    alloted_resource_limit JSONB NULL,
    sudo_resource_limit JSONB NULL,
    -- end limits
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

CREATE TABLE "project_user" (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    user_id UUID NOT NULL,
    is_admin BOOLEAN NOT NULL DEFAULT FALSE,
    access_scopes VARCHAR(100) ARRAY,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

CREATE TABLE "job_run" (
    id UUID PRIMARY KEY,
    job_id UUID NULL,
    compute_spawn_id UUID NULL,
    started_on TIMESTAMPTZ NULL,
    completed_on TIMESTAMPTZ NULL,
    "state" VARCHAR(255) NOT NULL,
    log TEXT[] NULL,
    internal_log TEXT[] NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL,
    -- extra
    log_job_id UUID NULL,
    log_compute_spawn_id UUID NULL,
    log_job_name VARCHAR(255) NULL
    -- END extra
)

-- Add migration script here
CREATE TABLE "aws_vpc_subnet" (
    id UUID PRIMARY KEY,
    aws_vpc_id UUID NOT NULL,
    subnet_id VARCHAR(255) NOT NULL,
    region_name VARCHAR(255) NOT NULL,
    availability_zone VARCHAR(255) NOT NULL,
    ipv4_cidr VARCHAR(255) NULL,
    ipv6_cidr VARCHAR(255) NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = ANY (current_schemas($1)) AND c.relname = $2 AND c.relkind IN ($3,$4) /*application='PgHero'*/

-- Add migration script here

CREATE TABLE "job_queue" (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "description" VARCHAR(5000) NULL,
    "priority" INTEGER NOT NULL,
    max_concurrency INTEGER NOT NULL DEFAULT 0,
    max_idle_second INTEGER NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

-- Add migration script here
CREATE TABLE "aws_vpc" (
    id UUID PRIMARY KEY,
    cloud_auth_id UUID NOT NULL,
    region_name VARCHAR(255) NOT NULL,
    vpc_id VARCHAR(255) NOT NULL,
    subnet_id VARCHAR(255) NULL,
    internet_gateway_id VARCHAR(255) NULL,
    route_table_id VARCHAR(255) NULL,
    elastic_ip_allocation_id VARCHAR(255) NULL,
    nat_gateway_id VARCHAR(255) NULL,
    last_validated_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

-- Add migration script here

-- TABLE instancy type location
CREATE TABLE "instance_type_location" (
    id UUID PRIMARY KEY,
    cloud_provider_location_id UUID NOT NULL,
    instance_type_id UUID NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL,
    -- cached data cols
    log_location_name VARCHAR(255) NULL,
    log_country_code VARCHAR(2) NULL,
    log_country_name VARCHAR(255) NULL,
    log_country_flag_icon VARCHAR(255) NULL,
    log_continent_name VARCHAR(255) NULL
)

WITH buckets AS (
            SELECT
                gt,
                gt + INTERVAL $4 AS ls
            FROM generate_series(
                $1::TIMESTAMPTZ,
                $2::TIMESTAMPTZ - INTERVAL $5,
                INTERVAL $6
            ) AS gt
        )
        SELECT
            b.ls AS "time?",
            COUNT(*) FILTER (WHERE jr.state = $7) AS "successful?",
            COUNT(*) FILTER (
                WHERE jr.state IN ($8, $9, $10)
            ) AS "failed?"
        FROM buckets b
        LEFT JOIN job_run jr
            ON jr.project_id = $3
            AND jr.completed_on > b.gt
            AND jr.completed_on <= b.ls
            AND jr.state IN ($11, $12, $13, $14)
        GROUP BY b.gt, b.ls
        ORDER BY b.ls

-- Add migration script here

CREATE TABLE "cron_job" (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    job_definition_version_id UUID NOT NULL,
    job_queue_id UUID NOT NULL,
    cron_expression VARCHAR(255) NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "description" VARCHAR(2048) NULL,
    next_trigger_at TIMESTAMPTZ NULL,
    last_run_on TIMESTAMPTZ NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL,
    -- extra
    log_project_code VARCHAR(255) NULL,
    log_job_definition_name VARCHAR(255) NULL,
    log_job_definition_version INTEGER NULL,
    log_job_queue_name VARCHAR(255) NULL
    -- END extra
)

CREATE TABLE "container_registry_auth" (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    registry_url VARCHAR(1024) NULL,
    username VARCHAR(255) NULL,
    "password" VARCHAR(255) NULL,
    docker_config VARCHAR(5000) NULL,
    keyfile_json JSONB NULL,
    access_key_id VARCHAR(1024) NULL,
    secret_access_key VARCHAR(1024) NULL,
    created_by UUID NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

-- Add migration script here

CREATE TABLE "cloud_auth" (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    cloud_provider_id UUID NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    account_identifier VARCHAR(255) NULL,
    access_token VARCHAR(1024) NULL,
    access_key_id VARCHAR(1024) NULL,
    secret_access_key VARCHAR(1024) NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

SELECT nsp.nspname AS schema, rel.relname AS table, con.conname AS name, fnsp.nspname AS referenced_schema, frel.relname AS referenced_table FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid LEFT JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid LEFT JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace WHERE con.convalidated = $1 /*application='PgHero'*/

-- Add migration script here

CREATE TABLE "compute_env" (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    -- fk_computeenv_cloudauthid
    cloud_auth_id UUID NULL,
    -- fk_computeenv_instancetypeid
    instance_type_id UUID NULL,
    -- fk_computeenv_instancetypelocationid
    instance_type_location_id UUID NULL,
    "name" VARCHAR(255) NOT NULL,
    compute_type VARCHAR(255) NOT NULL,
    storage_gb INTEGER NULL,
    metadata VARCHAR(5000) NULL,
    request_spot BOOLEAN NOT NULL DEFAULT FALSE,
    spot_bid_amount FLOAT NULL,
    spot_bid_amount_currency VARCHAR(16) NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

CREATE TABLE "project_api_key" (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL,
    "name" VARCHAR(32) NOT NULL,
    permission VARCHAR(255)[] NOT NULL,
    key_initials VARCHAR(15) NOT NULL,
    key_hash VARCHAR(255) NOT NULL,
    expire_by_date DATE NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    has_expired BOOLEAN NOT NULL DEFAULT FALSE,
    last_used_at TIMESTAMPTZ NULL,
    created_by UUID NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)

INSERT INTO "cloud_auth" (
        "id",
        "project_id",
        "cloud_provider_id", 
        "name",
        "access_token",
        "is_active"
    ) 
    VALUES (
        $1, 
        $2, 
        $3, 
        $4,
        $5, 
        $6
    )

CREATE UNIQUE INDEX IF NOT EXISTS "unique_user_email"
    ON "user" (lower(email))

-- Add migration script here

-- TABLE continent
CREATE TABLE "continent" (
  code VARCHAR(2) PRIMARY KEY,
  -- unique_continent_name
  "name" VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NULL
)

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2,$3,$4,$5,$6,$7,$8,$9,$10) ORDER BY 1, 2, 3 /*application='PgHero'*/

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3,$4,$5,$6) ORDER BY 1, 2 /*application='PgHero'*/

ALTER TABLE action_log
    ADD CONSTRAINT "fk_actionLog_projectId"
    FOREIGN KEY (project_id)
    REFERENCES "project"(id)
    ON DELETE SET NULL

UPDATE "country" SET flag_icon=$1 WHERE code=$2

SELECT
            cs.id,
            cs.name,
            cs.compute_env_id,
            cs.compute_type,
            cs.log_compute_env_name AS compute_env_name,
            cs.job_queue_id,
            cs.log_job_queue_name AS job_queue_name,
            cs.initial_job_id,
            cs.log_initial_job_name AS initial_job_name,
            cs.state,
            cs.created_at
        FROM compute_spawn cs
        WHERE cs.project_id = $1
        AND ($2::TEXT[] IS NULL OR cs.state = ANY($2))
        AND ($3::UUID IS NULL OR cs.job_queue_id = $3)
        AND ($4::UUID IS NULL OR cs.compute_env_id = $4)
        ORDER BY cs.created_at DESC
        LIMIT $5

-- Indexes for compute_spawn_scheduler.rs
CREATE INDEX IF NOT EXISTS "idx_project_isActiveLastSchedulerAccess"
    ON project (last_scheduler_access ASC) 
    WHERE is_active = true

SET client_min_messages TO 'warning' /*application='PgHero'*/

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2,$3,$4,$5,$6,$7,$8,$9) ORDER BY 1, 2, 3 /*application='PgHero'*/

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3,$4,$5,$6,$7,$8,$9,$10,$11) ORDER BY 1, 2 /*application='PgHero'*/

ALTER TABLE "project_api_key"
    ALTER COLUMN "key_initials" TYPE VARCHAR(32),
    ADD CONSTRAINT "unique_projectApiKey_keyInitials" UNIQUE ("key_initials"),
    DROP CONSTRAINT "unique_projectApiKey_keyHash"

CREATE TABLE "job_queue_compute_env" (
    id UUID PRIMARY KEY,
    job_queue_id UUID NOT NULL,
    compute_env_id UUID NOT NULL,
    "order" INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NULL
)