-- ======================
-- SOCIAL: Post
-- ======================
CREATE TABLE unified_social_post (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  updated_at           timestamptz,
  profile_id           text,
  parent_post_id       text,
  title                text,
  body                 text,
  status               text,
  type                 text,
  media                jsonb,
  url                  text,
  language             text,
  scheduled_at         timestamptz,
  call_to_action       jsonb,
  metrics              jsonb,
  raw                  jsonb
);
-- ======================
-- SOCIAL: Profile
-- ======================
CREATE TABLE unified_social_profile (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  updated_at           timestamptz,
  name                 text,
  username             text,
  parent_id            text,
  description          text,
  url                  text,
  website_url          text,
  avatar_url           text,
  phone                text,
  location             text,
  address              jsonb,
  category             text,
  is_verified          boolean,
  is_private           boolean,
  metrics              jsonb,
  raw                  jsonb
);
-- ======================
-- SOCIAL: Review
-- ======================
CREATE TABLE unified_social_review (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  updated_at           timestamptz,
  profile_id           text,
  rating               numeric,
  title                text,
  content              text,
  author_name          text,
  author_avatar_url    text,
  author_location      text,
  reply                jsonb,
  url                  text,
  raw                  jsonb
);
-- ======================
-- SOCIAL: Insight
-- ======================
CREATE TABLE unified_social_insight (
  unified_connection_id  text NOT NULL,
  unified_webhook_id     text NOT NULL,
  unified_external_xref  text,
  unified_integration_type text NOT NULL,
  unified_raw_id         text,
  unified_id             text NOT NULL,
  id                   text,
  created_at           timestamptz,
  profile_id           text,
  post_id              text,
  date                 timestamptz,
  period               text,
  metric               text,
  value                numeric,
  raw                  jsonb
);
