CREATE TABLE workspaces (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL REFERENCES tenants(id),
    name TEXT NOT NULL,
    slug TEXT NOT NULL,
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, slug)
);

CREATE UNIQUE INDEX idx_workspaces_tenant_default
    ON workspaces (tenant_id)
    WHERE is_default = TRUE;

CREATE TABLE workspace_memberships (
    id BIGSERIAL PRIMARY KEY,
    workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    tenant_id BIGINT NOT NULL REFERENCES tenants(id),
    role TEXT NOT NULL DEFAULT 'member',
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, user_id)
);

CREATE UNIQUE INDEX idx_memberships_user_primary
    ON workspace_memberships (user_id)
    WHERE is_primary = TRUE;

INSERT INTO workspaces (tenant_id, name, slug, is_default)
SELECT id, 'Default', 'default', TRUE
FROM tenants
ON CONFLICT (tenant_id, slug) DO NOTHING;

INSERT INTO workspace_memberships (workspace_id, user_id, tenant_id, role, is_primary)
SELECT w.id, tm.user_id, tm.tenant_id, 'member', TRUE
FROM tenant_memberships tm
JOIN workspaces w
  ON w.tenant_id = tm.tenant_id
 AND w.is_default = TRUE
WHERE tm.deleted_at IS NULL
ON CONFLICT (workspace_id, user_id) DO NOTHING;