CREATE TYPE share_channel AS ENUM (
  'whatsapp', 'instagram', 'facebook', 'twitter', 'telegram',
  'copy', 'email', 'sms', 'other'
);

CREATE TABLE share_links (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug            TEXT NOT NULL UNIQUE,
  target_url      TEXT NOT NULL,
  deal_id         UUID REFERENCES deals(id) ON DELETE SET NULL,
  owner_user_id   UUID REFERENCES users(id) ON DELETE SET NULL,
  channel         share_channel NOT NULL DEFAULT 'other',
  campaign_name   TEXT,
  utm_source      TEXT NOT NULL,
  utm_medium      TEXT NOT NULL DEFAULT 'social',
  utm_campaign    TEXT NOT NULL,
  is_active       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at      TIMESTAMPTZ
);

CREATE INDEX share_links_deal_idx     ON share_links(deal_id);
CREATE INDEX share_links_owner_idx    ON share_links(owner_user_id);
CREATE INDEX share_links_created_idx  ON share_links(created_at DESC);
CREATE INDEX share_links_channel_idx  ON share_links(channel);

CREATE TABLE share_link_stats_daily (
  link_id             UUID NOT NULL REFERENCES share_links(id) ON DELETE CASCADE,
  day                 DATE NOT NULL,
  clicks              INTEGER NOT NULL DEFAULT 0,
  clicks_suspicious   INTEGER NOT NULL DEFAULT 0,
  deal_id             UUID,
  channel             share_channel,
  PRIMARY KEY (link_id, day)
);

CREATE INDEX share_stats_deal_day_idx ON share_link_stats_daily(deal_id, day);
CREATE INDEX share_stats_day_idx      ON share_link_stats_daily(day DESC);
