%% ERD for Haji Bot (from PRD §6.x, Product Design §8)
%% Mermaid ER Diagram + normalization checklist

erDiagram
  USERS ||--o{ SUBSCRIPTIONS : has
  USERS ||--o{ PAYMENTS : has
  USERS ||--o{ CHAT_THREADS : has
  USERS ||--o{ CHATS : has
  USERS ||--o{ EVENTS : has
  USERS ||--o{ SAVED_ADS : has
  USERS ||--o{ TICKETS : has
  USERS ||--o{ USER_ADDONS : has
  USERS ||--o{ WALLET_LEDGER : has
  USERS ||--o{ TRIALS : has
  USERS ||--o{ EXPERIMENT_ASSIGNMENTS : has
  USERS ||--o{ PUSH_LOGS : receives
  USERS ||--o{ REFERRAL_LINKS : owns
  USERS ||--o{ CANCELLATIONS : files

  VECTOR_STORES ||--o{ VECTOR_FILES : contains
  CITIES ||--o{ CITY_ALIASES : maps
  CITIES ||--o{ USERS : used_by
  CITIES ||--o{ OPPORTUNITIES : used_by

  USERS {
    bigint id PK
    string tg_id UK
    enum role
    bigint city_id FK
    string plan
    enum notif_level
    datetime created_at
    datetime updated_at
  }

  SUBSCRIPTIONS {
    bigint id PK
    bigint user_id FK
    string plan
    datetime starts_at
    datetime ends_at
    enum status
    datetime created_at
    datetime updated_at
  }

  PAYMENTS {
    bigint id PK
    bigint user_id FK
    bigint plan_id FK
    string gateway
    string ref
    int amount
    enum status
    json payload_json
    datetime created_at
  }

  MENU_ITEMS {
    bigint id PK
    string key
    string title
    string emoji
    bool enabled
    int ord
    json visible_roles
    datetime updated_at
  }

  PROMPT_CONFIGS {
    bigint id PK
    string module
    string model
    float temperature
    float top_p
    bool enable_file_search
    datetime updated_at
  }

  VECTOR_STORES {
    bigint id PK
    string name
    string openai_vector_store_id UK
    bool is_active
    datetime created_at
  }

  VECTOR_FILES {
    bigint id PK
    bigint vector_store_id FK
    string openai_file_id UK
    string tag
    string status
    datetime created_at
  }

  ADS_RAW {
    bigint id PK
    enum source
    json payload_json
    datetime fetched_at
  }

  OPPORTUNITIES {
    bigint id PK
    enum source
    string code
    string brand
    string model
    string trim
    int year
    int km
    bigint city_id FK
    int price
    string body_status
    string link
    string contact_ref
    string dedup_key UK
    float score
    json reasons_json
    enum status
    datetime ts_posted
    datetime pushed_at
    datetime created_at
    datetime updated_at
  }

  FEATURED_ADS {
    bigint id PK
    string title
    text description
    int price
    string city
    string link
    json target_roles
    json target_cities
    datetime scheduled_at
    datetime pushed_at
    string status
  }

  SCRAPE_PROFILES {
    bigint id PK
    string name
    string url
    string class_name
    string element_id
    string city
    string keywords
    bool enabled
  }

  LEGAL_FILES {
    bigint id PK
    enum type
    string file_path
    datetime updated_at
  }

  CHECKLISTS {
    bigint id PK
    string name
    enum type
    json questions_json
    datetime updated_at
  }

  CITY_REPORTS {
    bigint id PK
    string city
    int week_no
    json summary_json
    datetime created_at
  }

  CHAT_THREADS {
    bigint id PK
    bigint user_id FK
    string module
    string last_response_id
    datetime expires_at
    datetime created_at
    datetime updated_at
  }

  CHATS {
    bigint id PK
    bigint user_id FK
    string module
    enum role
    text text
    string response_id
    int token_in
    int token_out
    int latency_ms
    datetime created_at
    datetime updated_at
  }

  EVENTS {
    bigint id PK
    bigint user_id FK
    string type
    json payload_json
    datetime created_at
  }

  %% Growth tables (Phase 2A)
  PLAN_ENTITLEMENTS {
    bigint id PK
    bigint plan_id FK
    string key
    int int_value
    json json_value
  }
  USER_QUOTAS {
    bigint id PK
    bigint user_id FK
    string key
    string period
    int used
    datetime period_start
    datetime period_end
  }
  ADDONS {
    bigint id PK
    string key UK
    string name
    json entitlements_json
  }
  USER_ADDONS {
    bigint id PK
    bigint user_id FK
    bigint addon_id FK
    datetime starts_at
    datetime ends_at
    string status
  }
  WALLET_LEDGER {
    bigint id PK
    bigint user_id FK
    string type
    int amount_irr
    string reason
  }
  TRIALS {
    bigint id PK
    bigint user_id FK
    string status
    datetime started_at
    datetime ends_at
    int penalty_points
  }
  EXPERIMENTS {
    bigint id PK
    string key UK
    json variant_keys
  }
  EXPERIMENT_ASSIGNMENTS {
    bigint id PK
    bigint user_id FK
    bigint experiment_id FK
    string variant_key
  }
  PUSH_CAMPAIGNS {
    bigint id PK
    string key UK
    string name
    json schedule_json
    string status
  }
  PUSH_LOGS {
    bigint id PK
    bigint user_id FK
    bigint campaign_id FK
    datetime sent_at
    datetime delivered_at
    datetime clicked_cta_at
  }
  REFERRAL_LINKS {
    bigint id PK
    bigint user_id FK
    string code UK
  }
  REFERRAL_EVENTS {
    bigint id PK
    bigint link_id FK
    bigint user_id FK
    string type
  }
  CANCELLATIONS {
    bigint id PK
    bigint user_id FK
    string status
    string reason
  }

  SAVED_ADS {
    bigint id PK
    bigint user_id FK
    enum source  // opportunity|external
    bigint opportunity_id FK  // nullable when external
    enum external_source  // bama|divar (nullable when opportunity)
    string external_code   // nullable when opportunity
    string link
    datetime created_at
    %% Uniqueness:
    %% UK(user_id, opportunity_id) when source=opportunity
    %% UK(user_id, external_source, external_code) when source=external
  }

  TICKETS {
    bigint id PK
    bigint user_id FK
    string subject
    text body
    enum status  // open|closed
    datetime admin_replied_at
    text admin_last_reply_text
    datetime created_at
    datetime updated_at
  }

  PLANS {
    bigint id PK
    string key UK
    string name
    int amount_irr
    int duration_days
    bool is_active
    datetime created_at
    datetime updated_at
  }

  CITIES {
    bigint id PK
    string name UK
    string province
  }

  CITY_ALIASES {
    bigint id PK
    bigint city_id FK
    string alias UK
  }


%% Normalization Checklist (3NF)
%% - No repeating groups: arrays stored as json where appropriate (reasons_json, questions_json)
%% - Non-key attributes depend on key, the whole key, and nothing but the key
%% - Reference integrity: all FK relations enumerated above
%% - Suggested indexes:
%%   * USERS.tg_id (UK), OPPORTUNITIES.dedup_key (UK)
%%   * OPPORTUNITIES (city, brand, model, year, price)
%%   * ADS_RAW (source, fetched_at)
%%   * CHATS (user_id, created_at), CHAT_THREADS (user_id, module)
%% - Archival: OPPORTUNITIES older than 72h moved to archived status per PRD
