-- SEO Tables Migration for Real Estate CEO Platform
-- This script adds comprehensive SEO monitoring tables to the existing database

-- Create SEO-related enums
CREATE TYPE public.seo_metric_type AS ENUM (
    'clicks',
    'impressions',
    'ctr',
    'position',
    'traffic',
    'bounce_rate',
    'session_duration'
);

CREATE TYPE public.seo_data_source AS ENUM (
    'google_search_console',
    'google_analytics',
    'manual',
    'lighthouse',
    'third_party'
);

CREATE TYPE public.keyword_difficulty AS ENUM (
    'very_easy',
    'easy',
    'medium',
    'hard',
    'very_hard'
);

CREATE TYPE public.page_audit_status AS ENUM (
    'pending',
    'in_progress',
    'completed',
    'failed'
);

-- 1. SEO Search Console Data Table
CREATE TABLE public.seo_search_console_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    date DATE NOT NULL,
    url TEXT NOT NULL,
    query TEXT,
    clicks INTEGER DEFAULT 0,
    impressions INTEGER DEFAULT 0,
    ctr DECIMAL(5,4) DEFAULT 0,
    position DECIMAL(8,2) DEFAULT 0,
    country VARCHAR(2),
    device VARCHAR(20),
    page_type VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Keyword Rankings Table
CREATE TABLE public.seo_keyword_rankings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    keyword TEXT NOT NULL,
    url TEXT,
    position INTEGER,
    previous_position INTEGER,
    search_volume INTEGER,
    difficulty public.keyword_difficulty,
    competition_level DECIMAL(3,2),
    cpc DECIMAL(10,2),
    date DATE NOT NULL,
    listing_id UUID REFERENCES public.listings(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Page SEO Audits Table
CREATE TABLE public.seo_page_audits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    url TEXT NOT NULL,
    title TEXT,
    meta_description TEXT,
    h1_count INTEGER DEFAULT 0,
    h2_count INTEGER DEFAULT 0,
    h3_count INTEGER DEFAULT 0,
    image_count INTEGER DEFAULT 0,
    images_missing_alt INTEGER DEFAULT 0,
    internal_links INTEGER DEFAULT 0,
    external_links INTEGER DEFAULT 0,
    word_count INTEGER DEFAULT 0,
    has_robots_meta BOOLEAN DEFAULT false,
    has_canonical BOOLEAN DEFAULT false,
    has_schema_markup BOOLEAN DEFAULT false,
    load_time_ms INTEGER,
    mobile_friendly BOOLEAN DEFAULT false,
    lighthouse_performance INTEGER,
    lighthouse_accessibility INTEGER,
    lighthouse_best_practices INTEGER,
    lighthouse_seo INTEGER,
    status public.page_audit_status DEFAULT 'pending',
    listing_id UUID REFERENCES public.listings(id) ON DELETE SET NULL,
    audited_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. SEO Metrics Summary Table
CREATE TABLE public.seo_metrics_summary (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    date DATE NOT NULL,
    metric_type public.seo_metric_type NOT NULL,
    value DECIMAL(15,4) NOT NULL,
    previous_value DECIMAL(15,4),
    change_percentage DECIMAL(8,4),
    data_source public.seo_data_source NOT NULL,
    url TEXT,
    listing_id UUID REFERENCES public.listings(id) ON DELETE SET NULL,
    office_id UUID REFERENCES public.offices(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 5. Competitor Analysis Table
CREATE TABLE public.seo_competitor_analysis (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    competitor_domain TEXT NOT NULL,
    competitor_name TEXT,
    keyword TEXT NOT NULL,
    our_position INTEGER,
    competitor_position INTEGER,
    search_volume INTEGER,
    date DATE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 6. Backlinks Monitoring Table
CREATE TABLE public.seo_backlinks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_url TEXT NOT NULL,
    target_url TEXT NOT NULL,
    anchor_text TEXT,
    domain_authority INTEGER,
    page_authority INTEGER,
    spam_score INTEGER,
    follow_type VARCHAR(20) DEFAULT 'follow',
    discovered_date DATE NOT NULL,
    last_seen_date DATE,
    is_active BOOLEAN DEFAULT true,
    listing_id UUID REFERENCES public.listings(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 7. SEO Alerts Table
CREATE TABLE public.seo_alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_type VARCHAR(50) NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    severity VARCHAR(20) DEFAULT 'medium',
    url TEXT,
    metric_value DECIMAL(15,4),
    threshold_value DECIMAL(15,4),
    is_resolved BOOLEAN DEFAULT false,
    resolved_at TIMESTAMP WITH TIME ZONE,
    listing_id UUID REFERENCES public.listings(id) ON DELETE SET NULL,
    office_id UUID REFERENCES public.offices(id) ON DELETE SET NULL,
    user_id UUID REFERENCES public.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 8. SEO Reports Table
CREATE TABLE public.seo_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    report_type VARCHAR(50) NOT NULL,
    title TEXT NOT NULL,
    content JSONB,
    file_path TEXT,
    generated_for_user_id UUID REFERENCES public.users(id) ON DELETE SET NULL,
    generated_for_office_id UUID REFERENCES public.offices(id) ON DELETE SET NULL,
    date_from DATE,
    date_to DATE,
    generated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 10. Sitemap Monitoring Table
CREATE TABLE public.seo_sitemap_monitoring (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sitemap_url TEXT NOT NULL,
    total_urls INTEGER DEFAULT 0,
    indexed_urls INTEGER DEFAULT 0,
    errors_count INTEGER DEFAULT 0,
    warnings_count INTEGER DEFAULT 0,
    last_submitted TIMESTAMP WITH TIME ZONE,
    last_checked TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX idx_seo_search_console_data_date ON public.seo_search_console_data(date);
CREATE INDEX idx_seo_search_console_data_url ON public.seo_search_console_data(url);
CREATE INDEX idx_seo_search_console_data_query ON public.seo_search_console_data(query);
CREATE INDEX idx_seo_search_console_data_date_url ON public.seo_search_console_data(date, url);

CREATE INDEX idx_seo_keyword_rankings_keyword ON public.seo_keyword_rankings(keyword);
CREATE INDEX idx_seo_keyword_rankings_date ON public.seo_keyword_rankings(date);
CREATE INDEX idx_seo_keyword_rankings_position ON public.seo_keyword_rankings(position);
CREATE INDEX idx_seo_keyword_rankings_listing_id ON public.seo_keyword_rankings(listing_id);

CREATE INDEX idx_seo_page_audits_url ON public.seo_page_audits(url);
CREATE INDEX idx_seo_page_audits_listing_id ON public.seo_page_audits(listing_id);
CREATE INDEX idx_seo_page_audits_audited_at ON public.seo_page_audits(audited_at);
CREATE INDEX idx_seo_page_audits_status ON public.seo_page_audits(status);

CREATE INDEX idx_seo_metrics_summary_date ON public.seo_metrics_summary(date);
CREATE INDEX idx_seo_metrics_summary_metric_type ON public.seo_metrics_summary(metric_type);
CREATE INDEX idx_seo_metrics_summary_listing_id ON public.seo_metrics_summary(listing_id);
CREATE INDEX idx_seo_metrics_summary_office_id ON public.seo_metrics_summary(office_id);

CREATE INDEX idx_seo_competitor_analysis_keyword ON public.seo_competitor_analysis(keyword);
CREATE INDEX idx_seo_competitor_analysis_date ON public.seo_competitor_analysis(date);
CREATE INDEX idx_seo_competitor_analysis_competitor_domain ON public.seo_competitor_analysis(competitor_domain);

CREATE INDEX idx_seo_backlinks_target_url ON public.seo_backlinks(target_url);
CREATE INDEX idx_seo_backlinks_source_url ON public.seo_backlinks(source_url);
CREATE INDEX idx_seo_backlinks_listing_id ON public.seo_backlinks(listing_id);
CREATE INDEX idx_seo_backlinks_is_active ON public.seo_backlinks(is_active);

CREATE INDEX idx_seo_alerts_alert_type ON public.seo_alerts(alert_type);
CREATE INDEX idx_seo_alerts_is_resolved ON public.seo_alerts(is_resolved);
CREATE INDEX idx_seo_alerts_user_id ON public.seo_alerts(user_id);
CREATE INDEX idx_seo_alerts_office_id ON public.seo_alerts(office_id);
CREATE INDEX idx_seo_alerts_listing_id ON public.seo_alerts(listing_id);

CREATE INDEX idx_seo_reports_report_type ON public.seo_reports(report_type);
CREATE INDEX idx_seo_reports_generated_at ON public.seo_reports(generated_at);
CREATE INDEX idx_seo_reports_user_id ON public.seo_reports(generated_for_user_id);
CREATE INDEX idx_seo_reports_office_id ON public.seo_reports(generated_for_office_id);

-- Create indexes for new listing columns
CREATE INDEX IF NOT EXISTS idx_listings_seo_score ON public.listings(seo_score);
CREATE INDEX IF NOT EXISTS idx_listings_last_seo_audit ON public.listings(last_seo_audit);

COMMENT ON TABLE public.seo_search_console_data IS 'Stores Google Search Console performance data';
COMMENT ON TABLE public.seo_keyword_rankings IS 'Tracks keyword rankings for listings';
COMMENT ON TABLE public.seo_page_audits IS 'Stores SEO audit results for pages';
COMMENT ON TABLE public.seo_metrics_summary IS 'Aggregated SEO metrics for reporting';
COMMENT ON TABLE public.seo_competitor_analysis IS 'Competitor keyword analysis data';
COMMENT ON TABLE public.seo_backlinks IS 'Backlink monitoring and analysis';
COMMENT ON TABLE public.seo_alerts IS 'SEO alerts and notifications';
COMMENT ON TABLE public.seo_reports IS 'Generated SEO reports';
COMMENT ON TABLE public.seo_sitemap_monitoring IS 'Sitemap submission and monitoring';

-- Grant permissions (adjust as needed for your user roles)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_app_user;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO your_app_user; 