-- ============================================================
-- Digital Maturity Assessment Platform - Foundation
-- Migration 002: ETAP 1 - Core Tables
-- ============================================================
-- Created: 2025-11-26
-- Description:
--   - Extends companies table with digital maturity fields
--   - Creates company_digital_maturity (dashboard/composite scores)
--   - Creates company_website_analysis (website & online presence)
--   - Creates maturity_assessments (historical tracking)
--
-- Usage:
--   PostgreSQL: psql -h localhost -U nordabiz_app -d nordabiz -f 002_digital_maturity_foundation.sql
--   SQLite: sqlite3 nordabiz_local.db < 002_digital_maturity_foundation.sql
-- ============================================================

-- ============================================================
-- 1. EXTEND companies TABLE
-- ============================================================

ALTER TABLE companies ADD COLUMN IF NOT EXISTS digital_maturity_last_assessed TIMESTAMP;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS digital_maturity_score INTEGER;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS digital_maturity_rank_category INTEGER;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS digital_maturity_rank_overall INTEGER;

-- AI Readiness (basic fields)
ALTER TABLE companies ADD COLUMN IF NOT EXISTS ai_enabled BOOLEAN DEFAULT FALSE;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS ai_tools_used TEXT[]; -- PostgreSQL array, TEXT for SQLite
ALTER TABLE companies ADD COLUMN IF NOT EXISTS data_structured BOOLEAN DEFAULT FALSE;

-- IT Management
ALTER TABLE companies ADD COLUMN IF NOT EXISTS it_manager_exists BOOLEAN DEFAULT FALSE;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS it_outsourced BOOLEAN DEFAULT FALSE;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS it_provider_company_id INTEGER REFERENCES companies(id);

-- Website tracking
ALTER TABLE companies ADD COLUMN IF NOT EXISTS website_last_analyzed TIMESTAMP;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS website_status VARCHAR(20);
ALTER TABLE companies ADD COLUMN IF NOT EXISTS website_quality_score INTEGER;

COMMENT ON COLUMN companies.digital_maturity_score IS 'Composite score 0-100 across all digital maturity areas';
COMMENT ON COLUMN companies.website_quality_score IS 'Website quality score 0-100';


-- ============================================================
-- 2. CREATE company_digital_maturity TABLE
-- ============================================================

CREATE TABLE IF NOT EXISTS company_digital_maturity (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- === COMPOSITE SCORES (0-100 each) ===
    overall_score INTEGER,
    online_presence_score INTEGER,
    social_media_score INTEGER,
    it_infrastructure_score INTEGER,
    business_applications_score INTEGER,
    backup_disaster_recovery_score INTEGER,
    cybersecurity_score INTEGER,
    ai_readiness_score INTEGER,
    digital_marketing_score INTEGER,

    -- === GAPS & OPPORTUNITIES ===
    critical_gaps TEXT[], -- Array of strings
    improvement_priority VARCHAR(20), -- 'critical', 'high', 'medium', 'low'
    estimated_investment_needed NUMERIC(10, 2),

    -- === BENCHMARKING ===
    rank_in_category INTEGER,
    rank_overall INTEGER,
    percentile INTEGER, -- top X% of companies

    -- === SALES INTELLIGENCE ===
    total_opportunity_value NUMERIC(10, 2),
    sales_readiness VARCHAR(20), -- 'hot', 'warm', 'cold', 'not_ready'

    -- Constraints
    UNIQUE(company_id)
);

CREATE INDEX IF NOT EXISTS idx_digital_maturity_company ON company_digital_maturity(company_id);
CREATE INDEX IF NOT EXISTS idx_digital_maturity_updated ON company_digital_maturity(last_updated);
CREATE INDEX IF NOT EXISTS idx_digital_maturity_overall_score ON company_digital_maturity(overall_score);

COMMENT ON TABLE company_digital_maturity IS 'Central dashboard for company digital maturity - composite scores and benchmarking';


-- ============================================================
-- 3. CREATE company_website_analysis TABLE
-- ============================================================

CREATE TABLE IF NOT EXISTS company_website_analysis (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- === BASIC INFO ===
    website_url VARCHAR(500),
    final_url VARCHAR(500), -- After redirects
    http_status_code INTEGER,
    load_time_ms INTEGER,

    -- === TECHNICAL ===
    has_ssl BOOLEAN DEFAULT FALSE,
    ssl_expires_at DATE,
    is_responsive BOOLEAN DEFAULT FALSE, -- mobile-friendly
    cms_detected VARCHAR(100),
    frameworks_detected TEXT[], -- ['WordPress', 'Bootstrap', etc.]

    -- === CONTENT RICHNESS ===
    content_richness_score INTEGER, -- 1-10
    page_count_estimate INTEGER,
    word_count_homepage INTEGER,
    has_blog BOOLEAN DEFAULT FALSE,
    has_portfolio BOOLEAN DEFAULT FALSE,
    has_contact_form BOOLEAN DEFAULT FALSE,
    has_live_chat BOOLEAN DEFAULT FALSE,

    -- === EXTRACTED CONTENT ===
    content_summary TEXT, -- AI-generated summary from website
    services_extracted TEXT[], -- Services mentioned on website
    main_keywords TEXT[], -- Top keywords

    -- === SEO ===
    seo_title VARCHAR(500),
    seo_description TEXT,
    has_sitemap BOOLEAN DEFAULT FALSE,
    has_robots_txt BOOLEAN DEFAULT FALSE,
    google_indexed_pages INTEGER,

    -- === DOMAIN ===
    domain_registered_at DATE,
    domain_expires_at DATE,
    domain_age_years INTEGER,

    -- === ANALYTICS ===
    has_google_analytics BOOLEAN DEFAULT FALSE,
    has_google_tag_manager BOOLEAN DEFAULT FALSE,
    has_facebook_pixel BOOLEAN DEFAULT FALSE,

    -- === OPPORTUNITY SCORING ===
    needs_redesign BOOLEAN DEFAULT FALSE,
    missing_features TEXT[], -- ['blog', 'portfolio', 'ssl', etc.]
    opportunity_score INTEGER, -- 0-100
    estimated_project_value NUMERIC(10, 2), -- PLN
    opportunity_notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_website_analysis_company ON company_website_analysis(company_id);
CREATE INDEX IF NOT EXISTS idx_website_analysis_date ON company_website_analysis(analyzed_at);
CREATE INDEX IF NOT EXISTS idx_website_analysis_opportunity ON company_website_analysis(opportunity_score);

COMMENT ON TABLE company_website_analysis IS 'Detailed website and online presence analysis';
COMMENT ON COLUMN company_website_analysis.content_summary IS 'AI-extracted summary of what the company does based on website';
COMMENT ON COLUMN company_website_analysis.opportunity_score IS 'Sales opportunity score 0-100 for website redesign/improvement';


-- ============================================================
-- 4. CREATE maturity_assessments TABLE (Historical Tracking)
-- ============================================================

CREATE TABLE IF NOT EXISTS maturity_assessments (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    assessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assessed_by_user_id INTEGER REFERENCES users(id),
    assessment_type VARCHAR(50), -- 'full', 'quick', 'self_reported', 'audit'

    -- === SNAPSHOT OF SCORES ===
    overall_score INTEGER,
    online_presence_score INTEGER,
    social_media_score INTEGER,
    it_infrastructure_score INTEGER,
    business_applications_score INTEGER,
    backup_dr_score INTEGER,
    cybersecurity_score INTEGER,
    ai_readiness_score INTEGER,

    -- === CHANGES SINCE LAST ASSESSMENT ===
    score_change INTEGER, -- +5, -3, etc.
    areas_improved TEXT[], -- ['cybersecurity', 'backup']
    areas_declined TEXT[], -- ['social_media']

    notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_maturity_assessments_company ON maturity_assessments(company_id);
CREATE INDEX IF NOT EXISTS idx_maturity_assessments_date ON maturity_assessments(assessed_at);

COMMENT ON TABLE maturity_assessments IS 'Historical tracking of digital maturity scores over time';


-- ============================================================
-- 5. CREATE HELPER VIEWS
-- ============================================================

-- View: Companies with low digital maturity (potential clients)
CREATE OR REPLACE VIEW v_low_maturity_companies AS
SELECT
    c.id,
    c.name,
    c.slug,
    c.category_id,
    cat.name as category_name,
    c.website,
    dm.overall_score,
    dm.critical_gaps,
    dm.total_opportunity_value,
    dm.sales_readiness
FROM companies c
LEFT JOIN company_digital_maturity dm ON c.id = dm.company_id
LEFT JOIN categories cat ON c.category_id = cat.id
WHERE dm.overall_score < 50
ORDER BY dm.overall_score ASC;

COMMENT ON VIEW v_low_maturity_companies IS 'Companies with digital maturity score below 50 - sales opportunities';


-- View: Digital maturity leaderboard
CREATE OR REPLACE VIEW v_maturity_leaderboard AS
SELECT
    c.id,
    c.name,
    c.slug,
    cat.name as category_name,
    dm.overall_score,
    dm.rank_in_category,
    dm.rank_overall,
    dm.percentile,
    dm.last_updated
FROM companies c
LEFT JOIN company_digital_maturity dm ON c.id = dm.company_id
LEFT JOIN categories cat ON c.category_id = cat.id
WHERE dm.overall_score IS NOT NULL
ORDER BY dm.overall_score DESC;

COMMENT ON VIEW v_maturity_leaderboard IS 'Digital maturity rankings - leaderboard for gamification';


-- ============================================================
-- 6. SAMPLE DATA / DEFAULTS
-- ============================================================

-- Initialize digital_maturity records for all existing companies
INSERT INTO company_digital_maturity (company_id, overall_score)
SELECT id, 0 FROM companies
ON CONFLICT (company_id) DO NOTHING;

COMMENT ON TABLE company_digital_maturity IS 'All companies should have a record here, even if scores are 0 initially';


-- ============================================================
-- MIGRATION COMPLETE
-- ============================================================

-- Verify tables created
DO $$
BEGIN
    RAISE NOTICE 'Migration 002 completed successfully!';
    RAISE NOTICE 'Tables created:';
    RAISE NOTICE '  - company_digital_maturity';
    RAISE NOTICE '  - company_website_analysis';
    RAISE NOTICE '  - maturity_assessments';
    RAISE NOTICE 'Views created:';
    RAISE NOTICE '  - v_low_maturity_companies';
    RAISE NOTICE '  - v_maturity_leaderboard';
END $$;
