-- Migration: B2B Classified Interactions
-- Date: 2026-01-31
-- Description: Dodanie tabel dla interakcji z ogłoszeniami B2B:
--   - classified_interests (zainteresowania)
--   - classified_questions (pytania i odpowiedzi publiczne)
--   - context_type/context_id w private_messages (powiązanie wiadomości z ogłoszeniem)

-- ============================================================
-- 1. Tabela zainteresowań ogłoszeniami
-- ============================================================

CREATE TABLE IF NOT EXISTS classified_interests (
    id SERIAL PRIMARY KEY,
    classified_id INTEGER NOT NULL REFERENCES classifieds(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    message VARCHAR(255),  -- opcjonalna krótka notatka
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(classified_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_classified_interests_classified ON classified_interests(classified_id);
CREATE INDEX IF NOT EXISTS idx_classified_interests_user ON classified_interests(user_id);

COMMENT ON TABLE classified_interests IS 'Zainteresowania użytkowników ogłoszeniami B2B';
COMMENT ON COLUMN classified_interests.message IS 'Opcjonalna krótka notatka do zainteresowania';

-- ============================================================
-- 2. Tabela pytań i odpowiedzi publicznych
-- ============================================================

CREATE TABLE IF NOT EXISTS classified_questions (
    id SERIAL PRIMARY KEY,
    classified_id INTEGER NOT NULL REFERENCES classifieds(id) ON DELETE CASCADE,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,

    -- Odpowiedź właściciela ogłoszenia
    answer TEXT,
    answered_by INTEGER REFERENCES users(id),
    answered_at TIMESTAMP,

    -- Widoczność
    is_public BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_classified_questions_classified ON classified_questions(classified_id);
CREATE INDEX IF NOT EXISTS idx_classified_questions_author ON classified_questions(author_id);
CREATE INDEX IF NOT EXISTS idx_classified_questions_unanswered ON classified_questions(classified_id) WHERE answer IS NULL;

COMMENT ON TABLE classified_questions IS 'Publiczne pytania i odpowiedzi do ogłoszeń B2B';
COMMENT ON COLUMN classified_questions.is_public IS 'Czy pytanie jest widoczne publicznie (autor ogłoszenia może ukryć)';

-- ============================================================
-- 3. Kontekst w wiadomościach prywatnych
-- ============================================================

-- Dodaj kolumny kontekstu do private_messages
ALTER TABLE private_messages
ADD COLUMN IF NOT EXISTS context_type VARCHAR(50),
ADD COLUMN IF NOT EXISTS context_id INTEGER;

CREATE INDEX IF NOT EXISTS idx_private_messages_context ON private_messages(context_type, context_id) WHERE context_type IS NOT NULL;

COMMENT ON COLUMN private_messages.context_type IS 'Typ powiązanego obiektu: classified, forum_topic, etc.';
COMMENT ON COLUMN private_messages.context_id IS 'ID powiązanego obiektu';

-- ============================================================
-- 4. Uprawnienia
-- ============================================================

GRANT ALL ON TABLE classified_interests TO nordabiz_app;
GRANT ALL ON TABLE classified_questions TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE classified_interests_id_seq TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE classified_questions_id_seq TO nordabiz_app;