ALTER TABLE auth_tokens ADD COLUMN attempts integer NOT NULL DEFAULT 0;
UPDATE auth_tokens SET used_at=now() WHERE used_at IS NULL;
UPDATE email_outbox SET state='EXPIRED',encrypted_body=NULL WHERE state IN ('PENDING','PROCESSING','FAILED');
CREATE TABLE workspaces(id uuid PRIMARY KEY,name text NOT NULL,timezone text NOT NULL,require_approval boolean NOT NULL DEFAULT true,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE workspace_members(workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,user_id uuid NOT NULL REFERENCES users(id),role text NOT NULL CHECK(role IN ('Owner','Admin','Content Manager','Editor','Approver','Viewer')),PRIMARY KEY(workspace_id,user_id));
CREATE INDEX memberships_user_idx ON workspace_members(user_id);
CREATE TABLE brand_kits(workspace_id uuid PRIMARY KEY REFERENCES workspaces(id) ON DELETE CASCADE,data jsonb NOT NULL DEFAULT '{}');
CREATE TABLE social_accounts(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),platform text NOT NULL,provider_id text NOT NULL,name text NOT NULL,state text NOT NULL DEFAULT 'Connected',last_sync timestamptz,UNIQUE(workspace_id,platform),UNIQUE(id,workspace_id));
CREATE TABLE social_account_tokens(account_id uuid PRIMARY KEY REFERENCES social_accounts(id) ON DELETE CASCADE,ciphertext text NOT NULL,expires_at timestamptz);
CREATE TABLE oauth_states(state_hash text PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),user_id uuid NOT NULL REFERENCES users(id),session_hash text NOT NULL,expires_at timestamptz NOT NULL);
CREATE TABLE content(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),title text NOT NULL,body text NOT NULL,variants jsonb NOT NULL DEFAULT '{}',platforms jsonb NOT NULL,status text NOT NULL DEFAULT 'DRAFT' CHECK(status IN ('DRAFT','NEEDS_REVIEW','APPROVED','SCHEDULED','PUBLISHING','PUBLISHED','FAILED','UNCERTAIN')),version integer NOT NULL DEFAULT 1,created_by uuid NOT NULL REFERENCES users(id),updated_by uuid NOT NULL REFERENCES users(id),approved_by uuid REFERENCES users(id),scheduled_by uuid REFERENCES users(id),published_by uuid REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now(),updated_at timestamptz NOT NULL DEFAULT now(),scheduled_at timestamptz,timezone text,UNIQUE(id,workspace_id));
CREATE INDEX content_workspace_status_idx ON content(workspace_id,status,created_at DESC);
CREATE TABLE content_versions(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),content_id uuid NOT NULL,version integer NOT NULL,snapshot jsonb NOT NULL,created_by uuid NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(content_id,version),FOREIGN KEY(content_id,workspace_id) REFERENCES content(id,workspace_id));
CREATE TABLE approvals(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),content_id uuid NOT NULL,version integer NOT NULL,actor_id uuid NOT NULL REFERENCES users(id),decision text NOT NULL CHECK(decision IN ('approve','reject')),created_at timestamptz NOT NULL DEFAULT now(),FOREIGN KEY(content_id,workspace_id) REFERENCES content(id,workspace_id));
CREATE TABLE scheduled_posts(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),content_id uuid NOT NULL,account_id uuid NOT NULL,content_version integer NOT NULL,execute_at timestamptz NOT NULL,timezone text NOT NULL,state text NOT NULL DEFAULT 'PENDING' CHECK(state IN ('PENDING','ENQUEUED','PROCESSING','PUBLISHED','FAILED','UNCERTAIN','CANCELLED')),attempt_started_at timestamptz,provider_post_id text,error_code text,created_at timestamptz NOT NULL DEFAULT now(),FOREIGN KEY(content_id,workspace_id) REFERENCES content(id,workspace_id),FOREIGN KEY(account_id,workspace_id) REFERENCES social_accounts(id,workspace_id),UNIQUE(content_id,account_id,content_version));
CREATE INDEX scheduled_pending_idx ON scheduled_posts(state,execute_at);
CREATE TABLE published_posts(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),scheduled_post_id uuid NOT NULL UNIQUE REFERENCES scheduled_posts(id),provider_post_id text NOT NULL,published_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE activity_logs(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),actor_id uuid REFERENCES users(id),action text NOT NULL,entity_type text NOT NULL,entity_id text NOT NULL,entity text NOT NULL,metadata jsonb NOT NULL DEFAULT '{}',previous_value jsonb,new_value jsonb,created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX activity_workspace_time_idx ON activity_logs(workspace_id,created_at DESC);
CREATE TABLE ai_generations(id uuid PRIMARY KEY,workspace_id uuid NOT NULL REFERENCES workspaces(id),actor_id uuid NOT NULL REFERENCES users(id),provider text NOT NULL,input jsonb NOT NULL,output jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now());

ALTER TABLE users ADD COLUMN active_workspace_id uuid REFERENCES workspaces(id);