-- +goose Up
-- A newly provisioned organization's owner must start with every tool, or the organization is born
-- locked out of itself.
--
-- Migration 0062 made tool entitlement fail closed and backfilled every user that EXISTED at that moment.
-- provision_org, which creates a brand new organization and its first owner, was never taught to grant
-- anything. So an org provisioned after 0062 produced an owner who could authenticate and then found
-- every product 404, with no way back: SetUserTools refuses a self edit, and the possession rule means an
-- actor can only confer tools they already hold, which is none. There is no second account to ask for
-- help, because the owner IS the first account. Recovery required direct database access.
--
-- Fixing it here rather than in the application matters: the owner and the grants must appear in the SAME
-- transaction that creates the organization. Doing it in the CLI afterwards would leave a window, and a
-- failure in that window produces exactly the stranded org this migration exists to prevent.

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION provision_org_grant_owner_tools() RETURNS trigger AS $$
BEGIN
    -- Only the first owner of an organization is seeded this way. Every later account is created through
    -- the invite path, which carries an explicit tool set chosen by a human, and must keep failing closed.
    IF NEW.role = 'owner' AND NOT EXISTS (
        SELECT 1 FROM users u
         WHERE u.organization_id = NEW.organization_id AND u.id <> NEW.id
    ) THEN
        INSERT INTO user_tool_access (organization_id, user_id, tool)
        SELECT NEW.organization_id, NEW.id, t.tool
          FROM (VALUES ('missionmatch'),('screenmatch'),('slatematch'),('recruitmatch')) AS t(tool)
        ON CONFLICT DO NOTHING;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
-- +goose StatementEnd

-- A trigger rather than an edit to provision_org itself, because provision_org is a large SECURITY
-- DEFINER function defined in 0009 and re declaring it here would fork its definition across two
-- migrations, so a later change to 0009 would silently not apply. The trigger states the one new
-- invariant, next to the table it constrains, and survives any future rewrite of the provisioning path.
DROP TRIGGER IF EXISTS trg_provision_org_grant_owner_tools ON users;
CREATE TRIGGER trg_provision_org_grant_owner_tools
    AFTER INSERT ON users
    FOR EACH ROW EXECUTE FUNCTION provision_org_grant_owner_tools();

-- Repair any organization already provisioned into the stranded state.
INSERT INTO user_tool_access (organization_id, user_id, tool)
SELECT u.organization_id, u.id, t.tool
  FROM users u
  CROSS JOIN (VALUES ('missionmatch'),('screenmatch'),('slatematch'),('recruitmatch')) AS t(tool)
 WHERE u.role IN ('owner', 'superuser')
   AND NOT EXISTS (SELECT 1 FROM user_tool_access a WHERE a.user_id = u.id)
ON CONFLICT DO NOTHING;

-- +goose Down
DROP TRIGGER IF EXISTS trg_provision_org_grant_owner_tools ON users;
DROP FUNCTION IF EXISTS provision_org_grant_owner_tools();