-- Rockview Staff Marketplace social upgrade (PostgreSQL)
-- Safe to run more than once.

CREATE TABLE IF NOT EXISTS campus_market_items (
    id BIGSERIAL PRIMARY KEY,
    seller_user_id BIGINT NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    price NUMERIC(12,2) NOT NULL DEFAULT 0,
    category VARCHAR(80) NOT NULL DEFAULT 'Other',
    condition VARCHAR(80) NOT NULL DEFAULT 'Used - Good',
    quantity INTEGER NOT NULL DEFAULT 1,
    campus VARCHAR(180) NULL,
    location VARCHAR(220) NULL,
    seller_name VARCHAR(180) NULL,
    seller_phone VARCHAR(80) NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    report_reason TEXT NULL,
    legacy_image_url TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMPTZ NULL,
    sold_at TIMESTAMPTZ NULL
);

CREATE TABLE IF NOT EXISTS campus_market_media (
    id BIGSERIAL PRIMARY KEY,
    item_id BIGINT NOT NULL REFERENCES campus_market_items(id) ON DELETE CASCADE,
    media_type VARCHAR(20) NOT NULL DEFAULT 'image',
    media_path TEXT NOT NULL,
    thumbnail_path TEXT NULL,
    file_size BIGINT NOT NULL DEFAULT 0,
    position INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_market_likes (
    id BIGSERIAL PRIMARY KEY,
    item_id BIGINT NOT NULL REFERENCES campus_market_items(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (item_id, user_id)
);

CREATE TABLE IF NOT EXISTS campus_market_comments (
    id BIGSERIAL PRIMARY KEY,
    item_id BIGINT NOT NULL REFERENCES campus_market_items(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL,
    parent_comment_id BIGINT NULL REFERENCES campus_market_comments(id) ON DELETE CASCADE,
    comment TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_market_comment_mentions (
    id BIGSERIAL PRIMARY KEY,
    comment_id BIGINT NOT NULL REFERENCES campus_market_comments(id) ON DELETE CASCADE,
    mentioned_user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (comment_id, mentioned_user_id)
);

CREATE TABLE IF NOT EXISTS campus_market_notifications (
    id BIGSERIAL PRIMARY KEY,
    recipient_user_id BIGINT NOT NULL,
    actor_user_id BIGINT NULL,
    item_id BIGINT NULL REFERENCES campus_market_items(id) ON DELETE CASCADE,
    comment_id BIGINT NULL REFERENCES campus_market_comments(id) ON DELETE CASCADE,
    type VARCHAR(40) NOT NULL DEFAULT 'general',
    title VARCHAR(180) NOT NULL,
    message VARCHAR(280) NOT NULL DEFAULT '',
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_market_inquiries (
    id BIGSERIAL PRIMARY KEY,
    item_id BIGINT NOT NULL REFERENCES campus_market_items(id) ON DELETE CASCADE,
    buyer_user_id BIGINT NOT NULL,
    seller_user_id BIGINT NULL,
    message TEXT NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'new',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_market_reports (
    id BIGSERIAL PRIMARY KEY,
    item_id BIGINT NOT NULL REFERENCES campus_market_items(id) ON DELETE CASCADE,
    reporter_user_id BIGINT NOT NULL,
    reason TEXT NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'open',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_market_items_status_created ON campus_market_items(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_market_items_seller ON campus_market_items(seller_user_id);
CREATE INDEX IF NOT EXISTS idx_market_items_category ON campus_market_items(category);
CREATE INDEX IF NOT EXISTS idx_market_items_campus ON campus_market_items(campus);
CREATE INDEX IF NOT EXISTS idx_market_media_item ON campus_market_media(item_id, position);
CREATE INDEX IF NOT EXISTS idx_market_comments_item ON campus_market_comments(item_id, created_at);
CREATE INDEX IF NOT EXISTS idx_market_notifications_user ON campus_market_notifications(recipient_user_id, is_read, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_market_reports_item ON campus_market_reports(item_id, created_at DESC);
