app/social/migrations/0010_analytics_insights.sql

begin;

create table if not exists analytics_sync_runs (
  id text primary key,
  workspace_id text not null,
  project_id text,
  provider text,
  date_from timestamptz not null,
  date_to timestamptz not null,
  timezone text not null,
  status text not null default 'queued'
    check (status in ('queued','running','succeeded','partial','failed','cancelled')),
  idempotency_key text not null check (char_length(idempotency_key) between 8 and 255),
  requested_by text,
  attempt_count integer not null default 0 check (attempt_count >= 0),
  next_attempt_at timestamptz,
  error_code text,
  error_message text,
  metrics_count integer not null default 0 check (metrics_count >= 0),
  created_at timestamptz not null default now(),
  started_at timestamptz,
  completed_at timestamptz,
  updated_at timestamptz not null default now(),
  constraint ck_analytics_sync_range check (date_to > date_from),
  constraint uq_analytics_sync_workspace_idempotency unique (workspace_id, idempotency_key)
);
create index if not exists ix_analytics_sync_workspace_status
  on analytics_sync_runs(workspace_id, status);
create index if not exists ix_analytics_sync_due
  on analytics_sync_runs(status, next_attempt_at);

create table if not exists analytics_metric_snapshots (
  id text primary key,
  workspace_id text not null,
  project_id text,
  social_post_id text references social_posts(id) on delete cascade,
  social_account_id text references social_accounts(id) on delete cascade,
  provider text not null,
  external_object_id text not null,
  metric_name text not null,
  metric_value double precision not null,
  bucket_start timestamptz not null,
  dimensions jsonb not null default '{}'::jsonb,
  source text not null default 'provider',
  collected_at timestamptz not null default now(),
  provider_updated_at timestamptz,
  constraint uq_analytics_metric_snapshot
    unique (workspace_id, provider, external_object_id, metric_name, bucket_start)
);
create index if not exists ix_analytics_metric_snapshots_workspace_bucket
  on analytics_metric_snapshots(workspace_id, bucket_start desc);

create table if not exists analytics_post_metrics (
  id text primary key,
  workspace_id text not null,
  project_id text,
  social_post_id text not null references social_posts(id) on delete cascade,
  social_post_target_id text not null references social_post_targets(id) on delete cascade,
  social_account_id text not null references social_accounts(id) on delete cascade,
  provider text not null,
  external_post_id text,
  metric_date timestamptz not null,
  views bigint,
  impressions bigint,
  likes bigint,
  comments bigint,
  shares bigint,
  engagement_rate double precision,
  dimensions jsonb not null default '{}'::jsonb,
  source text not null default 'provider',
  collected_at timestamptz not null default now(),
  provider_updated_at timestamptz,
  constraint ck_analytics_post_nonnegative check (
    (views is null or views >= 0) and
    (impressions is null or impressions >= 0) and
    (likes is null or likes >= 0) and
    (comments is null or comments >= 0) and
    (shares is null or shares >= 0) and
    (engagement_rate is null or engagement_rate >= 0)
  ),
  constraint uq_analytics_post_metric_bucket
    unique (workspace_id, social_post_target_id, metric_date)
);
create index if not exists ix_analytics_post_metrics_workspace_date
  on analytics_post_metrics(workspace_id, metric_date desc);
create index if not exists ix_analytics_post_metrics_project_date
  on analytics_post_metrics(workspace_id, project_id, metric_date desc);
create index if not exists ix_analytics_post_metrics_provider_date
  on analytics_post_metrics(workspace_id, provider, metric_date desc);

create table if not exists analytics_platform_metrics (
  id text primary key,
  workspace_id text not null,
  project_id text,
  social_account_id text not null references social_accounts(id) on delete cascade,
  provider text not null,
  metric_date timestamptz not null,
  posts_count integer not null default 0 check (posts_count >= 0),
  views bigint,
  impressions bigint,
  likes bigint,
  comments bigint,
  shares bigint,
  engagement_rate double precision,
  dimensions jsonb not null default '{}'::jsonb,
  source text not null default 'provider',
  collected_at timestamptz not null default now(),
  constraint uq_analytics_platform_metric_bucket
    unique (workspace_id, provider, social_account_id, metric_date)
);
create index if not exists ix_analytics_platform_metrics_workspace_date
  on analytics_platform_metrics(workspace_id, metric_date desc);

do $$
declare table_name text;
begin
  foreach table_name in array array[
    'analytics_sync_runs',
    'analytics_metric_snapshots',
    'analytics_post_metrics',
    'analytics_platform_metrics'
  ] 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 analytics_workspace_isolation on %I', table_name);
    execute format(
      'create policy analytics_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;