using Invercargill;
using Invercargill.DataStructures;
using Invercargill.Expressions;
using InvercargillSql;
using InvercargillSql.Orm;
using Inversion;
using ClankerSaddle.Data;

namespace ClankerSaddle.Services {

    /** Valid values of {@link ProjectLink.relationship}. */
    public const string LINK_CO_DEVELOPED = "co-developed";
    public const string LINK_REFERENCE = "reference";

    /**
     * Project storage: CRUD over `projects`, the directed
     * co-developed/reference link graph in `project_links`, and the
     * project's LLM-authored notes.
     */
    public class ProjectService : Object {

        private OrmSession db = inject<OrmSession>();
        private Connection connection = inject<Connection>();

        /** Inserts a project; `summary` may be empty. */
        public async Project create_project(string name, string git_url,
                string default_branch, string? summary) throws Error {
            var project = new Project() {
                name = name,
                git_url = git_url,
                default_branch = default_branch.length > 0 ? default_branch : "main",
                summary = summary != null && summary.length > 0 ? summary : null,
                created_at = new DateTime.now_local()
            };
            yield db.insert_async<Project>(project);
            return project;
        }

        /** Loads the project identified by `id`, or null. */
        public async Project? get_project(int64 id) throws Error {
            return yield db.query<Project>()
                .where(expr("id == $0", elem<int64?>(id)))
                .first_async();
        }

        /** Lists every project ordered by name. */
        public async ImmutableLot<Project> list_projects() throws Error {
            return yield db.query<Project>()
                .order_by(expr("name"))
                .materialise_async();
        }

        /**
         * Saves the editable fields of the project identified by `id`;
         * empty arguments keep their current values. Returns the saved
         * project, or null when no such project exists.
         */
        public async Project? update_project(int64 id, string? name, string? git_url,
                string? default_branch, string? summary) throws Error {
            var project = yield get_project(id);
            if (project == null) {
                return null;
            }
            if (name != null && name.length > 0) {
                project.name = name;
            }
            if (git_url != null && git_url.length > 0) {
                project.git_url = git_url;
            }
            if (default_branch != null && default_branch.length > 0) {
                project.default_branch = default_branch;
            }
            if (summary != null) {
                project.summary = summary.length > 0 ? summary : null;
            }
            yield db.update_async<Project>(project);
            return project;
        }

        /**
         * Links `linked_id` to `parent_id` with `relationship`
         * ('co-developed' or 'reference'); self-links and duplicates are
         * rejected.
         */
        public async ProjectLink add_link(int64 parent_id, int64 linked_id,
                string relationship) throws Error {
            if (relationship != LINK_CO_DEVELOPED && relationship != LINK_REFERENCE) {
                throw new Error(Quark.from_string("clanker-saddle"), 1,
                    @"Relationship must be '$(LINK_CO_DEVELOPED)' or '$(LINK_REFERENCE)'");
            }
            if (parent_id == linked_id) {
                throw new Error(Quark.from_string("clanker-saddle"), 2,
                    "A project cannot be linked to itself");
            }
            var parent = yield get_project(parent_id);
            var linked = yield get_project(linked_id);
            if (parent == null || linked == null) {
                throw new Error(Quark.from_string("clanker-saddle"), 3,
                    "Both projects must exist");
            }
            var existing = yield db.query<ProjectLink>()
                .where(expr("parent_project_id == $0 && linked_project_id == $1",
                    elem<int64?>(parent_id), elem<int64?>(linked_id)))
                .materialise_async();
            if (existing.length > 0) {
                throw new Error(Quark.from_string("clanker-saddle"), 4,
                    "Those projects are already linked");
            }

            var link = new ProjectLink() {
                parent_project_id = parent_id,
                linked_project_id = linked_id,
                relationship = relationship
            };
            yield db.insert_async<ProjectLink>(link);
            return link;
        }

        /** Deletes the link identified by `id`. */
        public async void remove_link(int64 id) throws Error {
            var link = yield db.query<ProjectLink>()
                .where(expr("id == $0", elem<int64?>(id)))
                .first_async();
            if (link != null) {
                yield db.delete_async<ProjectLink>((!)link);
            }
        }

        /** Lists every link whose parent is `project_id`. */
        public async ImmutableLot<ProjectLink> list_links(int64 project_id) throws Error {
            return yield db.query<ProjectLink>()
                .where(expr("parent_project_id == $0", elem<int64?>(project_id)))
                .materialise_async();
        }

        /** Loads the project identified by `name`, or null. */
        public async Project? get_project_by_name(string name) throws Error {
            return yield db.query<Project>()
                .where(expr("name == $0", elem(name)))
                .first_async();
        }

        /**
         * Inserts a project note (LLM-authored via the callback endpoint).
         * `source_task_id` may be 0 (unknown source task).
         */
        public async ProjectNote add_note(int64 project_id, int64 source_task_id,
                string body) throws Error {
            var note = new ProjectNote() {
                project_id = project_id,
                source_task_id = source_task_id,
                body = body,
                created_at = new DateTime.now_local()
            };
            yield db.insert_async<ProjectNote>(note);
            return note;
        }

        /** Loads the note identified by `id`, or null. */
        public async ProjectNote? get_note(int64 id) throws Error {
            return yield db.query<ProjectNote>()
                .where(expr("id == $0", elem<int64?>(id)))
                .first_async();
        }

        /** A project's notes, oldest first. */
        public async ImmutableLot<ProjectNote> list_notes(int64 project_id) throws Error {
            return yield db.query<ProjectNote>()
                .where(expr("project_id == $0", elem<int64?>(project_id)))
                .order_by(expr("id"))
                .materialise_async();
        }

        /** Deletes the note identified by `id`. */
        public async void delete_note(int64 id) throws Error {
            var note = yield get_note(id);
            if (note != null) {
                yield db.delete_async<ProjectNote>((!)note);
            }
        }

        // ------------------------------------------------------------------
        // Deletion (ownership-only cascade)
        // ------------------------------------------------------------------

        /**
         * The task statuses that keep {@link delete_project} refusing:
         * a task still queued, assigned to a saddle, working, blocked
         * or waiting on review has live work attached — deleting the
         * project out from under it would strand a running daemon.
         * ({@link STATUS_ASSIGNED} and {@link STATUS_BLOCKED} are
         * included: both sit between queued and terminal.)
         */
        public const string[] ACTIVE_TASK_STATUSES = {
            STATUS_QUEUED,
            STATUS_ASSIGNED,
            STATUS_WORKING,
            STATUS_BLOCKED,
            STATUS_REVIEW_REQUESTED
        };

        /**
         * What {@link delete_project} will remove — the confirmation
         * dialog's counts. `task_count` includes goal-run tasks; the
         * page's visible task list hides them, so the dialog says so
         * (they are the goal agents' own supervision rows, owned by
         * the project's goals).
         */
        public class ProjectDeleteCounts : Object {
            public int64 goal_count { get; set; }
            public int64 task_count { get; set; }
            public int64 active_task_count { get; set; }
        }

        /**
         * Counts the rows the deletion would take (the dialog's
         * "3 goals, 9 tasks"), plus how many tasks are still live
         * (the guard's refusal message). Null when the project does
         * not exist.
         */
        public async ProjectDeleteCounts? deletion_counts(int64 project_id) throws Error {
            var project = yield get_project(project_id);
            if (project == null) {
                return null;
            }
            var counts = new ProjectDeleteCounts();
            foreach (var row in yield db.query<Goal>()
                    .where(expr("project_id == $0", elem<int64?>(project_id)))
                    .materialise_async()) {
                counts.goal_count++;
            }
            foreach (var task in yield db.query<TaskEntity>()
                    .where(expr("project_id == $0", elem<int64?>(project_id)))
                    .materialise_async()) {
                counts.task_count++;
                if (task.status in ACTIVE_TASK_STATUSES) {
                    counts.active_task_count++;
                }
            }
            return counts;
        }

        /**
         * The rows a task deletion must take with it — everything that
         * dangles under a task row: attached rules and capabilities,
         * comments, blocks, notify prefs, the task's sessions and the
         * events riding them, plus the events scoped to the task
         * itself (web-originated rows carry no session). Bulk DELETEs
         * over the task's id set; rules/capabilities themselves belong
         * to the fleet, only the attachment rows go.
         */
        private async void delete_task_children(string task_ids) throws Error {
            exec(@"DELETE FROM task_rules WHERE task_id IN ($(task_ids))");
            exec(@"DELETE FROM task_capabilities WHERE task_id IN ($(task_ids))");
            exec(@"DELETE FROM task_comments WHERE task_id IN ($(task_ids))");
            exec(@"DELETE FROM task_blocks WHERE task_id IN ($(task_ids))");
            exec(@"DELETE FROM task_notify_prefs WHERE task_id IN ($(task_ids))");
            // Sessions carry the daemon-pushed events (events.session_id
            // FK); the task-scoped ledger covers the web-originated rows.
            exec(@"DELETE FROM events WHERE session_id IN"
                + @" (SELECT id FROM sessions WHERE task_id IN ($(task_ids)))");
            exec(@"DELETE FROM events WHERE scope_type = 'task' AND scope_id IN ($(task_ids))");
            exec(@"DELETE FROM sessions WHERE task_id IN ($(task_ids))");
            // A deleted predecessor must not strand its dependents in
            // not-ready forever — the reference clears, the dependent
            // row itself survives wherever it lives.
            exec(@"UPDATE tasks SET enqueue_after_task_id = 0"
                + @" WHERE enqueue_after_task_id IN ($(task_ids))");
        }

        /** Runs one SQL statement on the open transaction's connection. */
        private void exec(string sql) throws Error {
            connection.create_command(sql).execute_non_query();
        }

        /**
         * Deletes a project by OWNERSHIP CASCADE ONLY — the project
         * row and the rows the project owns: its direct tasks
         * (`tasks.project_id`), its goals (`goals.project_id`), and
         * those rows' children (task comments/blocks/rules/
         * capabilities/notify prefs, the tasks' sessions and events,
         * the goals' links and goal-scoped events, the project's
         * links, notes, understandings and rule attachments, and the
         * stack membership rows pointing at it).
         *
         * What it NEVER takes: another project's tasks, goals or
         * project rows — even when they reference this project as a
         * co-developed or reference link. Deleting a project only
         * tidies those projects' POINTER rows (their `project_links`
         * and `goal_links` entries naming this project); their work is
         * untouchable. Standalone tasks (project_id 0) are never
         * touched, and neither are stack rows (`stacks`) — only the
         * membership pointer rows.
         *
         * Refuses (error code 90) while the project still has tasks
         * in queued/assigned/working/blocked/review_requested: cancel
         * or let them finish first. Everything runs in ONE
         * transaction — a failure anywhere leaves the database
         * exactly as it was.
         */
        public async void delete_project(int64 project_id) throws Error {
            var project = yield get_project(project_id);
            if (project == null) {
                throw new Error(Quark.from_string("clanker-saddle"), 40,
                    @"No project exists with id $(project_id)");
            }

            // Guard: refuse while live work sits under the project.
            // Counted BEFORE the transaction opens (a refused delete
            // should not have opened one at all).
            var counts = yield deletion_counts(project_id);
            if (counts != null && ((!)counts).active_task_count > 0) {
                throw new Error(Quark.from_string("clanker-saddle"), 90,
                    @"The project still has $(((!)counts).active_task_count)"
                    + @" task(s) queued, working or awaiting review — cancel or"
                    + @" let them finish before deleting it");
            }

            // The id sets, read up front so the DELETEs inside the
            // transaction are simple IN-lists (subqueries against
            // rows the same transaction is deleting would race
            // themselves).
            var task_ids = yield owned_id_set("tasks", "project_id", project_id);
            var goal_ids = yield owned_id_set("goals", "project_id", project_id);
            string task_list = task_ids.length > 0 ? task_ids : "(0)";
            string goal_list = goal_ids.length > 0 ? goal_ids : "(0)";

            var transaction = connection.begin_transaction();
            try {
                // 1. Children of the project's tasks — before the
                //    task rows they hang from.
                yield delete_task_children(task_list);

                // 2. Children of the project's goals: their link
                //    pointers and the goal-scoped event ledger. The
                //    goals' tasks were handled above (tasks carry
                //    project_id, goal_id or both — the union of the
                //    two id sets covers every row the project owns).
                exec(@"DELETE FROM goal_links WHERE goal_id IN ($(goal_list))");
                exec(@"DELETE FROM events WHERE scope_type = 'goal' AND scope_id IN ($(goal_list))");
                // Project-scoped ledger rows (the scope constant exists
                // though no writer emits them yet — when one appears,
                // deleting the project must take them with it).
                exec(@"DELETE FROM events WHERE scope_type = 'project' AND scope_id = $(project_id)");

                // 3. The tasks, then the goals. A surviving row that
                //    referenced one of the goals (goal tasks always
                //    carry the goal's project_id, so this is purely
                //    defensive) has its pointer cleared, not the row
                //    taken.
                exec(@"DELETE FROM tasks WHERE id IN ($(task_list))");
                exec(@"DELETE FROM goals WHERE id IN ($(goal_list))");
                exec(@"UPDATE tasks SET goal_id = 0 WHERE goal_id IN ($(goal_list))"
                    + @" AND id NOT IN ($(task_list))");

                // 4. The project's own rows: understandings, notes,
                //    rule attachments, its outgoing links and the
                //    stack membership pointers.
                exec(@"DELETE FROM project_understandings WHERE project_id = $(project_id)");
                exec(@"DELETE FROM project_notes WHERE project_id = $(project_id)");
                exec(@"DELETE FROM rule_projects WHERE project_id = $(project_id)");
                exec(@"DELETE FROM project_links WHERE parent_project_id = $(project_id)");
                exec(@"DELETE FROM stack_projects WHERE project_id = $(project_id)");

                // 5. POINTER tidy-up in OTHER projects (their work is
                //    untouched): drop the link rows naming this
                //    project as a co-developed/reference target, in
                //    both the project and the goal link graphs.
                exec(@"DELETE FROM project_links WHERE linked_project_id = $(project_id)");
                exec(@"DELETE FROM goal_links WHERE project_id = $(project_id)");

                // 6. The project row itself, last.
                exec(@"DELETE FROM projects WHERE id = $(project_id)");

                transaction.commit();
            } catch (Error cascade_error) {
                transaction.rollback();
                throw cascade_error;
            }
        }

        /**
         * The comma-separated id list of `table` rows whose `column`
         * equals `value` ("1,4,9"), for IN-lists. Empty when none —
         * callers substitute "(0)". Only ever called with this
         * service's own table/column literals.
         */
        private async string owned_id_set(string table, string column,
                int64 value) throws Error {
            var ids = new Vector<string>();
            foreach (var row in yield connection.create_command(
                    @"SELECT id FROM $(table) WHERE $(column) = :value")
                .with_parameter<Element>("value", elem<int64?>(value))
                .execute_query_async()) {
                var id_element = row.get("id");
                int64? id = 0;
                if (id_element != null && ((!)id_element).try_get_as<int64?>(out id)
                        && id != 0) {
                    ids.add(((!)id).to_string());
                }
            }
            return string.joinv(",", ids.to_array());
        }
    }
}