-- name: CreateRepository :one
INSERT INTO repositories (name, public) VALUES ($1, $2) RETURNING id;

-- name: GrantRepositoryAccess :exec
INSERT INTO repository_access (repository_id, user_id) VALUES ($1, $2)
ON CONFLICT (repository_id, user_id) DO NOTHING;

-- name: RevokeRepositoryAccess :exec
DELETE FROM repository_access WHERE repository_id = $1 AND user_id = $2;

-- name: CheckUserRepositoryAccess :one
SELECT EXISTS (
    SELECT 1 FROM repository_access
    WHERE repository_id = $1 AND user_id = $2
) AS has_access;

-- name: GetUserAccessibleRepositories :many
SELECT r.* FROM repositories r
LEFT JOIN repository_access ra ON r.id = ra.repository_id
WHERE r.public = TRUE OR ra.user_id = $1
GROUP BY r.id
ORDER BY r.name;

-- name: GetRepositoryUsers :many
SELECT u.* FROM users u
JOIN repository_access ra ON u.id = ra.user_id
WHERE ra.repository_id = $1
ORDER BY u.username;

-- name: GetPublicRepositories :many
SELECT * FROM repositories
WHERE public = TRUE
ORDER BY name;

-- name: GetRepository :one
SELECT * FROM repositories WHERE id = $1;

-- name: GetRepositoryByName :one
SELECT * FROM repositories WHERE name = $1;

-- name: GetRepositoryFiles :many
SELECT DISTINCT f.name, f.executable, f.content_hash, f.conflict
FROM files f
JOIN change_files cf ON f.id = cf.file_id
JOIN changes c ON cf.change_id = c.id
JOIN bookmarks b ON c.id = b.change_id
WHERE c.repository_id = $1 AND b.name = @bookmark
ORDER BY f.name;

-- name: GetRepositoryFilesForRevisionFuzzy :many
WITH target_change AS (
    -- First priority: exact bookmark match
    SELECT b.change_id AS id, 1 AS priority
    FROM bookmarks b
    WHERE b.repository_id = $1
      AND b.name = @revision::text

    UNION ALL

    -- Second priority: exact change name match
    SELECT c.id, 2 AS priority
    FROM changes c
    WHERE c.repository_id = $1
      AND c.name = @revision::text

    UNION ALL

    -- Third priority: change name prefix match
    SELECT c.id, 3 AS priority
    FROM changes c
    WHERE c.repository_id = $1
      AND c.name LIKE @revision::text || '%'
      AND c.name != @revision::text  -- Exclude exact matches (already covered above)
)
SELECT DISTINCT f.*
FROM files f
JOIN change_files cf ON f.id = cf.file_id
JOIN target_change tc ON cf.change_id = tc.id
WHERE tc.id = (SELECT id FROM target_change ORDER BY priority ASC LIMIT 1)
ORDER BY f.name;

-- name: GetRepositoryIgnoreFilesForRevisionFuzzy :many
WITH target_change AS (
    -- First priority: exact bookmark match
    SELECT b.change_id AS id, 1 AS priority
    FROM bookmarks b
    WHERE b.repository_id = $1
      AND b.name = @revision::text

    UNION ALL

    -- Second priority: exact change name match
    SELECT c.id, 2 AS priority
    FROM changes c
    WHERE c.repository_id = $1
      AND c.name = @revision::text

    UNION ALL

    -- Third priority: change name prefix match
    SELECT c.id, 3 AS priority
    FROM changes c
    WHERE c.repository_id = $1
      AND c.name LIKE @revision::text || '%'
      AND c.name != @revision::text  -- Exclude exact matches (already covered above)
)
SELECT DISTINCT f.*
FROM files f
JOIN change_files cf ON f.id = cf.file_id
JOIN target_change tc ON cf.change_id = tc.id
WHERE tc.id = (SELECT id FROM target_change ORDER BY priority ASC LIMIT 1)
  AND (f.name LIKE '%.gitignore' OR f.name LIKE '%.pogoignore')
ORDER BY f.name;

-- name: GetRepositoryIgnoreFilesForChangeId :many
SELECT DISTINCT f.*
FROM files f
JOIN change_files cf ON f.id = cf.file_id
WHERE cf.change_id = $1
  AND (f.name LIKE '%.gitignore' OR f.name LIKE '%.pogoignore')
ORDER BY f.name;

-- name: GetRepositoryBookmarkFileByName :one
SELECT DISTINCT f.name, f.executable, f.content_hash, f.conflict
FROM files f
JOIN change_files cf ON f.id = cf.file_id
JOIN changes c ON cf.change_id = c.id
JOIN bookmarks b ON c.id = b.change_id
WHERE c.repository_id = $1 AND b.name = @bookmark::text AND f.name ILIKE @filename::text || '%'
LIMIT 1;

-- name: CreateUser :one
INSERT INTO users (username) VALUES ($1) RETURNING id;

-- name: GetUserByUsername :one
SELECT * FROM users WHERE username = $1;

-- name: GetUser :one
SELECT * FROM users WHERE id = $1;

-- name: CreatePersonalAccessToken :one
INSERT INTO personal_access_tokens (token, user_id) 
VALUES ($1, $2) RETURNING id;

-- name: GetUserByToken :one
SELECT u.* FROM users u
JOIN personal_access_tokens pat ON u.id = pat.user_id
WHERE pat.token = $1;

-- name: CountUsers :one
SELECT COUNT(*) FROM users;

-- name: CreateUserWithToken :exec
WITH new_user AS (
    INSERT INTO users (username) VALUES ($1) RETURNING id
)
INSERT INTO personal_access_tokens (token, user_id)
SELECT $2, id FROM new_user;

-- name: CreateInitChange :one
INSERT INTO changes (repository_id, name, description, author_id, depth)
VALUES ($1, $2, 'init', $3, 0)
RETURNING id;

-- name: CreateChange :one
INSERT INTO changes (
  repository_id,
  name,
  description,
  author_id,
  depth
)
VALUES (
  $1,  -- repository_id
  $2,  -- name
  $3,  -- description
  $4,  -- author_id
  0
)
RETURNING id AS change_id;

-- name: SetParent :exec
INSERT INTO change_relations (
  change_id,
  parent_id
)
VALUES (
  $1,  -- child change_id
  $2   -- parent_id
)
ON CONFLICT (change_id, parent_id) DO NOTHING;

-- name: SetDepthFromParent :exec
UPDATE changes AS child
SET depth      = GREATEST(parent.depth + 1, child.depth),
    updated_at = CURRENT_TIMESTAMP
FROM changes AS parent
WHERE child.id   = $1  -- child change_id
  AND parent.id  = $2; -- parent change_id

-- name: GetAllChangeRelations :many
SELECT
  child.id    AS child_id,
  child.name  AS child_name,
  parent.id   AS parent_id,
  COALESCE(parent.name, '~') AS parent_name
FROM change_relations cr
JOIN changes child
  ON child.id = cr.change_id
LEFT JOIN changes parent
  ON parent.id = cr.parent_id
WHERE child.repository_id = $1;

-- name: GetNewestChanges :many
SELECT
  id,
  name,
  description,
  created_at,
  updated_at,
  get_unique_prefix(id) AS unique_prefix
FROM changes
WHERE repository_id = $1
ORDER BY updated_at DESC
LIMIT $2;

-- name: GetChangeRelationsForChanges :many
SELECT
  child.id    AS child_id,
  child.name  AS child_name,
  parent.id   AS parent_id,
  COALESCE(parent.name, '~') AS parent_name
FROM change_relations cr
JOIN changes child
  ON child.id = cr.change_id
LEFT JOIN changes parent
  ON parent.id = cr.parent_id
WHERE child.id = ANY(@change_ids::BIGINT[]);

-- name: GetChange :one
SELECT
  changes.*,
  get_unique_prefix(changes.id) AS unique_prefix
FROM changes
WHERE id = $1;

-- name: GetChangeName :one
SELECT name FROM changes WHERE id = $1;

-- name: GetRepositoryFilesForChangeId :many
SELECT DISTINCT f.*
FROM files f
JOIN change_files cf ON f.id = cf.file_id
WHERE cf.change_id = $1
ORDER BY f.name;

-- name: GetChangeDescription :one
SELECT description FROM changes WHERE id = $1;

-- name: GetChangeBookmarks :many
SELECT b.name
FROM bookmarks b
JOIN changes c ON c.id = b.change_id
WHERE c.id = $1;

-- name: IsChangeInConflict :one
SELECT EXISTS (
    SELECT 1 FROM change_files cf
    JOIN files f ON cf.file_id = f.id
    WHERE cf.change_id = $1 AND f.conflict = TRUE
);

-- name: GetConflictFilesForChange :many
SELECT f.name FROM change_files cf
JOIN files f ON cf.file_id = f.id
WHERE cf.change_id = $1 AND f.conflict = TRUE
ORDER BY f.name;

-- name: FindLCA :one
WITH RECURSIVE ancestors AS (
    -- Start with the two target changes
    SELECT
        c.id AS change_id,
        c.id AS origin_id,
        c.depth,
        0 AS distance
    FROM changes c
    WHERE c.id IN ($1, $2)

    UNION ALL

    -- Walk up the DAG via parents
    SELECT
        cr.parent_id AS change_id,
        a.origin_id,
        c.depth,
        a.distance + 1
    FROM ancestors a
    JOIN change_relations cr ON cr.change_id = a.change_id
    JOIN changes c ON c.id = cr.parent_id
    WHERE c.depth <= (
        -- Stop at the minimum depth of our starting nodes
        SELECT MAX(depth) FROM changes WHERE id IN ($1, $2)
    )
)
SELECT change_id AS lca_id
FROM ancestors
GROUP BY change_id
HAVING COUNT(DISTINCT origin_id) = 2  -- Reachable from both inputs
ORDER BY MAX(depth) DESC  -- Highest depth = closest to leaves
LIMIT 1;

-- name: GetThreeWayMergeFiles :many
WITH all_file_names AS (
    SELECT DISTINCT f.name
    FROM change_files cf
    JOIN files f ON cf.file_id = f.id
    WHERE cf.change_id IN (@lca, @a, @b)
),
lca_files AS (
    SELECT DISTINCT ON (f.name) f.id, f.name, f.executable, f.content_hash
    FROM change_files cf
    JOIN files f ON cf.file_id = f.id
    WHERE cf.change_id = @lca
    ORDER BY f.name, f.id
),
a_files AS (
    SELECT DISTINCT ON (f.name) f.id, f.name, f.executable, f.content_hash
    FROM change_files cf
    JOIN files f ON cf.file_id = f.id
    WHERE cf.change_id = @a
    ORDER BY f.name, f.id
),
b_files AS (
    SELECT DISTINCT ON (f.name) f.id, f.name, f.executable, f.content_hash
    FROM change_files cf
    JOIN files f ON cf.file_id = f.id
    WHERE cf.change_id = @b
    ORDER BY f.name, f.id
)
SELECT
    afn.name as file_name,
    lca.id as lca_file_id,
    lca.executable as lca_executable,
    lca.content_hash as lca_content_hash,
    a.id as a_file_id,
    a.executable as a_executable,
    a.content_hash as a_content_hash,
    b.id as b_file_id,
    b.executable as b_executable,
    b.content_hash as b_content_hash
FROM all_file_names afn
LEFT JOIN lca_files lca ON afn.name = lca.name
LEFT JOIN a_files a ON afn.name = a.name
LEFT JOIN b_files b ON afn.name = b.name
ORDER BY afn.name;

-- name: CopyChangeFiles :exec
INSERT INTO change_files (
  change_id,
  file_id
)
SELECT
  @target_change,
  src.file_id
FROM
  change_files AS src
WHERE
  src.change_id = @source_change
ON CONFLICT (change_id, file_id) DO NOTHING;

-- name: GetChangeFiles :many
SELECT f.id, f.content_hash
FROM files f
JOIN change_files cf ON f.id = cf.file_id
WHERE cf.change_id = $1;

-- name: ClearChangeFiles :exec
DELETE FROM change_files WHERE change_id = $1;

-- name: FindChangeByNameExact :one
SELECT id FROM changes WHERE repository_id = $1 AND name = @revision::text;

-- name: FindChangeByNameFuzzy :one
WITH matches AS (
    -- First priority: exact bookmark match
    SELECT b.change_id AS id, 1 AS priority
    FROM bookmarks b
    WHERE b.repository_id = @repository
      AND b.name = @revision::text

    UNION ALL

    -- Second priority: exact change name match
    SELECT c.id, 2 AS priority
    FROM changes c
    WHERE c.repository_id = @repository
      AND c.name = @revision::text

    UNION ALL

    -- Third priority: change name prefix match
    SELECT c.id, 3 AS priority
    FROM changes c
    WHERE c.repository_id = @repository
      AND c.name LIKE @revision::text || '%'
      AND c.name != @revision::text  -- Exclude exact matches (already covered above)
)
SELECT id FROM matches
ORDER BY priority ASC
LIMIT 1;

-- name: GetUniquePrefix :one
SELECT get_unique_prefix($1::BIGINT) AS unique_prefix;

-- name: UpdateChangeTimestamp :exec
UPDATE changes SET updated_at = CURRENT_TIMESTAMP WHERE id = $1;

-- name: SetChangeDescription :exec
UPDATE changes SET description = @description::text WHERE id = $1;

-- name: AddFileToChange :exec
WITH ins AS (
  INSERT INTO files (name, executable, content_hash, conflict)
  VALUES ($2, $3, $4, $5)
  ON CONFLICT (name, executable, content_hash) DO UPDATE SET conflict = $5
  RETURNING id
)
INSERT INTO change_files (change_id, file_id)
SELECT $1, COALESCE((SELECT id FROM ins), (SELECT id FROM files WHERE files.name = $2 AND files.executable = $3 AND files.content_hash = $4))
ON CONFLICT (change_id, file_id) DO NOTHING;

-- name: GetPublicRepositoryBookmarks :many
SELECT b.name
FROM bookmarks b
JOIN changes c ON b.change_id = c.id
JOIN repositories r ON c.repository_id = r.id
WHERE r.name = $1 AND r.public = TRUE
ORDER BY c.updated_at DESC;

-- name: GetPublicBookmarkTimeStamp :one
SELECT c.updated_at
FROM bookmarks b
JOIN changes c ON b.change_id = c.id
JOIN repositories r ON c.repository_id = r.id
WHERE r.name = $1 AND r.public = TRUE AND b.name = $2;

-- name: GetPublicGoModByBookmark :one
SELECT f.content_hash
FROM files f
JOIN change_files cf ON f.id = cf.file_id
JOIN changes c ON cf.change_id = c.id
JOIN bookmarks b ON c.id = b.change_id
JOIN repositories r ON c.repository_id = r.id
WHERE r.name = @repository AND r.public = TRUE AND b.name = @bookmark AND f.name = 'go.mod'
LIMIT 1;

-- name: GetPublicFileHashesByBookmark :many
SELECT f.name, f.content_hash
FROM files f
JOIN change_files cf ON f.id = cf.file_id
JOIN changes c ON cf.change_id = c.id
JOIN bookmarks b ON c.id = b.change_id
JOIN repositories r ON c.repository_id = r.id
WHERE r.name = @repository AND r.public = TRUE AND b.name = @bookmark
ORDER BY f.name;

-- name: SetBookmark :exec
INSERT INTO bookmarks (repository_id, name, change_id)
VALUES ($1, $2, $3)
ON CONFLICT (repository_id, name) DO UPDATE SET change_id = $3;

-- name: GetBookmark :one
SELECT change_id
FROM bookmarks
WHERE repository_id = $1 AND name = $2;

-- name: GetBookmarks :many
SELECT b.name AS bookmark, b.change_id, c.name AS change_name, c.updated_at
FROM bookmarks b
JOIN changes c ON c.id = b.change_id
WHERE b.repository_id = $1;

-- name: RemoveBookmark :exec
DELETE FROM bookmarks
WHERE repository_id = $1 AND name = $2;

-- name: GetCIConfigFiles :many
SELECT f.name, f.content_hash
FROM files f
JOIN change_files cf ON f.id = cf.file_id
WHERE cf.change_id = $1 AND f.name LIKE '.pogo/ci/%';

-- name: GetChangeChildren :many
SELECT c.id, c.name
FROM changes c
JOIN change_relations cr ON c.id = cr.change_id
WHERE cr.parent_id = $1;

-- name: GetChangeParents :many
SELECT c.id, c.name
FROM changes c
JOIN change_relations cr ON c.id = cr.parent_id
WHERE cr.change_id = $1;

-- name: DeleteChange :exec
DELETE FROM changes WHERE id = $1;

-- name: UpdateChildrenParents :exec
INSERT INTO change_relations (change_id, parent_id)
SELECT child.change_id, parent.parent_id
FROM change_relations child
CROSS JOIN change_relations parent
WHERE child.parent_id = $1 AND parent.change_id = $1
ON CONFLICT (change_id, parent_id) DO NOTHING;

-- name: RemoveChangeRelations :exec
DELETE FROM change_relations WHERE change_id = $1 OR parent_id = $1;

-- name: GetAllDescendants :many
WITH RECURSIVE descendants AS (
    -- Start with the target change
    SELECT c.id AS change_id, c.name AS change_name, c.depth AS change_depth
    FROM changes c
    WHERE c.id = $1

    UNION ALL

    -- Recursively find all children
    SELECT child.id AS change_id, child.name AS change_name, child.depth AS change_depth
    FROM changes child
    JOIN change_relations cr ON child.id = cr.change_id
    JOIN descendants parent ON cr.parent_id = parent.change_id
)
SELECT change_id, change_name, change_depth
FROM descendants
WHERE change_id != $1  -- Exclude the root change itself
ORDER BY change_depth DESC;  -- Delete deepest children first

-- name: UpdateRepositoryName :exec
UPDATE repositories SET name = $2 WHERE id = $1;

-- name: UpdateRepositoryVisibility :exec
UPDATE repositories SET public = $2 WHERE id = $1;

-- name: GrantRepositoryAccessByUsername :exec
INSERT INTO repository_access (repository_id, user_id)
SELECT $1, u.id
FROM users u
WHERE u.username = $2
ON CONFLICT (repository_id, user_id) DO NOTHING;

-- name: RevokeRepositoryAccessByUsername :exec
DELETE FROM repository_access
WHERE repository_id = $1 
  AND user_id = (SELECT id FROM users WHERE username = $2);

-- name: GetReachableFileHashes :many
SELECT DISTINCT f.content_hash
FROM files f
JOIN change_files cf ON f.id = cf.file_id
JOIN changes c ON cf.change_id = c.id
JOIN repositories r ON c.repository_id = r.id
ORDER BY f.content_hash;

-- name: GetUnreachableFiles :many
SELECT f.id, f.content_hash
FROM files f
WHERE NOT EXISTS (
    SELECT 1 FROM change_files cf
    WHERE cf.file_id = f.id
);

-- name: DeleteUnreachableFiles :exec
DELETE FROM files
WHERE NOT EXISTS (
    SELECT 1 FROM change_files cf
    WHERE cf.file_id = files.id
);

-- name: GetAllFileHashes :many
SELECT DISTINCT content_hash FROM files ORDER BY content_hash;

-- name: CountFiles :one
SELECT COUNT(DISTINCT content_hash) AS count FROM files;

-- name: GetOrphanedFileIds :many
SELECT f.id, f.content_hash
FROM files f
WHERE f.id = ANY(@file_ids::BIGINT[])
AND NOT EXISTS (
    SELECT 1 FROM change_files cf
    WHERE cf.file_id = f.id
);

-- name: DeleteFilesByIds :exec
DELETE FROM files
WHERE id = ANY(@file_ids::BIGINT[]);

-- name: CheckFileHashExists :one
SELECT EXISTS(SELECT 1 FROM files WHERE content_hash = $1) AS exists;

-- name: CheckMultipleFileHashesExist :many
SELECT input.hash AS content_hash
FROM (SELECT unnest(@hashes::BYTEA[]) AS hash) AS input
WHERE EXISTS (SELECT 1 FROM files WHERE content_hash = input.hash);

-- name: IsReadonly :one
SELECT EXISTS (
    -- Change has bookmarks pointing to it
    SELECT 1 FROM bookmarks b WHERE b.change_id = @change_id
    UNION
    -- Change has children
    SELECT 1 FROM change_relations cr WHERE cr.parent_id = @change_id
    UNION
    -- Change author is someone else
    SELECT 1 FROM changes c WHERE c.id = @change_id AND c.author_id != @user_id
) AS is_readonly;

-- name: GetRepositoryRootChange :one
SELECT c.id, c.name
FROM changes c
WHERE c.repository_id = $1
  AND NOT EXISTS (
    SELECT 1 FROM change_relations cr WHERE cr.change_id = c.id
  )
LIMIT 1;

-- name: CreateInvite :one
INSERT INTO invites (token, created_by_user_id, expires_at)
VALUES ($1, $2, $3) RETURNING id;

-- name: GetInviteByToken :one
SELECT * FROM invites WHERE token = $1;

-- name: UseInvite :exec
UPDATE invites SET used_at = CURRENT_TIMESTAMP, used_by_user_id = $2
WHERE token = $1 AND used_at IS NULL;

-- name: GetActiveInvitesByUser :many
SELECT * FROM invites 
WHERE created_by_user_id = $1 
  AND used_at IS NULL 
  AND expires_at > CURRENT_TIMESTAMP
ORDER BY created_at DESC;

-- name: GetAllInvitesByUser :many
SELECT i.*, u.username as used_by_username
FROM invites i
LEFT JOIN users u ON i.used_by_user_id = u.id
WHERE i.created_by_user_id = $1
ORDER BY i.created_at DESC;

-- name: DeleteExpiredInvites :exec
DELETE FROM invites WHERE expires_at < CURRENT_TIMESTAMP;

-- name: RevokeInvite :exec
DELETE FROM invites WHERE token = $1 AND created_by_user_id = $2 AND used_at IS NULL;

-- name: SetSecret :exec
INSERT INTO secrets (repository_id, key, value)
VALUES ($1, $2, $3)
ON CONFLICT (repository_id, key) DO UPDATE SET value = $3;

-- name: GetSecret :one
SELECT value FROM secrets WHERE repository_id = $1 AND key = $2;

-- name: GetAllSecrets :many
SELECT key, value FROM secrets WHERE repository_id = $1 ORDER BY key;

-- name: DeleteSecret :exec
DELETE FROM secrets WHERE repository_id = $1 AND key = $2;

-- name: CreateCIRun :one
INSERT INTO ci_runs (
  repository_id,
  config_filename,
  event_type,
  rev,
  pattern,
  reason,
  task_type,
  status_code,
  success,
  started_at,
  finished_at,
  log
) VALUES (
  @repository_id,
  @config_filename,
  @event_type,
  @rev,
  @pattern,
  @reason,
  @task_type,
  @status_code,
  @success,
  @started_at,
  @finished_at,
  @log
) RETURNING id;

-- name: ListCIRuns :many
SELECT
  id,
  repository_id,
  config_filename,
  event_type,
  rev,
  pattern,
  reason,
  task_type,
  status_code,
  success,
  started_at,
  finished_at
FROM ci_runs
WHERE repository_id = $1
ORDER BY started_at DESC;

-- name: GetCIRun :one
SELECT
  id,
  repository_id,
  config_filename,
  event_type,
  rev,
  pattern,
  reason,
  task_type,
  status_code,
  success,
  started_at,
  finished_at,
  log
FROM ci_runs
WHERE repository_id = $1 AND id = $2;

-- name: UpdateCIRun :exec
UPDATE ci_runs
SET
  status_code = @status_code,
  success = @success,
  finished_at = @finished_at,
  log = @log
WHERE id = @id;

-- name: DeleteExpiredCIRuns :one
WITH deleted AS (
  DELETE FROM ci_runs WHERE started_at < $1 RETURNING 1
)
SELECT COUNT(*) FROM deleted;

-- name: GetFilesForChange :many
SELECT f.name, f.executable, f.content_hash
FROM files f
JOIN change_files cf ON f.id = cf.file_id
WHERE cf.change_id = $1
ORDER BY f.name;