-- Migration 052: Enhanced GBP Audit with Reviews and Extended Attributes
-- Date: 2026-02-06

-- Review management
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS reviews_with_response INTEGER DEFAULT 0;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS reviews_without_response INTEGER DEFAULT 0;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS review_response_rate NUMERIC(5,2);
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS avg_review_response_days NUMERIC(5,1);
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS review_sentiment JSONB; -- {"positive": 15, "neutral": 3, "negative": 2}
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS reviews_30d INTEGER DEFAULT 0;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS review_keywords JSONB; -- ["profesjonalny", "szybko", "polecam"]

-- Content & activity
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS has_posts BOOLEAN;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS posts_count_30d INTEGER;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS has_products BOOLEAN;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS has_qa BOOLEAN;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS qa_count INTEGER;

-- Enhanced attributes
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS attributes JSONB; -- {"payment": [...], "accessibility": [...], ...}
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS special_hours JSONB;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS has_special_hours BOOLEAN;

-- NAP consistency
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS nap_consistent BOOLEAN;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS nap_issues JSONB; -- [{"field": "phone", "gbp": "...", "website": "..."}]

-- Keywords
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS description_keywords JSONB;
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS keyword_density_score INTEGER; -- 0-100

-- Photo analysis
ALTER TABLE gbp_audits ADD COLUMN IF NOT EXISTS photo_categories JSONB; -- {"interior": 3, "exterior": 2, "team": 1, ...}

-- NEW TABLE: Individual reviews tracking
CREATE TABLE IF NOT EXISTS gbp_reviews (
    id SERIAL PRIMARY KEY,
    company_id INTEGER REFERENCES companies(id) ON DELETE CASCADE,
    google_review_id VARCHAR(255),
    author_name VARCHAR(255),
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    text TEXT,
    publish_time TIMESTAMP,
    has_owner_response BOOLEAN DEFAULT FALSE,
    owner_response_text TEXT,
    owner_response_time TIMESTAMP,
    sentiment VARCHAR(20), -- positive, neutral, negative
    keywords JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(company_id, google_review_id)
);

CREATE INDEX IF NOT EXISTS idx_gbp_reviews_company ON gbp_reviews(company_id);
CREATE INDEX IF NOT EXISTS idx_gbp_reviews_publish ON gbp_reviews(publish_time);
CREATE INDEX IF NOT EXISTS idx_gbp_reviews_sentiment ON gbp_reviews(sentiment);

-- Permissions
GRANT ALL ON TABLE gbp_audits TO nordabiz_app;
GRANT ALL ON TABLE gbp_reviews TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE gbp_reviews_id_seq TO nordabiz_app;