-- 184_user_events.sql
-- Foundation schema for Features 2 & 3: unified user-event tracking.
--
-- WHY:
--   trade-in had no durable event log for login/IP audit or behavior
--   analytics. A near-identical design already existed once — `audit_log`
--   (event_type, ip_address inet, user_agent, estimate_id uuid, payload
--   jsonb, created_at) was defined in 002_core_tables.sql and DROPPED in
--   095_dead_schema.sql as unused dead schema at the time. Product now
--   needs exactly that shape again, so this migration resurrects the
--   design under a new name, `user_events`, as the ONE unified
--   append-only table serving:
--     - login-audit (who logged in, from what IP/UA, when)
--     - search-audit (estimate requests, listing lookups)
--     - behavior-analytics (pdf_download, listing_click, page_view,
--       drawer_open, and future event_type values)
--
-- WHAT:
--   `user_events` — append-only, admin-read-only. No 152-ФЗ consent
--   gating and no mandatory retention policy for MVP (product-owner
--   decision): we log IP/UA/path/method/payload unconditionally for every
--   tracked event. `estimate_id` is a plain uuid column with NO FK
--   constraint on purpose — this keeps the log decoupled/append-only so
--   estimate deletion (or any future estimate lifecycle change) never
--   blocks or cascades into event rows.
--
-- IDEMPOTENCY / SAFETY:
--   CREATE TABLE IF NOT EXISTS + CREATE INDEX IF NOT EXISTS throughout —
--   safe re-run. Purely additive: no existing table/view/column is
--   touched.
--
-- Dependencies: none (new standalone table). Auto-applied on deploy via
-- _schema_migrations tracking (tradein-mvp/backend/data/sql convention).

BEGIN;

CREATE TABLE IF NOT EXISTS user_events (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type   text NOT NULL,
    username     text NOT NULL,
    ip_address   inet,
    user_agent   text,
    path         text,
    method       text,
    estimate_id  uuid,
    payload      jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at   timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE user_events IS 'Unified append-only event log (login/IP audit + behavior analytics), admin-read-only. Resurrects the design of audit_log (002_core_tables.sql, dropped in 095_dead_schema.sql).';
COMMENT ON COLUMN user_events.event_type IS 'e.g. login / estimate_request / pdf_download / listing_click / page_view / drawer_open.';
COMMENT ON COLUMN user_events.username IS 'X-Authenticated-User value at the time of the event.';
COMMENT ON COLUMN user_events.estimate_id IS 'No FK constraint by design — keeps the log decoupled/append-only from trade_in_estimates lifecycle.';

CREATE INDEX IF NOT EXISTS user_events_username_created_at_idx ON user_events (username, created_at DESC);
CREATE INDEX IF NOT EXISTS user_events_event_type_created_at_idx ON user_events (event_type, created_at DESC);
CREATE INDEX IF NOT EXISTS user_events_ip_address_idx ON user_events (ip_address);
CREATE INDEX IF NOT EXISTS user_events_created_at_idx ON user_events (created_at DESC);

COMMIT;