-- =====================================================================
-- TARLA.AZ — PostgreSQL sxemi
-- Bütün balans, auksion və oyun əməliyyatları burada, tranzaksiya daxilində
-- və sətir kilidləri ilə icra olunur. Node.js yalnız bu funksiyaları çağırır.
-- Məbləğlər: AZN = qəpik (1 ₼ = 100), RUBY = tam rubin.
-- =====================================================================

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ---------- İstifadəçilər ----------
CREATE TABLE IF NOT EXISTS users (
  id              BIGSERIAL PRIMARY KEY,
  full_name       TEXT NOT NULL,
  username        TEXT NOT NULL,
  phone           TEXT NOT NULL UNIQUE,
  password_hash   TEXT NOT NULL,
  avatar_url      TEXT,
  bio             TEXT,
  role            TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user','moderator','admin')),
  xp              BIGINT NOT NULL DEFAULT 0,
  level           INT NOT NULL DEFAULT 1,
  farm_level      INT NOT NULL DEFAULT 1,
  harvest_count   BIGINT NOT NULL DEFAULT 0,
  farm_earned     BIGINT NOT NULL DEFAULT 0,
  daily_streak    INT NOT NULL DEFAULT 0,
  last_bonus_date DATE,
  is_blocked      BOOLEAN NOT NULL DEFAULT FALSE,
  block_reason    TEXT,
  token_version   INT NOT NULL DEFAULT 0,
  last_login_at   TIMESTAMPTZ,
  last_seen_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS users_username_lower ON users (lower(username));

CREATE TABLE IF NOT EXISTS sms_codes (
  id          BIGSERIAL PRIMARY KEY,
  phone       TEXT NOT NULL,
  purpose     TEXT NOT NULL CHECK (purpose IN ('register','reset')),
  code_hash   TEXT NOT NULL,
  attempts    INT NOT NULL DEFAULT 0,
  used        BOOLEAN NOT NULL DEFAULT FALSE,
  expires_at  TIMESTAMPTZ NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS sms_codes_phone ON sms_codes (phone, purpose, created_at DESC);

-- ---------- Parametrlər ----------
CREATE TABLE IF NOT EXISTS settings (
  key        TEXT PRIMARY KEY,
  value      JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- Cüzdanlar və dəyişdirilməz ledger ----------
CREATE TABLE IF NOT EXISTS wallets (
  user_id   BIGINT NOT NULL REFERENCES users(id),
  currency  TEXT NOT NULL CHECK (currency IN ('AZN','RUBY')),
  balance   BIGINT NOT NULL DEFAULT 0,
  locked    BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, currency),
  CONSTRAINT wallet_balance_nonneg CHECK (balance >= 0),
  CONSTRAINT wallet_locked_range CHECK (locked >= 0 AND locked <= balance)
);

CREATE TABLE IF NOT EXISTS ledger (
  id            BIGSERIAL PRIMARY KEY,
  user_id       BIGINT NOT NULL REFERENCES users(id),
  currency      TEXT NOT NULL CHECK (currency IN ('AZN','RUBY')),
  delta_balance BIGINT NOT NULL,
  delta_locked  BIGINT NOT NULL DEFAULT 0,
  balance_after BIGINT NOT NULL,
  locked_after  BIGINT NOT NULL,
  kind          TEXT NOT NULL,
  ref_type      TEXT,
  ref_id        BIGINT,
  meta          JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ledger_user ON ledger (user_id, currency, id DESC);
CREATE INDEX IF NOT EXISTS ledger_kind ON ledger (kind, created_at);

CREATE OR REPLACE FUNCTION ledger_immutable() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'Ledger qeydləri dəyişdirilə və silinə bilməz';
END $$;
DROP TRIGGER IF EXISTS ledger_no_update ON ledger;
CREATE TRIGGER ledger_no_update BEFORE UPDATE OR DELETE ON ledger
  FOR EACH ROW EXECUTE FUNCTION ledger_immutable();
DROP TRIGGER IF EXISTS ledger_no_truncate ON ledger;
CREATE TRIGGER ledger_no_truncate BEFORE TRUNCATE ON ledger
  FOR EACH STATEMENT EXECUTE FUNCTION ledger_immutable();

CREATE SEQUENCE IF NOT EXISTS transfer_seq;

-- ---------- Ödəniş sorğuları (manat) ----------
CREATE TABLE IF NOT EXISTS payment_requests (
  id           BIGSERIAL PRIMARY KEY,
  user_id      BIGINT NOT NULL REFERENCES users(id),
  type         TEXT NOT NULL CHECK (type IN ('deposit','withdraw')),
  amount       BIGINT NOT NULL CHECK (amount > 0),
  method       TEXT NOT NULL,
  details      TEXT,
  status       TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected')),
  admin_id     BIGINT REFERENCES users(id),
  admin_note   TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS payment_requests_status ON payment_requests (status, created_at DESC);

-- ---------- Rubin paketləri ----------
CREATE TABLE IF NOT EXISTS ruby_packages (
  id         BIGSERIAL PRIMARY KEY,
  rubies     BIGINT NOT NULL CHECK (rubies > 0),
  bonus      BIGINT NOT NULL DEFAULT 0 CHECK (bonus >= 0),
  price_azn  BIGINT NOT NULL CHECK (price_azn > 0),
  is_active  BOOLEAN NOT NULL DEFAULT TRUE,
  sort       INT NOT NULL DEFAULT 0
);

-- ---------- Auksion ----------
CREATE TABLE IF NOT EXISTS auctions (
  id                BIGSERIAL PRIMARY KEY,
  seller_id         BIGINT NOT NULL REFERENCES users(id),
  title             TEXT NOT NULL,
  description       TEXT NOT NULL DEFAULT '',
  image_url         TEXT,
  start_price       BIGINT NOT NULL CHECK (start_price > 0),
  min_increment     BIGINT NOT NULL CHECK (min_increment > 0),
  current_price     BIGINT,
  current_bidder_id BIGINT REFERENCES users(id),
  bid_count         INT NOT NULL DEFAULT 0,
  ends_at           TIMESTAMPTZ NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','ended','cancelled')),
  winner_id         BIGINT REFERENCES users(id),
  final_price       BIGINT,
  commission        BIGINT,
  cancel_reason     TEXT,
  is_hidden         BOOLEAN NOT NULL DEFAULT FALSE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  ended_at          TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS auctions_active ON auctions (status, ends_at);

CREATE TABLE IF NOT EXISTS bids (
  id         BIGSERIAL PRIMARY KEY,
  auction_id BIGINT NOT NULL REFERENCES auctions(id),
  user_id    BIGINT NOT NULL REFERENCES users(id),
  amount     BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS bids_auction ON bids (auction_id, id DESC);
CREATE INDEX IF NOT EXISTS bids_user ON bids (user_id, id DESC);

-- ---------- Ferma ----------
CREATE TABLE IF NOT EXISTS plots (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  crop_key    TEXT,
  planted_at  TIMESTAMPTZ,
  ready_at    TIMESTAMPTZ,
  fertilized  BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS plots_user ON plots (user_id, id);

CREATE TABLE IF NOT EXISTS user_animals (
  id                BIGSERIAL PRIMARY KEY,
  user_id           BIGINT NOT NULL REFERENCES users(id),
  animal_key        TEXT NOT NULL,
  last_collected_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS user_animals_user ON user_animals (user_id, id);

CREATE TABLE IF NOT EXISTS user_items (
  user_id   BIGINT NOT NULL REFERENCES users(id),
  item_key  TEXT NOT NULL,
  qty       INT NOT NULL DEFAULT 0 CHECK (qty >= 0),
  acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, item_key)
);

CREATE TABLE IF NOT EXISTS quest_progress (
  user_id   BIGINT NOT NULL REFERENCES users(id),
  day       DATE NOT NULL,
  quest_key TEXT NOT NULL,
  progress  INT NOT NULL DEFAULT 0,
  claimed   BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (user_id, day, quest_key)
);

-- ---------- Sosial ----------
CREATE TABLE IF NOT EXISTS friendships (
  requester_id BIGINT NOT NULL REFERENCES users(id),
  addressee_id BIGINT NOT NULL REFERENCES users(id),
  status       TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','accepted')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (requester_id, addressee_id),
  CHECK (requester_id <> addressee_id)
);

CREATE TABLE IF NOT EXISTS user_blocks (
  blocker_id BIGINT NOT NULL REFERENCES users(id),
  blocked_id BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (blocker_id, blocked_id)
);

CREATE TABLE IF NOT EXISTS messages (
  id          BIGSERIAL PRIMARY KEY,
  sender_id   BIGINT NOT NULL REFERENCES users(id),
  receiver_id BIGINT NOT NULL REFERENCES users(id),
  body        TEXT NOT NULL CHECK (length(body) BETWEEN 1 AND 2000),
  read_at     TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS messages_pair ON messages (LEAST(sender_id,receiver_id), GREATEST(sender_id,receiver_id), id DESC);
CREATE INDEX IF NOT EXISTS messages_receiver ON messages (receiver_id, read_at);

CREATE TABLE IF NOT EXISTS notifications (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  type       TEXT NOT NULL,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL DEFAULT '',
  link       TEXT,
  read_at    TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS notifications_user ON notifications (user_id, id DESC);

CREATE TABLE IF NOT EXISTS news (
  id         BIGSERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL,
  icon       TEXT NOT NULL DEFAULT '📰',
  author_id  BIGINT REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS admin_log (
  id         BIGSERIAL PRIMARY KEY,
  admin_id   BIGINT NOT NULL REFERENCES users(id),
  action     TEXT NOT NULL,
  target     TEXT,
  meta       JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =====================================================================
-- Köməkçi funksiyalar
-- =====================================================================

CREATE OR REPLACE FUNCTION fail(msg TEXT) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION USING ERRCODE = 'P0001', MESSAGE = msg;
END $$;

CREATE OR REPLACE FUNCTION baku_today() RETURNS DATE LANGUAGE sql STABLE AS $$
  SELECT (now() AT TIME ZONE 'Asia/Baku')::date
$$;

CREATE OR REPLACE FUNCTION game_cfg() RETURNS JSONB LANGUAGE sql STABLE AS $$
  SELECT value FROM settings WHERE key = 'game'
$$;

-- Real-time hadisə: server.js LISTEN tarla_events edir və socket-ə ötürür.
CREATE OR REPLACE FUNCTION emit_event(p_type TEXT, p_user BIGINT, p_data JSONB) RETURNS void
LANGUAGE sql AS $$
  SELECT pg_notify('tarla_events', jsonb_build_object('t', p_type, 'u', p_user, 'd', p_data)::text)
$$;

CREATE OR REPLACE FUNCTION notify_user(p_user BIGINT, p_type TEXT, p_title TEXT, p_body TEXT, p_link TEXT)
RETURNS BIGINT LANGUAGE plpgsql AS $$
DECLARE v_id BIGINT;
BEGIN
  INSERT INTO notifications (user_id, type, title, body, link)
  VALUES (p_user, p_type, p_title, coalesce(p_body,''), p_link) RETURNING id INTO v_id;
  PERFORM emit_event('notification', p_user, jsonb_build_object(
    'id', v_id, 'type', p_type, 'title', p_title, 'body', coalesce(p_body,''), 'link', p_link,
    'created_at', now()));
  RETURN v_id;
END $$;

-- Cüzdan əməliyyatının YEGANƏ yolu. Sətri kilidləyir, ledger yazır.
CREATE OR REPLACE FUNCTION wallet_apply(
  p_user BIGINT, p_currency TEXT, p_dbal BIGINT, p_dlock BIGINT,
  p_kind TEXT, p_ref_type TEXT DEFAULT NULL, p_ref_id BIGINT DEFAULT NULL, p_meta JSONB DEFAULT '{}'::jsonb
) RETURNS wallets LANGUAGE plpgsql AS $$
DECLARE w wallets;
BEGIN
  IF p_dbal = 0 AND p_dlock = 0 THEN
    SELECT * INTO w FROM wallets WHERE user_id = p_user AND currency = p_currency;
    RETURN w;
  END IF;
  SELECT * INTO w FROM wallets WHERE user_id = p_user AND currency = p_currency FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Cüzdan tapılmadı'); END IF;

  IF w.balance + p_dbal < 0 OR w.locked + p_dlock < 0 THEN
    PERFORM fail(CASE WHEN p_currency = 'RUBY' THEN 'Rubin balansı kifayət etmir' ELSE 'Manat balansı kifayət etmir' END);
  END IF;
  IF w.locked + p_dlock > w.balance + p_dbal THEN
    PERFORM fail(CASE WHEN p_currency = 'RUBY' THEN 'İstifadə oluna bilən rubin kifayət etmir' ELSE 'İstifadə oluna bilən manat kifayət etmir' END);
  END IF;

  UPDATE wallets SET balance = balance + p_dbal, locked = locked + p_dlock
   WHERE user_id = p_user AND currency = p_currency RETURNING * INTO w;

  INSERT INTO ledger (user_id, currency, delta_balance, delta_locked, balance_after, locked_after, kind, ref_type, ref_id, meta)
  VALUES (p_user, p_currency, p_dbal, p_dlock, w.balance, w.locked, p_kind, p_ref_type, p_ref_id, coalesce(p_meta,'{}'::jsonb));

  PERFORM emit_event('wallet', p_user, jsonb_build_object('currency', p_currency, 'balance', w.balance, 'locked', w.locked));
  RETURN w;
END $$;

-- Bir neçə cüzdanı deterministik sıra ilə kilidləyir (deadlock-un qarşısını alır)
CREATE OR REPLACE FUNCTION lock_wallets(p_users BIGINT[], p_currency TEXT) RETURNS void LANGUAGE sql AS $$
  SELECT 1 FROM wallets WHERE user_id = ANY(p_users) AND currency = p_currency
  ORDER BY user_id FOR UPDATE
$$;

CREATE OR REPLACE FUNCTION level_for_xp(p_xp BIGINT) RETURNS INT LANGUAGE plpgsql STABLE AS $$
DECLARE
  base INT := coalesce((game_cfg()->'xp'->>'per_level_base')::int, 100);
  lvl INT := 1; need BIGINT := 0;
BEGIN
  LOOP
    need := need + base * lvl;
    EXIT WHEN p_xp < need OR lvl >= 999;
    lvl := lvl + 1;
  END LOOP;
  RETURN lvl;
END $$;

CREATE OR REPLACE FUNCTION add_xp(p_user BIGINT, p_xp INT) RETURNS INT LANGUAGE plpgsql AS $$
DECLARE u users; new_level INT;
BEGIN
  IF p_xp <= 0 THEN SELECT level INTO new_level FROM users WHERE id = p_user; RETURN new_level; END IF;
  UPDATE users SET xp = xp + p_xp WHERE id = p_user RETURNING * INTO u;
  new_level := level_for_xp(u.xp);
  IF new_level > u.level THEN
    UPDATE users SET level = new_level WHERE id = p_user;
    PERFORM notify_user(p_user, 'level', 'Yeni səviyyə! 🎉', 'Təbriklər, ' || new_level || '-ci səviyyəyə çatdınız.', '/profil');
  END IF;
  PERFORM emit_event('xp', p_user, jsonb_build_object('xp', u.xp, 'level', greatest(new_level, u.level)));
  RETURN new_level;
END $$;

CREATE OR REPLACE FUNCTION quest_bump(p_user BIGINT, p_action TEXT, p_n INT DEFAULT 1) RETURNS void LANGUAGE plpgsql AS $$
DECLARE q JSONB;
BEGIN
  FOR q IN SELECT * FROM jsonb_array_elements(coalesce(game_cfg()->'quests','[]'::jsonb)) LOOP
    IF q->>'action' = p_action THEN
      INSERT INTO quest_progress (user_id, day, quest_key, progress)
      VALUES (p_user, baku_today(), q->>'key', p_n)
      ON CONFLICT (user_id, day, quest_key)
      DO UPDATE SET progress = quest_progress.progress + p_n;
    END IF;
  END LOOP;
END $$;

-- Yeni istifadəçi: cüzdanlar + başlanğıc torpaqlar
CREATE OR REPLACE FUNCTION init_user(p_user BIGINT) RETURNS void LANGUAGE plpgsql AS $$
DECLARE cfg JSONB := game_cfg(); n INT; gift BIGINT;
BEGIN
  INSERT INTO wallets (user_id, currency) VALUES (p_user,'AZN'),(p_user,'RUBY') ON CONFLICT DO NOTHING;
  n := coalesce((cfg->'plot'->>'start_plots')::int, 4);
  INSERT INTO plots (user_id) SELECT p_user FROM generate_series(1, n);
  gift := coalesce((cfg->>'welcome_rubies')::bigint, 0);
  IF gift > 0 THEN
    PERFORM wallet_apply(p_user, 'RUBY', gift, 0, 'welcome_gift');
  END IF;
  PERFORM notify_user(p_user, 'system', 'TARLA.AZ-a xoş gəlmisiniz! 🌾',
    'Fermanızı qurun, auksionlarda iştirak edin və liderlər cədvəlinə yüksəlin.', '/ferma');
END $$;

-- =====================================================================
-- Rubin mağazası və köçürmələr
-- =====================================================================

CREATE OR REPLACE FUNCTION buy_ruby_package(p_user BIGINT, p_package BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE p ruby_packages; total BIGINT;
BEGIN
  SELECT * INTO p FROM ruby_packages WHERE id = p_package AND is_active;
  IF NOT FOUND THEN PERFORM fail('Paket tapılmadı'); END IF;
  total := p.rubies + p.bonus;
  -- Eyni istifadəçinin iki cüzdanı: sabit sıra (AZN, RUBY)
  PERFORM 1 FROM wallets WHERE user_id = p_user ORDER BY currency FOR UPDATE;
  PERFORM wallet_apply(p_user, 'AZN', -p.price_azn, 0, 'ruby_purchase', 'ruby_package', p.id,
                       jsonb_build_object('rubies', total));
  PERFORM wallet_apply(p_user, 'RUBY', total, 0, 'ruby_purchase', 'ruby_package', p.id,
                       jsonb_build_object('price_azn', p.price_azn));
  PERFORM notify_user(p_user, 'ruby', 'Rubin alındı 💎', total || ' rubin balansınıza əlavə olundu.', '/cuzdan');
  RETURN jsonb_build_object('rubies', total, 'price_azn', p.price_azn);
END $$;

CREATE OR REPLACE FUNCTION transfer_ruby(p_from BIGINT, p_to BIGINT, p_amount BIGINT, p_note TEXT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE fee_pct NUMERIC := coalesce((game_cfg()->'transfer'->>'fee_percent')::numeric, 0);
        min_amt BIGINT := coalesce((game_cfg()->'transfer'->>'min')::bigint, 1);
        fee BIGINT; from_name TEXT; to_name TEXT; tid BIGINT;
BEGIN
  IF p_from = p_to THEN PERFORM fail('Özünüzə rubin göndərə bilməzsiniz'); END IF;
  IF p_amount < min_amt THEN PERFORM fail('Minimum göndəriş: ' || min_amt || ' rubin'); END IF;
  SELECT username INTO to_name FROM users WHERE id = p_to AND NOT is_blocked;
  IF NOT FOUND THEN PERFORM fail('Alıcı tapılmadı'); END IF;
  IF EXISTS (SELECT 1 FROM user_blocks WHERE blocker_id = p_to AND blocked_id = p_from) THEN
    PERFORM fail('Bu istifadəçi sizi bloklayıb');
  END IF;
  SELECT username INTO from_name FROM users WHERE id = p_from;
  fee := floor(p_amount * fee_pct / 100);
  tid := nextval('transfer_seq');
  PERFORM lock_wallets(ARRAY[p_from, p_to], 'RUBY');
  PERFORM wallet_apply(p_from, 'RUBY', -(p_amount + fee), 0, 'transfer_out', 'transfer', tid,
          jsonb_build_object('to', to_name, 'fee', fee, 'note', p_note));
  PERFORM wallet_apply(p_to, 'RUBY', p_amount, 0, 'transfer_in', 'transfer', tid,
          jsonb_build_object('from', from_name, 'note', p_note));
  PERFORM notify_user(p_to, 'ruby', 'Rubin gəldi 💎', from_name || ' sizə ' || p_amount || ' rubin göndərdi.', '/cuzdan');
  RETURN jsonb_build_object('amount', p_amount, 'fee', fee);
END $$;

-- =====================================================================
-- Manat ödənişləri
-- =====================================================================

CREATE OR REPLACE FUNCTION request_withdraw(p_user BIGINT, p_amount BIGINT, p_method TEXT, p_details TEXT) RETURNS BIGINT
LANGUAGE plpgsql AS $$
DECLARE min_amt BIGINT := coalesce((game_cfg()->'payments'->>'min_withdraw')::bigint, 500); rid BIGINT;
BEGIN
  IF p_amount < min_amt THEN PERFORM fail('Minimum çıxarış: ' || to_char(min_amt/100.0, 'FM999990.00') || ' ₼'); END IF;
  INSERT INTO payment_requests (user_id, type, amount, method, details)
  VALUES (p_user, 'withdraw', p_amount, p_method, p_details) RETURNING id INTO rid;
  PERFORM wallet_apply(p_user, 'AZN', 0, p_amount, 'withdraw_hold', 'payment', rid);
  RETURN rid;
END $$;

CREATE OR REPLACE FUNCTION process_payment(p_request BIGINT, p_admin BIGINT, p_approve BOOLEAN, p_note TEXT) RETURNS JSONB
LANGUAGE plpgsql AS $$
DECLARE r payment_requests; amt TEXT;
BEGIN
  SELECT * INTO r FROM payment_requests WHERE id = p_request FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Sorğu tapılmadı'); END IF;
  IF r.status <> 'pending' THEN PERFORM fail('Bu sorğu artıq emal olunub'); END IF;
  amt := to_char(r.amount/100.0, 'FM999999990.00') || ' ₼';

  IF r.type = 'deposit' THEN
    IF p_approve THEN
      PERFORM wallet_apply(r.user_id, 'AZN', r.amount, 0, 'deposit', 'payment', r.id, jsonb_build_object('method', r.method));
      PERFORM notify_user(r.user_id, 'payment', 'Depozit təsdiqləndi ✅', amt || ' manat balansınıza əlavə olundu.', '/cuzdan');
    ELSE
      PERFORM notify_user(r.user_id, 'payment', 'Depozit rədd edildi', amt || ' depozit sorğusu rədd edildi. ' || coalesce(p_note,''), '/cuzdan');
    END IF;
  ELSE
    IF p_approve THEN
      PERFORM wallet_apply(r.user_id, 'AZN', -r.amount, -r.amount, 'withdraw', 'payment', r.id, jsonb_build_object('method', r.method));
      PERFORM notify_user(r.user_id, 'payment', 'Çıxarış təsdiqləndi ✅', amt || ' hesabınıza köçürüldü.', '/cuzdan');
    ELSE
      PERFORM wallet_apply(r.user_id, 'AZN', 0, -r.amount, 'withdraw_release', 'payment', r.id);
      PERFORM notify_user(r.user_id, 'payment', 'Çıxarış rədd edildi', amt || ' balansınıza qaytarıldı. ' || coalesce(p_note,''), '/cuzdan');
    END IF;
  END IF;

  UPDATE payment_requests SET status = CASE WHEN p_approve THEN 'approved' ELSE 'rejected' END,
         admin_id = p_admin, admin_note = p_note, processed_at = now()
   WHERE id = r.id;
  INSERT INTO admin_log (admin_id, action, target, meta)
  VALUES (p_admin, CASE WHEN p_approve THEN 'payment_approve' ELSE 'payment_reject' END, 'payment:' || r.id,
          jsonb_build_object('amount', r.amount, 'type', r.type));
  RETURN jsonb_build_object('id', r.id, 'approved', p_approve);
END $$;

CREATE OR REPLACE FUNCTION admin_adjust(p_admin BIGINT, p_user BIGINT, p_currency TEXT, p_amount BIGINT, p_reason TEXT) RETURNS wallets
LANGUAGE plpgsql AS $$
DECLARE w wallets;
BEGIN
  IF p_amount = 0 THEN PERFORM fail('Məbləğ sıfır ola bilməz'); END IF;
  IF coalesce(trim(p_reason),'') = '' THEN PERFORM fail('Səbəb yazılmalıdır'); END IF;
  w := wallet_apply(p_user, p_currency, p_amount, 0, CASE WHEN p_amount > 0 THEN 'admin_credit' ELSE 'admin_debit' END,
                    'admin', p_admin, jsonb_build_object('reason', p_reason));
  INSERT INTO admin_log (admin_id, action, target, meta)
  VALUES (p_admin, 'balance_adjust', 'user:' || p_user, jsonb_build_object('currency', p_currency, 'amount', p_amount, 'reason', p_reason));
  IF p_currency = 'RUBY' AND p_amount > 0 THEN
    PERFORM notify_user(p_user, 'ruby', 'Rubin gəldi 💎', 'Administrasiya balansınıza ' || p_amount || ' rubin əlavə etdi. ' || p_reason, '/cuzdan');
  ELSIF p_currency = 'AZN' AND p_amount > 0 THEN
    PERFORM notify_user(p_user, 'payment', 'Manat balansı artırıldı', to_char(p_amount/100.0,'FM999999990.00') || ' ₼ əlavə olundu. ' || p_reason, '/cuzdan');
  ELSE
    PERFORM notify_user(p_user, 'system', 'Balans düzəlişi', 'Administrasiya balansınızda düzəliş etdi: ' || p_reason, '/cuzdan');
  END IF;
  RETURN w;
END $$;

-- =====================================================================
-- AUKSION
-- =====================================================================

CREATE OR REPLACE FUNCTION create_auction(p_seller BIGINT, p_title TEXT, p_desc TEXT, p_image TEXT,
  p_start BIGINT, p_inc BIGINT, p_ends TIMESTAMPTZ) RETURNS BIGINT LANGUAGE plpgsql AS $$
DECLARE cfg JSONB := game_cfg()->'auction'; aid BIGINT;
  min_min INT := coalesce((cfg->>'min_duration_minutes')::int, 5);
  max_h INT := coalesce((cfg->>'max_duration_hours')::int, 168);
  max_active INT := coalesce((cfg->>'max_active_per_user')::int, 10);
BEGIN
  IF length(trim(coalesce(p_title,''))) < 3 THEN PERFORM fail('Məhsulun adı ən azı 3 simvol olmalıdır'); END IF;
  IF p_start < 1 THEN PERFORM fail('Başlanğıc qiymət ən azı 1 rubin olmalıdır'); END IF;
  IF p_inc < 1 THEN PERFORM fail('Minimum artım ən azı 1 rubin olmalıdır'); END IF;
  IF p_ends < now() + make_interval(mins => min_min) THEN PERFORM fail('Auksion ən azı ' || min_min || ' dəqiqə davam etməlidir'); END IF;
  IF p_ends > now() + make_interval(hours => max_h) THEN PERFORM fail('Auksion ən çox ' || max_h || ' saat davam edə bilər'); END IF;
  IF (SELECT count(*) FROM auctions WHERE seller_id = p_seller AND status = 'active') >= max_active THEN
    PERFORM fail('Eyni anda ən çox ' || max_active || ' aktiv auksionunuz ola bilər');
  END IF;
  INSERT INTO auctions (seller_id, title, description, image_url, start_price, min_increment, ends_at)
  VALUES (p_seller, trim(p_title), coalesce(p_desc,''), p_image, p_start, p_inc, p_ends) RETURNING id INTO aid;
  PERFORM emit_event('auction_new', NULL, jsonb_build_object('id', aid));
  RETURN aid;
END $$;

-- Təklif: auksion sətri kilidlənir (bütün təkliflər bir-bir emal olunur),
-- sonra cüzdanlar user_id sırası ilə kilidlənir.
CREATE OR REPLACE FUNCTION place_bid(p_auction BIGINT, p_user BIGINT, p_amount BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE a auctions; min_req BIGINT; prev BIGINT; prev_amount BIGINT; bidder_name TEXT;
  snipe INT := coalesce((game_cfg()->'auction'->>'anti_snipe_seconds')::int, 30);
  new_end TIMESTAMPTZ; bid_id BIGINT;
BEGIN
  SELECT * INTO a FROM auctions WHERE id = p_auction FOR UPDATE;
  IF NOT FOUND OR a.is_hidden THEN PERFORM fail('Auksion tapılmadı'); END IF;
  IF a.status <> 'active' OR a.ends_at <= now() THEN PERFORM fail('Auksion artıq bitib'); END IF;
  IF a.seller_id = p_user THEN PERFORM fail('Öz auksionunuza təklif verə bilməzsiniz'); END IF;
  IF EXISTS (SELECT 1 FROM users WHERE id = p_user AND is_blocked) THEN PERFORM fail('Hesabınız bloklanıb'); END IF;

  min_req := CASE WHEN a.current_bidder_id IS NULL THEN a.start_price ELSE a.current_price + a.min_increment END;
  IF p_amount < min_req THEN PERFORM fail('Minimum təklif: ' || min_req || ' rubin'); END IF;

  prev := a.current_bidder_id; prev_amount := a.current_price;
  SELECT username INTO bidder_name FROM users WHERE id = p_user;

  IF prev IS NOT NULL AND prev <> p_user THEN
    PERFORM lock_wallets(ARRAY[prev, p_user], 'RUBY');
    PERFORM wallet_apply(p_user, 'RUBY', 0, p_amount, 'bid_lock', 'auction', a.id);
    PERFORM wallet_apply(prev, 'RUBY', 0, -prev_amount, 'bid_release', 'auction', a.id,
                         jsonb_build_object('outbid_by', bidder_name));
    PERFORM notify_user(prev, 'bid', 'Təklifiniz keçildi ⚡',
      bidder_name || ' "' || a.title || '" üçün ' || p_amount || ' rubin təklif etdi. Bloklanmış ' || prev_amount || ' rubin qaytarıldı.',
      '/auksion/' || a.id);
  ELSIF prev = p_user THEN
    -- Öz təklifini artırır: yalnız fərq bloklanır
    PERFORM wallet_apply(p_user, 'RUBY', 0, p_amount - prev_amount, 'bid_lock', 'auction', a.id, jsonb_build_object('raise', true));
  ELSE
    PERFORM wallet_apply(p_user, 'RUBY', 0, p_amount, 'bid_lock', 'auction', a.id);
  END IF;

  new_end := a.ends_at;
  IF snipe > 0 AND a.ends_at - now() < make_interval(secs => snipe) THEN
    new_end := now() + make_interval(secs => snipe);
  END IF;

  INSERT INTO bids (auction_id, user_id, amount) VALUES (a.id, p_user, p_amount) RETURNING id INTO bid_id;
  UPDATE auctions SET current_price = p_amount, current_bidder_id = p_user, bid_count = bid_count + 1, ends_at = new_end
   WHERE id = a.id;

  IF prev IS DISTINCT FROM p_user THEN
    PERFORM notify_user(a.seller_id, 'bid', 'Auksionda yeni təklif 💎',
      bidder_name || ' "' || a.title || '" üçün ' || p_amount || ' rubin təklif etdi.', '/auksion/' || a.id);
  END IF;
  PERFORM quest_bump(p_user, 'bid', 1);
  PERFORM emit_event('bid', NULL, jsonb_build_object(
    'auction_id', a.id, 'bid_id', bid_id, 'amount', p_amount, 'user_id', p_user, 'username', bidder_name,
    'bid_count', a.bid_count + 1, 'ends_at', new_end, 'created_at', now()));
  RETURN jsonb_build_object('auction_id', a.id, 'amount', p_amount, 'ends_at', new_end);
END $$;

CREATE OR REPLACE FUNCTION finalize_auction(p_auction BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE a auctions; pct NUMERIC := coalesce((game_cfg()->'auction'->>'commission_percent')::numeric, 0);
  comm BIGINT; winner_name TEXT;
BEGIN
  SELECT * INTO a FROM auctions WHERE id = p_auction FOR UPDATE SKIP LOCKED;
  IF NOT FOUND OR a.status <> 'active' THEN RETURN NULL; END IF;

  IF a.current_bidder_id IS NULL THEN
    UPDATE auctions SET status = 'ended', ended_at = now() WHERE id = a.id;
    PERFORM notify_user(a.seller_id, 'auction_end', 'Auksion bitdi',
      '"' || a.title || '" auksionu təkliflər olmadan başa çatdı.', '/auksion/' || a.id);
  ELSE
    comm := floor(a.current_price * pct / 100);
    SELECT username INTO winner_name FROM users WHERE id = a.current_bidder_id;
    PERFORM lock_wallets(ARRAY[a.current_bidder_id, a.seller_id], 'RUBY');
    -- Qalibin bloklanmış rubini tutulur
    PERFORM wallet_apply(a.current_bidder_id, 'RUBY', -a.current_price, -a.current_price, 'auction_win', 'auction', a.id,
                         jsonb_build_object('title', a.title));
    -- Satıcıya köçürülür (komissiya çıxılmaqla)
    PERFORM wallet_apply(a.seller_id, 'RUBY', a.current_price - comm, 0, 'auction_sale', 'auction', a.id,
                         jsonb_build_object('title', a.title, 'commission', comm, 'buyer', winner_name));
    UPDATE auctions SET status = 'ended', ended_at = now(), winner_id = a.current_bidder_id,
           final_price = a.current_price, commission = comm WHERE id = a.id;
    PERFORM add_xp(a.current_bidder_id, 15);
    PERFORM add_xp(a.seller_id, 10);
    PERFORM notify_user(a.current_bidder_id, 'auction_win', 'Qalib siz oldunuz! 🏆',
      '"' || a.title || '" auksionunu ' || a.current_price || ' rubinə qazandınız.', '/auksion/' || a.id);
    PERFORM notify_user(a.seller_id, 'auction_end', 'Auksion satıldı 💰',
      '"' || a.title || '" ' || winner_name || ' tərəfindən ' || a.current_price || ' rubinə alındı. Balansınıza ' ||
      (a.current_price - comm) || ' rubin köçürüldü.', '/auksion/' || a.id);
  END IF;
  PERFORM emit_event('auction_end', NULL, jsonb_build_object('auction_id', a.id, 'winner_id', a.current_bidder_id,
     'winner', winner_name, 'final_price', a.current_price));
  RETURN jsonb_build_object('auction_id', a.id, 'winner_id', a.current_bidder_id, 'final_price', a.current_price);
END $$;

CREATE OR REPLACE FUNCTION finalize_due_auctions() RETURNS INT LANGUAGE plpgsql AS $$
DECLARE r RECORD; n INT := 0;
BEGIN
  FOR r IN SELECT id FROM auctions WHERE status = 'active' AND ends_at <= now() ORDER BY ends_at LIMIT 100 LOOP
    IF finalize_auction(r.id) IS NOT NULL THEN n := n + 1; END IF;
  END LOOP;
  RETURN n;
END $$;

CREATE OR REPLACE FUNCTION cancel_auction(p_auction BIGINT, p_actor BIGINT, p_reason TEXT, p_hide BOOLEAN) RETURNS void
LANGUAGE plpgsql AS $$
DECLARE a auctions;
BEGIN
  SELECT * INTO a FROM auctions WHERE id = p_auction FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Auksion tapılmadı'); END IF;
  IF a.status = 'active' THEN
    IF a.current_bidder_id IS NOT NULL THEN
      PERFORM wallet_apply(a.current_bidder_id, 'RUBY', 0, -a.current_price, 'bid_release', 'auction', a.id,
                           jsonb_build_object('reason', 'cancelled'));
      PERFORM notify_user(a.current_bidder_id, 'auction_end', 'Auksion ləğv edildi',
        '"' || a.title || '" ləğv olundu, ' || a.current_price || ' rubin qaytarıldı.', '/auksion/' || a.id);
    END IF;
    UPDATE auctions SET status = 'cancelled', ended_at = now(), cancel_reason = p_reason WHERE id = a.id;
    IF a.seller_id <> p_actor THEN
      PERFORM notify_user(a.seller_id, 'auction_end', 'Auksionunuz ləğv edildi', '"' || a.title || '": ' || coalesce(p_reason,''), '/auksion/' || a.id);
    END IF;
  END IF;
  IF p_hide THEN UPDATE auctions SET is_hidden = TRUE WHERE id = a.id; END IF;
  PERFORM emit_event('auction_end', NULL, jsonb_build_object('auction_id', a.id, 'cancelled', true));
END $$;

-- İstifadəçi öz auksionunu yalnız təklif yoxdursa ləğv edə bilər
CREATE OR REPLACE FUNCTION seller_cancel_auction(p_auction BIGINT, p_user BIGINT) RETURNS void LANGUAGE plpgsql AS $$
DECLARE a auctions;
BEGIN
  SELECT * INTO a FROM auctions WHERE id = p_auction FOR UPDATE;
  IF NOT FOUND OR a.seller_id <> p_user THEN PERFORM fail('Auksion tapılmadı'); END IF;
  IF a.status <> 'active' THEN PERFORM fail('Auksion artıq aktiv deyil'); END IF;
  IF a.current_bidder_id IS NOT NULL THEN PERFORM fail('Təklif verilmiş auksionu ləğv etmək olmaz'); END IF;
  PERFORM cancel_auction(p_auction, p_user, 'Satıcı tərəfindən ləğv edildi', FALSE);
END $$;

-- =====================================================================
-- FERMA OYUNU
-- =====================================================================

CREATE OR REPLACE FUNCTION farm_level_cfg(p_level INT) RETURNS JSONB LANGUAGE sql STABLE AS $$
  SELECT coalesce(
    (SELECT l FROM jsonb_array_elements(game_cfg()->'farm_levels') l WHERE (l->>'level')::int = p_level),
    (SELECT l FROM jsonb_array_elements(game_cfg()->'farm_levels') l ORDER BY (l->>'level')::int DESC LIMIT 1))
$$;

CREATE OR REPLACE FUNCTION cfg_item(p_list TEXT, p_key TEXT) RETURNS JSONB LANGUAGE sql STABLE AS $$
  SELECT x FROM jsonb_array_elements(game_cfg()->p_list) x WHERE x->>'key' = p_key
$$;

CREATE OR REPLACE FUNCTION farm_plant(p_user BIGINT, p_plot BIGINT, p_crop TEXT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE c JSONB := cfg_item('crops', p_crop); pl plots; lvl INT; ready TIMESTAMPTZ;
BEGIN
  IF c IS NULL THEN PERFORM fail('Bitki tapılmadı'); END IF;
  SELECT level INTO lvl FROM users WHERE id = p_user FOR UPDATE;
  IF lvl < coalesce((c->>'min_level')::int, 1) THEN PERFORM fail('Bu bitki ' || (c->>'min_level') || '-ci səviyyədən açılır'); END IF;
  SELECT * INTO pl FROM plots WHERE id = p_plot AND user_id = p_user FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Torpaq sahəsi tapılmadı'); END IF;
  IF pl.crop_key IS NOT NULL THEN PERFORM fail('Bu sahədə artıq bitki var'); END IF;
  PERFORM wallet_apply(p_user, 'RUBY', -(c->>'seed_cost')::bigint, 0, 'seed_purchase', 'plot', pl.id, jsonb_build_object('crop', p_crop));
  ready := now() + make_interval(secs => ((c->>'grow_minutes')::numeric * 60)::int);
  UPDATE plots SET crop_key = p_crop, planted_at = now(), ready_at = ready, fertilized = FALSE WHERE id = pl.id;
  PERFORM quest_bump(p_user, 'plant', 1);
  RETURN jsonb_build_object('plot_id', pl.id, 'ready_at', ready);
END $$;

CREATE OR REPLACE FUNCTION farm_harvest(p_user BIGINT, p_plot BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE pl plots; c JSONB; fl JSONB; reward BIGINT; xp INT;
BEGIN
  SELECT * INTO pl FROM plots WHERE id = p_plot AND user_id = p_user FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Torpaq sahəsi tapılmadı'); END IF;
  IF pl.crop_key IS NULL THEN PERFORM fail('Bu sahə boşdur'); END IF;
  IF pl.ready_at > now() THEN PERFORM fail('Məhsul hələ yetişməyib'); END IF;
  c := cfg_item('crops', pl.crop_key);
  fl := farm_level_cfg((SELECT farm_level FROM users WHERE id = p_user));
  reward := round(coalesce((c->>'reward')::numeric, 1) * (100 + coalesce((fl->>'yield_bonus')::numeric,0)) / 100);
  xp := coalesce((c->>'xp')::int, 1);
  UPDATE plots SET crop_key = NULL, planted_at = NULL, ready_at = NULL, fertilized = FALSE WHERE id = pl.id;
  PERFORM wallet_apply(p_user, 'RUBY', reward, 0, 'harvest', 'plot', pl.id, jsonb_build_object('crop', pl.crop_key));
  UPDATE users SET harvest_count = harvest_count + 1, farm_earned = farm_earned + reward WHERE id = p_user;
  PERFORM add_xp(p_user, xp);
  PERFORM quest_bump(p_user, 'harvest', 1);
  RETURN jsonb_build_object('reward', reward, 'xp', xp, 'crop', pl.crop_key);
END $$;

CREATE OR REPLACE FUNCTION farm_harvest_all(p_user BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE r RECORD; res JSONB; total BIGINT := 0; n INT := 0; txp INT := 0;
BEGIN
  FOR r IN SELECT id FROM plots WHERE user_id = p_user AND crop_key IS NOT NULL AND ready_at <= now() ORDER BY id LOOP
    res := farm_harvest(p_user, r.id);
    total := total + (res->>'reward')::bigint; txp := txp + (res->>'xp')::int; n := n + 1;
  END LOOP;
  IF n = 0 THEN PERFORM fail('Yığılmağa hazır məhsul yoxdur'); END IF;
  RETURN jsonb_build_object('count', n, 'reward', total, 'xp', txp);
END $$;

CREATE OR REPLACE FUNCTION farm_buy_plot(p_user BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE cfg JSONB := game_cfg()->'plot'; u users; owned INT; maxp INT; price BIGINT; pid BIGINT;
BEGIN
  SELECT * INTO u FROM users WHERE id = p_user FOR UPDATE;
  SELECT count(*) INTO owned FROM plots WHERE user_id = p_user;
  maxp := (farm_level_cfg(u.farm_level)->>'max_plots')::int;
  IF owned >= maxp THEN PERFORM fail('Maksimum torpaq sayına çatmısınız. Fermanı inkişaf etdirin'); END IF;
  price := (cfg->>'base_price')::bigint + (cfg->>'price_step')::bigint * greatest(owned - coalesce((cfg->>'start_plots')::int,0), 0);
  INSERT INTO plots (user_id) VALUES (p_user) RETURNING id INTO pid;
  PERFORM wallet_apply(p_user, 'RUBY', -price, 0, 'land_purchase', 'plot', pid);
  PERFORM add_xp(p_user, 5);
  RETURN jsonb_build_object('plot_id', pid, 'price', price);
END $$;

CREATE OR REPLACE FUNCTION farm_sell_plot(p_user BIGINT, p_plot BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE cfg JSONB := game_cfg()->'plot'; pl plots; owned INT; refund BIGINT;
BEGIN
  PERFORM 1 FROM users WHERE id = p_user FOR UPDATE;
  SELECT * INTO pl FROM plots WHERE id = p_plot AND user_id = p_user FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Torpaq sahəsi tapılmadı'); END IF;
  IF pl.crop_key IS NOT NULL THEN PERFORM fail('Əkilmiş sahəni satmaq olmaz'); END IF;
  SELECT count(*) INTO owned FROM plots WHERE user_id = p_user;
  IF owned <= 1 THEN PERFORM fail('Son torpaq sahənizi sata bilməzsiniz'); END IF;
  refund := floor(((cfg->>'base_price')::bigint + (cfg->>'price_step')::bigint *
            greatest(owned - 1 - coalesce((cfg->>'start_plots')::int,0), 0)) * coalesce((cfg->>'sell_refund_percent')::numeric, 50) / 100);
  DELETE FROM plots WHERE id = pl.id;
  IF refund > 0 THEN
    PERFORM wallet_apply(p_user, 'RUBY', refund, 0, 'land_sale', 'plot', pl.id);
  END IF;
  RETURN jsonb_build_object('refund', refund);
END $$;

CREATE OR REPLACE FUNCTION farm_buy_animal(p_user BIGINT, p_animal TEXT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE an JSONB := cfg_item('animals', p_animal); u users; owned INT; aid BIGINT;
BEGIN
  IF an IS NULL THEN PERFORM fail('Heyvan tapılmadı'); END IF;
  SELECT * INTO u FROM users WHERE id = p_user FOR UPDATE;
  IF u.level < coalesce((an->>'min_level')::int,1) THEN PERFORM fail('Bu heyvan ' || (an->>'min_level') || '-ci səviyyədən açılır'); END IF;
  SELECT count(*) INTO owned FROM user_animals WHERE user_id = p_user;
  IF owned >= (farm_level_cfg(u.farm_level)->>'max_animals')::int THEN
    PERFORM fail('Tövlədə yer yoxdur. Fermanı inkişaf etdirin');
  END IF;
  INSERT INTO user_animals (user_id, animal_key) VALUES (p_user, p_animal) RETURNING id INTO aid;
  PERFORM wallet_apply(p_user, 'RUBY', -(an->>'price')::bigint, 0, 'animal_purchase', 'animal', aid, jsonb_build_object('animal', p_animal));
  PERFORM add_xp(p_user, 5);
  RETURN jsonb_build_object('animal_id', aid);
END $$;

CREATE OR REPLACE FUNCTION farm_sell_animal(p_user BIGINT, p_id BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE r user_animals; an JSONB; refund BIGINT;
BEGIN
  SELECT * INTO r FROM user_animals WHERE id = p_id AND user_id = p_user FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Heyvan tapılmadı'); END IF;
  an := cfg_item('animals', r.animal_key);
  refund := floor((an->>'price')::bigint * coalesce((game_cfg()->'plot'->>'sell_refund_percent')::numeric, 50) / 100);
  DELETE FROM user_animals WHERE id = r.id;
  PERFORM wallet_apply(p_user, 'RUBY', refund, 0, 'animal_sale', 'animal', r.id, jsonb_build_object('animal', r.animal_key));
  RETURN jsonb_build_object('refund', refund);
END $$;

CREATE OR REPLACE FUNCTION farm_collect(p_user BIGINT, p_id BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE r user_animals; an JSONB; fl JSONB; reward BIGINT; xp INT;
BEGIN
  SELECT * INTO r FROM user_animals WHERE id = p_id AND user_id = p_user FOR UPDATE;
  IF NOT FOUND THEN PERFORM fail('Heyvan tapılmadı'); END IF;
  an := cfg_item('animals', r.animal_key);
  IF r.last_collected_at + make_interval(secs => ((an->>'interval_minutes')::numeric * 60)::int) > now() THEN
    PERFORM fail('Məhsul hələ hazır deyil');
  END IF;
  fl := farm_level_cfg((SELECT farm_level FROM users WHERE id = p_user));
  reward := round((an->>'reward')::numeric * (100 + coalesce((fl->>'yield_bonus')::numeric,0)) / 100);
  xp := coalesce((an->>'xp')::int, 1);
  UPDATE user_animals SET last_collected_at = now() WHERE id = r.id;
  PERFORM wallet_apply(p_user, 'RUBY', reward, 0, 'animal_collect', 'animal', r.id, jsonb_build_object('animal', r.animal_key));
  UPDATE users SET farm_earned = farm_earned + reward WHERE id = p_user;
  PERFORM add_xp(p_user, xp);
  PERFORM quest_bump(p_user, 'collect', 1);
  RETURN jsonb_build_object('reward', reward, 'xp', xp, 'product', an->>'product');
END $$;

CREATE OR REPLACE FUNCTION farm_upgrade(p_user BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE u users; nxt JSONB; cost BIGINT;
BEGIN
  SELECT * INTO u FROM users WHERE id = p_user FOR UPDATE;
  SELECT l INTO nxt FROM jsonb_array_elements(game_cfg()->'farm_levels') l WHERE (l->>'level')::int = u.farm_level + 1;
  IF nxt IS NULL THEN PERFORM fail('Ferma artıq maksimum səviyyədədir'); END IF;
  IF u.level < coalesce((nxt->>'min_player_level')::int, 1) THEN
    PERFORM fail('Bunun üçün ' || (nxt->>'min_player_level') || '-ci oyunçu səviyyəsi lazımdır');
  END IF;
  cost := (nxt->>'upgrade_cost')::bigint;
  PERFORM wallet_apply(p_user, 'RUBY', -cost, 0, 'farm_upgrade', 'farm', u.farm_level + 1);
  UPDATE users SET farm_level = farm_level + 1 WHERE id = p_user;
  PERFORM add_xp(p_user, 25);
  PERFORM notify_user(p_user, 'system', 'Ferma inkişaf etdirildi 🏡', 'Fermanız ' || (u.farm_level + 1) || '-ci səviyyəyə yüksəldi.', '/ferma');
  RETURN jsonb_build_object('farm_level', u.farm_level + 1, 'cost', cost);
END $$;

CREATE OR REPLACE FUNCTION shop_buy(p_user BIGINT, p_item TEXT, p_qty INT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE it JSONB := cfg_item('shop', p_item); cost BIGINT;
BEGIN
  IF it IS NULL THEN PERFORM fail('Əşya tapılmadı'); END IF;
  IF p_qty < 1 OR p_qty > 99 THEN PERFORM fail('Miqdar 1–99 arası olmalıdır'); END IF;
  cost := (it->>'price')::bigint * p_qty;
  PERFORM wallet_apply(p_user, 'RUBY', -cost, 0, 'shop_purchase', 'item', NULL, jsonb_build_object('item', p_item, 'qty', p_qty));
  INSERT INTO user_items (user_id, item_key, qty) VALUES (p_user, p_item, p_qty)
  ON CONFLICT (user_id, item_key) DO UPDATE SET qty = user_items.qty + p_qty;
  PERFORM quest_bump(p_user, 'shop', 1);
  RETURN jsonb_build_object('item', p_item, 'qty', p_qty, 'cost', cost);
END $$;

CREATE OR REPLACE FUNCTION use_item(p_user BIGINT, p_item TEXT, p_plot BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE it JSONB := cfg_item('shop', p_item); pl plots; ui user_items; remaining INTERVAL; amount INT; r RECORD; n INT := 0;
BEGIN
  IF it IS NULL THEN PERFORM fail('Əşya tapılmadı'); END IF;
  SELECT * INTO ui FROM user_items WHERE user_id = p_user AND item_key = p_item FOR UPDATE;
  IF NOT FOUND OR ui.qty < 1 THEN PERFORM fail('Bu əşyadan sizdə yoxdur'); END IF;

  IF it->>'type' = 'fertilizer' THEN
    SELECT * INTO pl FROM plots WHERE id = p_plot AND user_id = p_user FOR UPDATE;
    IF NOT FOUND OR pl.crop_key IS NULL THEN PERFORM fail('Gübrə üçün əkilmiş sahə seçin'); END IF;
    IF pl.fertilized THEN PERFORM fail('Bu sahəyə artıq gübrə verilib'); END IF;
    IF pl.ready_at <= now() THEN PERFORM fail('Məhsul artıq hazırdır'); END IF;
    remaining := pl.ready_at - now();
    UPDATE plots SET ready_at = now() + remaining / 2, fertilized = TRUE WHERE id = pl.id;
  ELSIF it->>'type' = 'growall' THEN
    FOR r IN SELECT id FROM plots WHERE user_id = p_user AND crop_key IS NOT NULL AND ready_at > now() FOR UPDATE LOOP
      UPDATE plots SET ready_at = now() WHERE id = r.id; n := n + 1;
    END LOOP;
    IF n = 0 THEN PERFORM fail('Yetişməkdə olan bitki yoxdur'); END IF;
  ELSIF it->>'type' = 'xp' THEN
    amount := coalesce((it->>'value')::int, 50);
    PERFORM add_xp(p_user, amount);
  ELSE
    PERFORM fail('Bu əşya kolleksiya əşyasıdır və istifadə olunmur');
  END IF;
  UPDATE user_items SET qty = qty - 1 WHERE user_id = p_user AND item_key = p_item;
  RETURN jsonb_build_object('item', p_item, 'ok', true);
END $$;

CREATE OR REPLACE FUNCTION claim_daily_bonus(p_user BIGINT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE cfg JSONB := game_cfg()->'daily_bonus'; u users; today DATE := baku_today(); streak INT; reward BIGINT;
BEGIN
  SELECT * INTO u FROM users WHERE id = p_user FOR UPDATE;
  IF u.last_bonus_date = today THEN PERFORM fail('Bu günün bonusunu artıq almısınız'); END IF;
  streak := CASE WHEN u.last_bonus_date = today - 1 THEN u.daily_streak + 1 ELSE 1 END;
  reward := (cfg->>'base')::bigint + (cfg->>'streak_step')::bigint * (least(streak, (cfg->>'max_streak')::int) - 1);
  UPDATE users SET last_bonus_date = today, daily_streak = streak WHERE id = p_user;
  PERFORM wallet_apply(p_user, 'RUBY', reward, 0, 'daily_bonus', 'bonus', NULL, jsonb_build_object('streak', streak));
  PERFORM add_xp(p_user, coalesce((cfg->>'xp')::int, 10));
  PERFORM notify_user(p_user, 'bonus', 'Gündəlik bonus 🎁', reward || ' rubin qazandınız! Ardıcıl gün: ' || streak, '/tapsiriqlar');
  RETURN jsonb_build_object('reward', reward, 'streak', streak);
END $$;

CREATE OR REPLACE FUNCTION claim_quest(p_user BIGINT, p_quest TEXT) RETURNS JSONB LANGUAGE plpgsql AS $$
DECLARE q JSONB := cfg_item('quests', p_quest); qp quest_progress;
BEGIN
  IF q IS NULL THEN PERFORM fail('Tapşırıq tapılmadı'); END IF;
  SELECT * INTO qp FROM quest_progress WHERE user_id = p_user AND day = baku_today() AND quest_key = p_quest FOR UPDATE;
  IF NOT FOUND OR qp.progress < (q->>'target')::int THEN PERFORM fail('Tapşırıq hələ tamamlanmayıb'); END IF;
  IF qp.claimed THEN PERFORM fail('Mükafat artıq alınıb'); END IF;
  UPDATE quest_progress SET claimed = TRUE WHERE user_id = p_user AND day = qp.day AND quest_key = p_quest;
  PERFORM wallet_apply(p_user, 'RUBY', (q->>'reward')::bigint, 0, 'quest_reward', 'quest', NULL, jsonb_build_object('quest', p_quest));
  PERFORM add_xp(p_user, coalesce((q->>'xp')::int, 0));
  RETURN jsonb_build_object('reward', (q->>'reward')::bigint, 'xp', coalesce((q->>'xp')::int, 0));
END $$;

-- =====================================================================
-- Mesajlar
-- =====================================================================
CREATE OR REPLACE FUNCTION send_message(p_from BIGINT, p_to BIGINT, p_body TEXT) RETURNS messages LANGUAGE plpgsql AS $$
DECLARE m messages; from_name TEXT;
BEGIN
  IF p_from = p_to THEN PERFORM fail('Özünüzə mesaj yaza bilməzsiniz'); END IF;
  IF NOT EXISTS (SELECT 1 FROM users WHERE id = p_to AND NOT is_blocked) THEN PERFORM fail('İstifadəçi tapılmadı'); END IF;
  IF EXISTS (SELECT 1 FROM user_blocks WHERE (blocker_id = p_to AND blocked_id = p_from) OR (blocker_id = p_from AND blocked_id = p_to)) THEN
    PERFORM fail('Bu istifadəçi ilə mesajlaşma bloklanıb');
  END IF;
  IF length(trim(coalesce(p_body,''))) = 0 THEN PERFORM fail('Mesaj boşdur'); END IF;
  INSERT INTO messages (sender_id, receiver_id, body) VALUES (p_from, p_to, trim(p_body)) RETURNING * INTO m;
  SELECT username INTO from_name FROM users WHERE id = p_from;
  PERFORM emit_event('message', p_to, jsonb_build_object('id', m.id, 'sender_id', p_from, 'receiver_id', p_to,
     'sender', from_name, 'body', m.body, 'created_at', m.created_at));
  PERFORM emit_event('message', p_from, jsonb_build_object('id', m.id, 'sender_id', p_from, 'receiver_id', p_to,
     'sender', from_name, 'body', m.body, 'created_at', m.created_at));
  -- Oxunmamış mesaj bildirişi (spam olmasın deyə: 10 dəqiqədə bir)
  IF NOT EXISTS (SELECT 1 FROM notifications WHERE user_id = p_to AND type = 'message' AND link = '/mesajlar?u=' || from_name
                  AND created_at > now() - interval '10 minutes') THEN
    PERFORM notify_user(p_to, 'message', 'Yeni mesaj ✉️', from_name || ': ' || left(m.body, 80), '/mesajlar?u=' || from_name);
  END IF;
  RETURN m;
END $$;