-- Migration 059: Add CrUX field data, security headers, and image format columns
-- These metrics are currently collected live during AI analysis but not persisted
-- Adding DB columns allows displaying them in the SEO audit dashboard

-- CrUX field data (Chrome User Experience Report - real user metrics)
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_lcp_ms INTEGER;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_inp_ms INTEGER;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_cls NUMERIC(6,4);
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_fcp_ms INTEGER;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_ttfb_ms INTEGER;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_lcp_good_pct NUMERIC(5,2);
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_inp_good_pct NUMERIC(5,2);
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS crux_period_end DATE;

-- Security headers
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS has_hsts BOOLEAN;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS has_csp BOOLEAN;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS has_x_frame_options BOOLEAN;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS has_x_content_type_options BOOLEAN;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS security_headers_count SMALLINT;

-- Image format analysis
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS modern_image_count INTEGER;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS legacy_image_count INTEGER;
ALTER TABLE company_website_analysis ADD COLUMN IF NOT EXISTS modern_image_ratio NUMERIC(5,2);

-- Grant permissions to application user
GRANT ALL ON TABLE company_website_analysis TO nordabiz_app;