-- Migration 053: Enhanced SEO Audit with Local SEO and Citations
-- Date: 2026-02-06

-- Local SEO
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS local_seo_score INTEGER; -- 0-100
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS has_local_business_schema BOOLEAN;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS local_business_schema_fields JSONB; -- {"name": true, "address": true, ...}
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS nap_on_website JSONB; -- {"name": "...", "address": "...", "phone": "..."}
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS has_google_maps_embed BOOLEAN;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS has_local_keywords BOOLEAN;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS local_keywords_found JSONB; -- ["hydraulik wejherowo", ...]

-- Citations
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS citations_found JSONB; -- [{"directory": "panoramafirm.pl", "url": "...", "status": "found"}]
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS citations_count INTEGER DEFAULT 0;

-- Content freshness
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS content_freshness_score INTEGER; -- 0-100
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS last_content_update TIMESTAMP;

-- Score history
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS score_history JSONB; -- [{"date": "2026-02-01", "score": 72}, ...]

-- NEW TABLE: Company citations in local directories
CREATE TABLE IF NOT EXISTS company_citations (
    id SERIAL PRIMARY KEY,
    company_id INTEGER REFERENCES companies(id) ON DELETE CASCADE,
    directory_name VARCHAR(100) NOT NULL,
    directory_url VARCHAR(500),
    listing_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'unknown', -- found, not_found, incorrect
    nap_accurate BOOLEAN,
    details JSONB, -- extra info from the listing
    checked_at TIMESTAMP DEFAULT NOW(),
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(company_id, directory_name)
);

CREATE INDEX IF NOT EXISTS idx_company_citations_company ON company_citations(company_id);
CREATE INDEX IF NOT EXISTS idx_company_citations_status ON company_citations(status);

-- Permissions
GRANT ALL ON TABLE company_website_analysis TO nordabiz_app;
GRANT ALL ON TABLE company_citations TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE company_citations_id_seq TO nordabiz_app;