-- ============================================================
-- BiznesOutlet.pl – Schemat bazy danych PostgreSQL
-- Uruchom: psql -U postgres -d biznesoutlet -f schema.sql
-- ============================================================

-- Rozszerzenia
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ─── UŻYTKOWNICY ─────────────────────────────────────────────────────────────
CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email         VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name    VARCHAR(100),
  last_name     VARCHAR(100),
  company_name  VARCHAR(255),
  licence_no    VARCHAR(50),           -- numer licencji DR / KM
  licence_type  VARCHAR(20),           -- 'DR' | 'KM' | 'OTHER'
  trust_score   SMALLINT DEFAULT 0,    -- 0-100, wyliczany przez cron
  phone         VARCHAR(20),
  nip           VARCHAR(15),
  address       TEXT,
  avatar_url    TEXT,
  plan          VARCHAR(20) DEFAULT 'free', -- free | professional | business | enterprise
  plan_expires_at TIMESTAMPTZ,
  email_verified  BOOLEAN DEFAULT FALSE,
  is_active       BOOLEAN DEFAULT TRUE,
  is_admin        BOOLEAN DEFAULT FALSE,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_plan ON users(plan);

-- ─── TOKENY ODŚWIEŻANIA ───────────────────────────────────────────────────────
CREATE TABLE refresh_tokens (
  id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token      VARCHAR(512) UNIQUE NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ─── PREFERENCJE POWIADOMIEŃ ──────────────────────────────────────────────────
CREATE TABLE notification_prefs (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  channel_email  BOOLEAN DEFAULT TRUE,
  channel_sms    BOOLEAN DEFAULT FALSE,
  channel_push   BOOLEAN DEFAULT FALSE,
  event_new      BOOLEAN DEFAULT TRUE,   -- nowe ogłoszenie
  event_ending   BOOLEAN DEFAULT TRUE,   -- koniec za 1h
  event_outbid   BOOLEAN DEFAULT TRUE,   -- ktoś przebił ofertę
  event_digest   BOOLEAN DEFAULT FALSE,  -- cotygodniowy digest
  event_promo    BOOLEAN DEFAULT FALSE,
  categories     TEXT[] DEFAULT '{}',    -- ['nieruchomosci','prawna',...]
  updated_at     TIMESTAMPTZ DEFAULT NOW()
);

-- ─── OGŁOSZENIA (aukcje / przetargi / usługi) ────────────────────────────────
CREATE TABLE listings (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID NOT NULL REFERENCES users(id),
  listing_type    VARCHAR(30) NOT NULL, -- 'auction' | 'tender' | 'listing' | 'service_tender' | 'service_order'
  category        VARCHAR(50) NOT NULL,
  title           VARCHAR(500) NOT NULL,
  description     TEXT,
  location        VARCHAR(255),
  lat             DECIMAL(10,7),
  lng             DECIMAL(10,7),
  -- Ceny
  starting_price  DECIMAL(15,2),        -- cena wywoławcza / budżet
  min_bid_step    DECIMAL(15,2) DEFAULT 1000,
  current_bid     DECIMAL(15,2),
  reserve_price   DECIMAL(15,2),        -- cena minimalna (ukryta)
  -- Aukcja
  deadline        TIMESTAMPTZ,
  bids_count      INTEGER DEFAULT 0,
  views_count     INTEGER DEFAULT 0,
  -- Usługi
  wadium_amount   DECIMAL(15,2) DEFAULT 0,
  gwarancja       BOOLEAN DEFAULT FALSE,
  -- Metadane
  sygn_akt        VARCHAR(100),         -- sygnatura akt sprawy
  appraisal_value DECIMAL(15,2),        -- wycena biegłego
  -- Status
  status          VARCHAR(20) DEFAULT 'draft', -- draft | pending | active | closed | cancelled
  featured        BOOLEAN DEFAULT FALSE,
  is_urgent       BOOLEAN DEFAULT FALSE,
  ai_enabled      BOOLEAN DEFAULT FALSE,
  -- Opcje płatne
  promoted_until  TIMESTAMPTZ,
  -- Timestamps
  published_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_listings_user ON listings(user_id);
CREATE INDEX idx_listings_type ON listings(listing_type);
CREATE INDEX idx_listings_category ON listings(category);
CREATE INDEX idx_listings_status ON listings(status);
CREATE INDEX idx_listings_deadline ON listings(deadline);
CREATE INDEX idx_listings_search ON listings USING GIN (to_tsvector('polish', title || ' ' || COALESCE(description,'')));

-- ─── ZDJĘCIA I DOKUMENTY ─────────────────────────────────────────────────────
CREATE TABLE listing_files (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  listing_id  UUID NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
  file_type   VARCHAR(20) NOT NULL, -- 'image' | 'document'
  s3_key      VARCHAR(500) NOT NULL,
  url         TEXT NOT NULL,
  filename    VARCHAR(255),
  mime_type   VARCHAR(100),
  size_bytes  INTEGER,
  sort_order  SMALLINT DEFAULT 0,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_files_listing ON listing_files(listing_id);

-- ─── OFERTY (licytacje i oferty przetargowe) ──────────────────────────────────
CREATE TABLE bids (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  listing_id  UUID NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id),
  amount      DECIMAL(15,2) NOT NULL,
  bid_type    VARCHAR(20) DEFAULT 'bid',  -- 'bid' | 'service_offer'
  -- Dla ofert usługowych
  offer_price DECIMAL(15,2),
  offer_timeline VARCHAR(255),
  offer_description TEXT,
  -- Status
  status      VARCHAR(20) DEFAULT 'active', -- active | winning | outbid | cancelled | selected | rejected
  wadium_paid BOOLEAN DEFAULT FALSE,
  wadium_transaction_id UUID,
  is_winner   BOOLEAN DEFAULT FALSE,
  -- Timestamps
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_bids_listing ON bids(listing_id);
CREATE INDEX idx_bids_user ON bids(user_id);
CREATE INDEX idx_bids_status ON bids(status);
CREATE UNIQUE INDEX idx_bids_unique_winner ON bids(listing_id) WHERE is_winner = TRUE;

-- ─── WADIUM / ESCROW ─────────────────────────────────────────────────────────
CREATE TABLE wadium_transactions (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  listing_id        UUID NOT NULL REFERENCES listings(id),
  user_id           UUID NOT NULL REFERENCES users(id),
  bid_id            UUID REFERENCES bids(id),
  amount            DECIMAL(15,2) NOT NULL,
  -- Przelewy24
  p24_order_id      VARCHAR(100) UNIQUE,
  p24_session_id    VARCHAR(255),
  p24_transaction_id VARCHAR(100),
  payment_url       TEXT,
  -- Status
  status            VARCHAR(20) DEFAULT 'pending',
  -- pending | paid | held | released | forfeited
  paid_at           TIMESTAMPTZ,
  released_at       TIMESTAMPTZ,
  release_reason    VARCHAR(50),  -- 'winner_confirmed' | 'auction_lost' | 'forfeited'
  created_at        TIMESTAMPTZ DEFAULT NOW(),
  updated_at        TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_wadium_listing ON wadium_transactions(listing_id);
CREATE INDEX idx_wadium_user ON wadium_transactions(user_id);
CREATE INDEX idx_wadium_status ON wadium_transactions(status);

-- ─── SUBSKRYPCJE ─────────────────────────────────────────────────────────────
CREATE TABLE subscriptions (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID UNIQUE NOT NULL REFERENCES users(id),
  plan            VARCHAR(20) NOT NULL,
  billing_cycle   VARCHAR(10) DEFAULT 'monthly', -- monthly | annual
  price_pln       DECIMAL(10,2),
  p24_order_id    VARCHAR(100),
  status          VARCHAR(20) DEFAULT 'active', -- active | cancelled | expired
  starts_at       TIMESTAMPTZ NOT NULL,
  expires_at      TIMESTAMPTZ,
  cancelled_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ─── FAKTURY ─────────────────────────────────────────────────────────────────
CREATE TABLE invoices (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id       UUID NOT NULL REFERENCES users(id),
  invoice_no    VARCHAR(50) UNIQUE NOT NULL,
  amount_net    DECIMAL(10,2) NOT NULL,
  vat_rate      DECIMAL(5,2) DEFAULT 23.00,
  amount_gross  DECIMAL(10,2) NOT NULL,
  description   TEXT,
  status        VARCHAR(20) DEFAULT 'issued', -- issued | paid | cancelled
  issued_at     TIMESTAMPTZ DEFAULT NOW(),
  due_at        TIMESTAMPTZ,
  paid_at       TIMESTAMPTZ,
  pdf_url       TEXT
);

-- ─── POWIADOMIENIA ───────────────────────────────────────────────────────────
CREATE TABLE notifications (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type        VARCHAR(50) NOT NULL,
  title       VARCHAR(255),
  body        TEXT,
  data        JSONB,
  channel     VARCHAR(20),  -- email | sms | push | in_app
  sent_at     TIMESTAMPTZ,
  read_at     TIMESTAMPTZ,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_notif_user ON notifications(user_id);
CREATE INDEX idx_notif_unread ON notifications(user_id) WHERE read_at IS NULL;

-- ─── OBSERWOWANE OGŁOSZENIA ───────────────────────────────────────────────────
CREATE TABLE watchlist (
  user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  listing_id UUID NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_id, listing_id)
);

-- ─── LOGI AKTYWNOŚCI ─────────────────────────────────────────────────────────
CREATE TABLE activity_logs (
  id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id    UUID REFERENCES users(id),
  action     VARCHAR(100) NOT NULL,
  entity     VARCHAR(50),
  entity_id  UUID,
  meta       JSONB,
  ip         INET,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_logs_user ON activity_logs(user_id);
CREATE INDEX idx_logs_created ON activity_logs(created_at);

-- ─── WIDOK: AKTYWNE AUKCJE ───────────────────────────────────────────────────
CREATE VIEW active_listings AS
SELECT l.*, u.first_name, u.last_name, u.company_name, u.licence_no, u.licence_type, u.trust_score,
       (SELECT url FROM listing_files lf WHERE lf.listing_id = l.id AND lf.file_type = 'image' ORDER BY lf.sort_order LIMIT 1) AS thumbnail_url,
       (SELECT COUNT(*) FROM bids b WHERE b.listing_id = l.id AND b.status != 'cancelled') AS active_bids_count,
       (SELECT MAX(amount) FROM bids b WHERE b.listing_id = l.id AND b.status != 'cancelled') AS highest_bid
FROM listings l
JOIN users u ON l.user_id = u.id
WHERE l.status = 'active' AND (l.deadline IS NULL OR l.deadline > NOW());

-- ─── FUNKCJA: auto-close aukcji po deadline ───────────────────────────────────
CREATE OR REPLACE FUNCTION close_expired_listings() RETURNS void AS $$
BEGIN
  UPDATE listings
  SET status = 'closed', updated_at = NOW()
  WHERE status = 'active'
    AND deadline IS NOT NULL
    AND deadline < NOW();

  UPDATE bids b SET status = 'outbid'
  FROM listings l
  WHERE b.listing_id = l.id
    AND l.status = 'closed'
    AND b.status = 'active'
    AND b.amount < (SELECT MAX(b2.amount) FROM bids b2 WHERE b2.listing_id = l.id);

  UPDATE bids b SET status = 'winning', is_winner = TRUE
  FROM listings l
  WHERE b.listing_id = l.id
    AND l.status = 'closed'
    AND b.status = 'active'
    AND b.amount = (SELECT MAX(b2.amount) FROM bids b2 WHERE b2.listing_id = l.id);
END;
$$ LANGUAGE plpgsql;

-- ─── INDEKSY PEŁNOTEKSTOWE ────────────────────────────────────────────────────
CREATE INDEX idx_listings_fts ON listings USING GIN(
  to_tsvector('simple', COALESCE(title,'') || ' ' || COALESCE(description,'') || ' ' || COALESCE(location,'') || ' ' || COALESCE(sygn_akt,''))
);

COMMENT ON TABLE listings IS 'Ogłoszenia: aukcje, przetargi, zlecenia usług';
COMMENT ON TABLE bids IS 'Oferty składane przez użytkowników';
COMMENT ON TABLE wadium_transactions IS 'Transakcje wadium przez Przelewy24 – escrow';
COMMENT ON TABLE notification_prefs IS 'Preferencje powiadomień użytkownika';
