-- +goose Up
-- +goose NO TRANSACTION

PRAGMA foreign_keys = OFF;

DROP TRIGGER IF EXISTS tasks_source_workspace_project_insert;
DROP TRIGGER IF EXISTS tasks_source_workspace_project_update;

CREATE TEMP TABLE migration_check_zero(value INTEGER NOT NULL CHECK (value = 0));

INSERT INTO migration_check_zero(value)
SELECT 1
WHERE EXISTS (
    SELECT 1
    FROM project_workflow_links pwl
    JOIN tasks t ON t.project_workflow_link_id = pwl.id
    WHERE pwl.unlinked_at_unix_ms != 0
);

INSERT INTO migration_check_zero(value)
SELECT 1
WHERE EXISTS (
    SELECT 1
    FROM tasks t
    JOIN project_workflow_links pwl ON pwl.id = t.project_workflow_link_id
    WHERE t.project_id != pwl.project_id
       OR t.workflow_id != pwl.workflow_id
);

INSERT INTO migration_check_zero(value)
SELECT 1
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT project_id
        FROM project_workflow_links
        WHERE is_default = 1
          AND unlinked_at_unix_ms = 0
        GROUP BY project_id
        HAVING COUNT(*) > 1
    )
);

ALTER TABLE projects ADD COLUMN default_project_workflow_link_id TEXT NOT NULL DEFAULT '';

UPDATE projects
SET default_project_workflow_link_id = COALESCE((
    SELECT pwl.id
    FROM project_workflow_links pwl
    WHERE pwl.project_id = projects.id
      AND pwl.is_default = 1
      AND pwl.unlinked_at_unix_ms = 0
    ORDER BY pwl.created_at_unix_ms ASC, pwl.rowid ASC
    LIMIT 1
), '');

DELETE FROM project_workflow_links
WHERE unlinked_at_unix_ms != 0;

CREATE TABLE project_workflow_links_new (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    workflow_id TEXT NOT NULL REFERENCES workflows(id) ON DELETE RESTRICT,
    created_at_unix_ms INTEGER NOT NULL CHECK (created_at_unix_ms >= 0),
    updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= 0),
    UNIQUE (project_id, id),
    UNIQUE (project_id, workflow_id)
);

INSERT INTO project_workflow_links_new (
    id,
    project_id,
    workflow_id,
    created_at_unix_ms,
    updated_at_unix_ms
)
SELECT
    id,
    project_id,
    workflow_id,
    created_at_unix_ms,
    updated_at_unix_ms
FROM project_workflow_links;

DROP TABLE project_workflow_links;
ALTER TABLE project_workflow_links_new RENAME TO project_workflow_links;

CREATE INDEX project_workflow_links_workflow_idx
    ON project_workflow_links(workflow_id);

CREATE VIEW project_workflow_link_records AS
SELECT
    pwl.id,
    pwl.project_id,
    pwl.workflow_id,
    CASE WHEN p.default_project_workflow_link_id = pwl.id THEN 1 ELSE 0 END AS is_default,
    pwl.created_at_unix_ms,
    pwl.updated_at_unix_ms
FROM project_workflow_links pwl
JOIN projects p ON p.id = pwl.project_id;

CREATE TABLE tasks_new (
    id TEXT PRIMARY KEY,
    project_workflow_link_id TEXT NOT NULL REFERENCES project_workflow_links(id) ON DELETE RESTRICT,
    workflow_revision_seen INTEGER NOT NULL CHECK (workflow_revision_seen >= 1),
    task_seq INTEGER NOT NULL CHECK (task_seq >= 1),
    short_id TEXT NOT NULL,
    title TEXT NOT NULL CHECK (length(trim(title)) > 0),
    body TEXT NOT NULL DEFAULT '',
    source_url TEXT NOT NULL DEFAULT '',
    source_workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
    managed_worktree_id TEXT REFERENCES worktrees(id) ON DELETE SET NULL,
    canceled_at_unix_ms INTEGER NOT NULL DEFAULT 0 CHECK (canceled_at_unix_ms >= 0),
    cancellation_reason TEXT NOT NULL DEFAULT '',
    created_at_unix_ms INTEGER NOT NULL CHECK (created_at_unix_ms >= 0),
    updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= 0),
    metadata_json TEXT NOT NULL DEFAULT '{}' CHECK (json_valid(metadata_json))
);

INSERT INTO tasks_new (
    id,
    project_workflow_link_id,
    workflow_revision_seen,
    task_seq,
    short_id,
    title,
    body,
    source_url,
    source_workspace_id,
    managed_worktree_id,
    canceled_at_unix_ms,
    cancellation_reason,
    created_at_unix_ms,
    updated_at_unix_ms,
    metadata_json
)
SELECT
    id,
    project_workflow_link_id,
    workflow_revision_seen,
    task_seq,
    short_id,
    title,
    body,
    source_url,
    source_workspace_id,
    managed_worktree_id,
    canceled_at_unix_ms,
    cancellation_reason,
    created_at_unix_ms,
    updated_at_unix_ms,
    metadata_json
FROM tasks;

DROP TABLE tasks;
ALTER TABLE tasks_new RENAME TO tasks;

CREATE INDEX tasks_project_workflow_link_idx
    ON tasks(project_workflow_link_id);

CREATE INDEX tasks_project_workflow_link_updated_idx
    ON tasks(project_workflow_link_id, updated_at_unix_ms DESC, id DESC);

CREATE INDEX tasks_short_id_idx
    ON tasks(short_id);

CREATE INDEX tasks_source_workspace_idx
    ON tasks(source_workspace_id);

CREATE INDEX tasks_managed_worktree_idx
    ON tasks(managed_worktree_id);

CREATE VIEW task_records AS
SELECT
    t.id,
    pwl.project_id,
    t.project_workflow_link_id,
    pwl.workflow_id,
    t.workflow_revision_seen,
    t.task_seq,
    t.short_id,
    t.title,
    t.body,
    t.source_url,
    t.source_workspace_id,
    t.managed_worktree_id,
    t.canceled_at_unix_ms,
    t.cancellation_reason,
    t.created_at_unix_ms,
    t.updated_at_unix_ms,
    t.metadata_json
FROM tasks t
JOIN project_workflow_links pwl ON pwl.id = t.project_workflow_link_id;

-- +goose StatementBegin
CREATE TRIGGER projects_default_workflow_link_insert
BEFORE INSERT ON projects
FOR EACH ROW
WHEN NEW.default_project_workflow_link_id != ''
 AND NOT EXISTS (
    SELECT 1
    FROM project_workflow_links pwl
    WHERE pwl.id = NEW.default_project_workflow_link_id
      AND pwl.project_id = NEW.id
 )
BEGIN
    SELECT RAISE(ABORT, 'default workflow link must belong to project');
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER projects_default_workflow_link_update
BEFORE UPDATE OF id, default_project_workflow_link_id ON projects
FOR EACH ROW
WHEN NEW.default_project_workflow_link_id != ''
 AND NOT EXISTS (
    SELECT 1
    FROM project_workflow_links pwl
    WHERE pwl.id = NEW.default_project_workflow_link_id
      AND pwl.project_id = NEW.id
 )
BEGIN
    SELECT RAISE(ABORT, 'default workflow link must belong to project');
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER project_workflow_links_default_delete
AFTER DELETE ON project_workflow_links
FOR EACH ROW
BEGIN
    UPDATE projects
    SET default_project_workflow_link_id = ''
    WHERE id = OLD.project_id
      AND default_project_workflow_link_id = OLD.id;
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER tasks_project_task_seq_insert
BEFORE INSERT ON tasks
FOR EACH ROW
WHEN EXISTS (
    SELECT 1
    FROM tasks existing
    JOIN project_workflow_links existing_link ON existing_link.id = existing.project_workflow_link_id
    JOIN project_workflow_links new_link ON new_link.id = NEW.project_workflow_link_id
    WHERE existing_link.project_id = new_link.project_id
      AND existing.task_seq = NEW.task_seq
)
BEGIN
    SELECT RAISE(ABORT, 'task sequence must be unique within project');
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER tasks_project_task_seq_update
BEFORE UPDATE OF project_workflow_link_id, task_seq ON tasks
FOR EACH ROW
WHEN EXISTS (
    SELECT 1
    FROM tasks existing
    JOIN project_workflow_links existing_link ON existing_link.id = existing.project_workflow_link_id
    JOIN project_workflow_links new_link ON new_link.id = NEW.project_workflow_link_id
    WHERE existing.id != OLD.id
      AND existing_link.project_id = new_link.project_id
      AND existing.task_seq = NEW.task_seq
)
BEGIN
    SELECT RAISE(ABORT, 'task sequence must be unique within project');
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER tasks_project_short_id_insert
BEFORE INSERT ON tasks
FOR EACH ROW
WHEN EXISTS (
    SELECT 1
    FROM tasks existing
    JOIN project_workflow_links existing_link ON existing_link.id = existing.project_workflow_link_id
    JOIN project_workflow_links new_link ON new_link.id = NEW.project_workflow_link_id
    WHERE existing_link.project_id = new_link.project_id
      AND existing.short_id = NEW.short_id
)
BEGIN
    SELECT RAISE(ABORT, 'task short id must be unique within project');
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER tasks_project_short_id_update
BEFORE UPDATE OF project_workflow_link_id, short_id ON tasks
FOR EACH ROW
WHEN EXISTS (
    SELECT 1
    FROM tasks existing
    JOIN project_workflow_links existing_link ON existing_link.id = existing.project_workflow_link_id
    JOIN project_workflow_links new_link ON new_link.id = NEW.project_workflow_link_id
    WHERE existing.id != OLD.id
      AND existing_link.project_id = new_link.project_id
      AND existing.short_id = NEW.short_id
)
BEGIN
    SELECT RAISE(ABORT, 'task short id must be unique within project');
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER tasks_source_workspace_project_insert
BEFORE INSERT ON tasks
FOR EACH ROW
WHEN NEW.source_workspace_id IS NOT NULL
 AND NOT EXISTS (
    SELECT 1
    FROM workspaces w
    JOIN project_workflow_links pwl ON pwl.id = NEW.project_workflow_link_id
    WHERE w.id = NEW.source_workspace_id
      AND w.project_id = pwl.project_id
 )
BEGIN
    SELECT RAISE(ABORT, 'source workspace must belong to task project');
END;
-- +goose StatementEnd

-- +goose StatementBegin
CREATE TRIGGER tasks_source_workspace_project_update
BEFORE UPDATE OF project_workflow_link_id, source_workspace_id ON tasks
FOR EACH ROW
WHEN NEW.source_workspace_id IS NOT NULL
 AND NOT EXISTS (
    SELECT 1
    FROM workspaces w
    JOIN project_workflow_links pwl ON pwl.id = NEW.project_workflow_link_id
    WHERE w.id = NEW.source_workspace_id
      AND w.project_id = pwl.project_id
 )
BEGIN
    SELECT RAISE(ABORT, 'source workspace must belong to task project');
END;
-- +goose StatementEnd

DROP TABLE migration_check_zero;

PRAGMA foreign_keys = ON;
