app/projects/migrations/0010_collaboration_foundation.sql

begin;

-- Teams
create table if not exists teams (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    name varchar(255) not null,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

-- Team Members
create table if not exists team_members (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    team_id text not null references teams(id) on delete cascade,
    user_id text not null references users(id) on delete cascade,
    role varchar(50) not null,
    created_at timestamptz not null default now(),
    unique(team_id, user_id)
);

-- Invitations
create table if not exists invitations (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    email text not null,
    role varchar(50) not null,
    status varchar(20) not null check (status in ('pending', 'accepted', 'expired', 'revoked')),
    expires_at timestamptz not null,
    created_at timestamptz not null default now()
);

-- Project Collaborators
create table if not exists project_collaborators (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    project_id text not null references projects(id) on delete cascade,
    user_id text not null references users(id) on delete cascade,
    role varchar(50) not null,
    created_at timestamptz not null default now(),
    unique(project_id, user_id)
);

-- Approval Workflows
create table if not exists approval_workflows (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    project_id text not null references projects(id) on delete cascade,
    name varchar(255) not null,
    created_at timestamptz not null default now()
);

-- Approval Requests
create table if not exists approval_requests (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    workflow_id text not null references approval_workflows(id) on delete cascade,
    project_id text not null references projects(id) on delete cascade,
    status varchar(20) not null check (status in ('pending', 'approved', 'rejected')),
    created_by text not null references users(id) on delete cascade,
    created_at timestamptz not null default now()
);

-- Review Comments
create table if not exists review_comments (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    request_id text not null references approval_requests(id) on delete cascade,
    user_id text not null references users(id) on delete cascade,
    content text not null,
    created_at timestamptz not null default now()
);

-- Collaboration Activity
create table if not exists collaboration_activity (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    user_id text not null references users(id) on delete cascade,
    action varchar(100) not null,
    entity_id text not null,
    entity_type varchar(50) not null,
    metadata jsonb not null default '{}',
    created_at timestamptz not null default now()
);

-- Indexes
create index ix_teams_workspace_id on teams(workspace_id);
create index ix_invitations_workspace_id on invitations(workspace_id);
create index ix_team_members_team_id on team_members(team_id);
create index ix_project_collaborators_project_id on project_collaborators(project_id);
create index ix_approval_workflows_project_id on approval_workflows(project_id);
create index ix_approval_requests_workflow_id on approval_requests(workflow_id);
create index ix_review_comments_request_id on review_comments(request_id);
create index ix_collaboration_activity_workspace_id on collaboration_activity(workspace_id);

-- RLS
do $$
declare
    table_name text;
begin
    for table_name in select t.table_name
    from information_schema.tables t
    where t.table_schema = 'public'
    and t.table_name in ('teams', 'team_members', 'invitations', 'project_collaborators', 'approval_workflows', 'approval_requests', 'review_comments', 'collaboration_activity')
    loop
        execute format('alter table %I enable row level security', table_name);
        execute format('alter table %I force row level security', table_name);
        execute format('drop policy if exists collaboration_workspace_isolation on %I', table_name);
        execute format('create policy collaboration_workspace_isolation on %I using (workspace_id = current_setting(''app.workspace_id'', true)) with check (workspace_id = current_setting(''app.workspace_id'', true))', table_name);
    end loop;
end $$;

commit;