CREATE TABLE workspace (
    id           text PRIMARY KEY,          -- 'ws-xxxxxx'
    resource_id  text UNIQUE NOT NULL,      -- user-chosen slug: 'acme-corp'
    name         text NOT NULL,             -- display name
    created_at   timestamptz NOT NULL DEFAULT now(),
    deleted      boolean NOT NULL DEFAULT FALSE
);

-- For existing single-workspace installations, create the default workspace.
-- The workspace ID is derived from the existing setting.
INSERT INTO workspace (id, resource_id, name)
SELECT
    'ws-' || (value->>'workspaceId'),
    COALESCE(NULLIF(value->>'workspaceId', ''), 'default'),
    'Default Workspace'
FROM setting
WHERE name = 'WORKSPACE_PROFILE';

-- Helper: get the default workspace ID
-- All statements below use this subquery inline.

-- project
ALTER TABLE project ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE project SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE project ALTER COLUMN workspace SET NOT NULL;

-- instance
ALTER TABLE instance ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE instance SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE instance ALTER COLUMN workspace SET NOT NULL;

-- setting
ALTER TABLE setting ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE setting SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE setting ALTER COLUMN workspace SET NOT NULL;

-- policy
ALTER TABLE policy ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE policy SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE policy ALTER COLUMN workspace SET NOT NULL;

-- role
ALTER TABLE role ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE role SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE role ALTER COLUMN workspace SET NOT NULL;

-- idp
ALTER TABLE idp ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE idp SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE idp ALTER COLUMN workspace SET NOT NULL;

-- review_config
ALTER TABLE review_config ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE review_config SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE review_config ALTER COLUMN workspace SET NOT NULL;

-- user_group
ALTER TABLE user_group ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE user_group SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE user_group ALTER COLUMN workspace SET NOT NULL;

-- export_archive
ALTER TABLE export_archive ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE export_archive SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE export_archive ALTER COLUMN workspace SET NOT NULL;

-- audit_log
ALTER TABLE audit_log ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE audit_log SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE audit_log ALTER COLUMN workspace SET NOT NULL;

-- service_account (created in Step 01 without workspace)
ALTER TABLE service_account ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE service_account SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE service_account ALTER COLUMN workspace SET NOT NULL;

-- workload_identity (created in Step 01 without workspace)
ALTER TABLE workload_identity ADD COLUMN workspace text REFERENCES workspace(resource_id);
UPDATE workload_identity SET workspace = (SELECT id FROM workspace LIMIT 1);
ALTER TABLE workload_identity ALTER COLUMN workspace SET NOT NULL;

-- project
DROP INDEX idx_project_unique_resource_id;
CREATE UNIQUE INDEX idx_project_unique_resource_id ON project(workspace, resource_id);

-- instance
DROP INDEX idx_instance_unique_resource_id;
CREATE UNIQUE INDEX idx_instance_unique_resource_id ON instance(workspace, resource_id);

-- principal: no change needed (global identity, email unique globally)

-- setting
DROP INDEX idx_setting_unique_name;
CREATE UNIQUE INDEX idx_setting_unique_name ON setting(workspace, name);

-- role
DROP INDEX idx_role_unique_resource_id;
CREATE UNIQUE INDEX idx_role_unique_resource_id ON role(workspace, resource_id);

-- idp
DROP INDEX idx_idp_unique_resource_id;
CREATE UNIQUE INDEX idx_idp_unique_resource_id ON idp(workspace, resource_id);

-- user_group
DROP INDEX idx_user_group_unique_email;
CREATE UNIQUE INDEX idx_user_group_unique_email ON user_group(workspace, email) WHERE email IS NOT NULL;

-- service_account
CREATE UNIQUE INDEX idx_service_account_unique_workspace_email ON service_account(workspace, email);

-- workload_identity
CREATE UNIQUE INDEX idx_workload_identity_unique_workspace_email ON workload_identity(workspace, email);

-- These DO NOT change (already scoped through parent FKs):
-- idx_db_unique_instance_name ON db(instance, name)             -- instance is workspace-unique
-- idx_plan_check_run_unique_plan_id ON plan_check_run(plan_id)  -- plan_id is globally unique (bigserial)
-- idx_issue_unique_plan_id ON issue(plan_id)                    -- plan_id is globally unique (bigserial)
-- uk_task_run_task_id_attempt ON task_run(task_id, attempt)     -- task_id is globally unique (bigserial)
-- idx_db_group_unique_project_resource_id ON db_group(project, resource_id)       -- project is workspace-unique
-- idx_release_project_train_iteration ON release(project, train, iteration)       -- project is workspace-unique

CREATE INDEX idx_project_workspace ON project(workspace);
CREATE INDEX idx_instance_workspace ON instance(workspace);
CREATE INDEX idx_setting_workspace ON setting(workspace);
CREATE INDEX idx_policy_workspace ON policy(workspace);
CREATE INDEX idx_audit_log_workspace ON audit_log(workspace);
CREATE INDEX idx_service_account_workspace ON service_account(workspace);
CREATE INDEX idx_workload_identity_workspace ON workload_identity(workspace);