-- Domain Check Database Initialization Script
-- Version: 1.0.0
-- Date: 2026-01-29

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For similarity searches

-- Set timezone
SET TIME ZONE 'UTC';

-- ============================================================================
-- TABLES
-- ============================================================================

-- 1. Domains table
CREATE TABLE IF NOT EXISTS domains (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain VARCHAR(255) NOT NULL,
    subdomain VARCHAR(255),
    tld VARCHAR(50) NOT NULL,
    full_domain VARCHAR(255) UNIQUE NOT NULL, -- Complete domain (subdomain.domain.tld or domain.tld)
    first_seen_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_checked_at TIMESTAMP WITH TIME ZONE,
    check_count INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_domains_full_domain ON domains(full_domain);
CREATE INDEX idx_domains_domain ON domains(domain);
CREATE INDEX idx_domains_last_checked ON domains(last_checked_at);
CREATE INDEX idx_domains_tld ON domains(tld);
CREATE INDEX idx_domains_is_active ON domains(is_active);

-- 2. WHOIS Records table
CREATE TABLE IF NOT EXISTS whois_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    creation_date TIMESTAMP WITH TIME ZONE,
    expiration_date TIMESTAMP WITH TIME ZONE,
    updated_date TIMESTAMP WITH TIME ZONE,
    registrar VARCHAR(255),
    registrar_url VARCHAR(500),
    registrant_org VARCHAR(255),
    registrant_country VARCHAR(2),
    admin_email VARCHAR(255),
    name_servers TEXT[], -- Array of name servers
    status TEXT[], -- Array of domain statuses
    dnssec BOOLEAN,
    raw_whois_data JSONB,
    data_source VARCHAR(50) DEFAULT 'rdap', -- 'rdap', 'whoxy', 'manual'
    fetched_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_whois_domain_id ON whois_records(domain_id);
CREATE INDEX idx_whois_creation_date ON whois_records(creation_date);
CREATE INDEX idx_whois_registrar ON whois_records(registrar);
CREATE INDEX idx_whois_data_source ON whois_records(data_source);
CREATE INDEX idx_whois_fetched_at ON whois_records(fetched_at DESC);

-- 3. DNS Records table
CREATE TABLE IF NOT EXISTS dns_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    record_type VARCHAR(10) NOT NULL, -- 'A', 'AAAA', 'MX', 'TXT', 'NS', 'CNAME', 'SOA'
    record_value TEXT NOT NULL,
    ttl INTEGER,
    priority INTEGER, -- For MX records
    fetched_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_dns_domain_id ON dns_records(domain_id);
CREATE INDEX idx_dns_domain_type ON dns_records(domain_id, record_type);
CREATE INDEX idx_dns_record_type ON dns_records(record_type);
CREATE INDEX idx_dns_fetched_at ON dns_records(fetched_at DESC);

-- 4. SSL Certificates table
CREATE TABLE IF NOT EXISTS ssl_certificates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    issuer VARCHAR(255),
    subject VARCHAR(255),
    valid_from TIMESTAMP WITH TIME ZONE,
    valid_until TIMESTAMP WITH TIME ZONE,
    serial_number VARCHAR(255),
    signature_algorithm VARCHAR(100),
    key_size INTEGER,
    is_wildcard BOOLEAN DEFAULT FALSE,
    is_self_signed BOOLEAN DEFAULT FALSE,
    is_valid BOOLEAN DEFAULT TRUE,
    certificate_chain JSONB,
    fetched_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_ssl_domain_id ON ssl_certificates(domain_id);
CREATE INDEX idx_ssl_valid_until ON ssl_certificates(valid_until);
CREATE INDEX idx_ssl_issuer ON ssl_certificates(issuer);
CREATE INDEX idx_ssl_is_valid ON ssl_certificates(is_valid);
CREATE INDEX idx_ssl_fetched_at ON ssl_certificates(fetched_at DESC);

-- 5. Reputation Scores table
CREATE TABLE IF NOT EXISTS reputation_scores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    source VARCHAR(50) NOT NULL, -- 'virustotal', 'custom', 'opensquat', 'phishtank'
    score INTEGER CHECK (score >= 0 AND score <= 100),
    malicious_count INTEGER DEFAULT 0,
    suspicious_count INTEGER DEFAULT 0,
    harmless_count INTEGER DEFAULT 0,
    undetected_count INTEGER DEFAULT 0,
    is_blacklisted BOOLEAN DEFAULT FALSE,
    blacklist_names TEXT[],
    is_typosquatting BOOLEAN DEFAULT FALSE,
    typosquatting_target VARCHAR(255),
    raw_response JSONB,
    checked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    -- Note: UNIQUE constraint on domain_id + source + date handled at application level
);

CREATE INDEX idx_reputation_domain_id ON reputation_scores(domain_id);
CREATE INDEX idx_reputation_source ON reputation_scores(source);
CREATE INDEX idx_reputation_is_blacklisted ON reputation_scores(is_blacklisted);
CREATE INDEX idx_reputation_is_typosquatting ON reputation_scores(is_typosquatting);
CREATE INDEX idx_reputation_checked_at ON reputation_scores(checked_at DESC);

-- 6. Risk Assessments table
CREATE TABLE IF NOT EXISTS risk_assessments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    check_id UUID UNIQUE NOT NULL,
    total_score INTEGER NOT NULL CHECK (total_score >= 0 AND total_score <= 100),
    risk_level VARCHAR(20) NOT NULL CHECK (risk_level IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),

    -- Individual factor scores
    domain_age_score INTEGER DEFAULT 0,
    domain_age_days INTEGER,
    ssl_score INTEGER DEFAULT 0,
    dns_score INTEGER DEFAULT 0,
    reputation_score INTEGER DEFAULT 0,
    whois_score INTEGER DEFAULT 0,

    -- Risk factors breakdown (JSONB array)
    factors JSONB,

    -- Flags
    is_new_domain BOOLEAN DEFAULT FALSE,
    is_suspicious BOOLEAN DEFAULT FALSE,
    requires_manual_review BOOLEAN DEFAULT FALSE,

    assessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_risk_domain_id ON risk_assessments(domain_id);
CREATE INDEX idx_risk_check_id ON risk_assessments(check_id);
CREATE INDEX idx_risk_level ON risk_assessments(risk_level);
CREATE INDEX idx_risk_total_score ON risk_assessments(total_score);
CREATE INDEX idx_risk_is_new_domain ON risk_assessments(is_new_domain);
CREATE INDEX idx_risk_is_suspicious ON risk_assessments(is_suspicious);
CREATE INDEX idx_risk_assessed_at ON risk_assessments(assessed_at DESC);

-- 7. Check History table
CREATE TABLE IF NOT EXISTS check_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    check_id UUID UNIQUE NOT NULL,
    domain_id UUID NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
    risk_assessment_id UUID REFERENCES risk_assessments(id) ON DELETE SET NULL,

    -- Check metadata
    requested_by VARCHAR(100) DEFAULT 'api', -- 'api', 'cli', 'dashboard', 'batch'
    request_ip VARCHAR(45),
    user_agent TEXT,
    check_options JSONB,

    -- Performance metrics
    processing_time_ms INTEGER,
    cache_hit BOOLEAN DEFAULT FALSE,

    -- Changes detection
    changes_detected BOOLEAN DEFAULT FALSE,
    change_summary JSONB,

    -- Status
    status VARCHAR(20) DEFAULT 'completed', -- 'pending', 'processing', 'completed', 'failed'
    error_message TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_check_history_check_id ON check_history(check_id);
CREATE INDEX idx_check_history_domain_id ON check_history(domain_id);
CREATE INDEX idx_check_history_domain_created ON check_history(domain_id, created_at DESC);
CREATE INDEX idx_check_history_created_at ON check_history(created_at DESC);
CREATE INDEX idx_check_history_status ON check_history(status);
CREATE INDEX idx_check_history_requested_by ON check_history(requested_by);

-- 8. Batch Operations table
CREATE TABLE IF NOT EXISTS batch_operations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id UUID UNIQUE NOT NULL,
    total_domains INTEGER NOT NULL,
    completed_count INTEGER DEFAULT 0,
    failed_count INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'cancelled')),
    priority VARCHAR(20) DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),

    -- Timing
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    estimated_completion_at TIMESTAMP WITH TIME ZONE,

    -- Metadata
    requested_by VARCHAR(100),
    check_options JSONB,
    domain_list TEXT[], -- Array of domains to check

    -- Results
    error_log JSONB,
    summary JSONB,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_batch_batch_id ON batch_operations(batch_id);
CREATE INDEX idx_batch_status ON batch_operations(status);
CREATE INDEX idx_batch_priority ON batch_operations(priority);
CREATE INDEX idx_batch_created_at ON batch_operations(created_at DESC);

-- 9. Blacklists table
CREATE TABLE IF NOT EXISTS blacklists (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    domain VARCHAR(255) UNIQUE NOT NULL,
    reason TEXT,
    category VARCHAR(50), -- 'phishing', 'malware', 'spam', 'fake_news', 'disinformation'
    source VARCHAR(100),
    severity VARCHAR(20) DEFAULT 'medium' CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT TRUE,
    expires_at TIMESTAMP WITH TIME ZONE,

    -- Additional metadata
    metadata JSONB
);

CREATE INDEX idx_blacklist_domain ON blacklists(domain);
CREATE INDEX idx_blacklist_category ON blacklists(category);
CREATE INDEX idx_blacklist_is_active ON blacklists(is_active);
CREATE INDEX idx_blacklist_severity ON blacklists(severity);

-- 10. API Usage Logs table
CREATE TABLE IF NOT EXISTS api_usage_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    service_name VARCHAR(50) NOT NULL, -- 'whoxy', 'virustotal', 'rdap'
    endpoint VARCHAR(255),
    request_count INTEGER DEFAULT 1,
    response_time_ms INTEGER,
    status_code INTEGER,
    quota_used INTEGER,
    quota_remaining INTEGER,
    error_message TEXT,
    request_params JSONB,
    logged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_api_usage_service ON api_usage_logs(service_name);
CREATE INDEX idx_api_usage_service_logged ON api_usage_logs(service_name, logged_at DESC);
CREATE INDEX idx_api_usage_logged_at ON api_usage_logs(logged_at DESC);
CREATE INDEX idx_api_usage_status ON api_usage_logs(status_code);

-- ============================================================================
-- VIEWS
-- ============================================================================

-- View for latest domain information
CREATE OR REPLACE VIEW v_latest_domain_info AS
SELECT
    d.id,
    d.full_domain,
    d.domain,
    d.subdomain,
    d.tld,
    d.first_seen_at,
    d.last_checked_at,
    d.check_count,
    ra.total_score as latest_risk_score,
    ra.risk_level as latest_risk_level,
    ra.is_new_domain,
    ra.is_suspicious,
    w.creation_date as domain_creation_date,
    w.registrar,
    w.registrant_country,
    EXTRACT(DAY FROM (NOW() - w.creation_date)) as domain_age_days
FROM domains d
LEFT JOIN LATERAL (
    SELECT * FROM risk_assessments
    WHERE domain_id = d.id
    ORDER BY assessed_at DESC
    LIMIT 1
) ra ON TRUE
LEFT JOIN LATERAL (
    SELECT * FROM whois_records
    WHERE domain_id = d.id
    ORDER BY fetched_at DESC
    LIMIT 1
) w ON TRUE;

-- View for high-risk domains
CREATE OR REPLACE VIEW v_high_risk_domains AS
SELECT
    d.full_domain,
    ra.total_score,
    ra.risk_level,
    ra.is_new_domain,
    ra.domain_age_days,
    ra.assessed_at,
    rs.is_blacklisted,
    rs.is_typosquatting
FROM domains d
JOIN risk_assessments ra ON d.id = ra.domain_id
LEFT JOIN reputation_scores rs ON d.id = rs.domain_id
WHERE ra.risk_level IN ('HIGH', 'CRITICAL')
ORDER BY ra.total_score DESC, ra.assessed_at DESC;

-- View for daily statistics
CREATE OR REPLACE VIEW v_daily_stats AS
SELECT
    DATE(created_at) as check_date,
    COUNT(DISTINCT domain_id) as unique_domains_checked,
    COUNT(*) as total_checks,
    AVG(processing_time_ms) as avg_processing_time_ms,
    SUM(CASE WHEN cache_hit THEN 1 ELSE 0 END) as cache_hits,
    SUM(CASE WHEN NOT cache_hit THEN 1 ELSE 0 END) as cache_misses,
    ROUND(100.0 * SUM(CASE WHEN cache_hit THEN 1 ELSE 0 END) / COUNT(*), 2) as cache_hit_rate
FROM check_history
WHERE status = 'completed'
GROUP BY DATE(created_at)
ORDER BY check_date DESC;

-- ============================================================================
-- FUNCTIONS
-- ============================================================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for updated_at
CREATE TRIGGER update_domains_updated_at BEFORE UPDATE ON domains
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_blacklists_updated_at BEFORE UPDATE ON blacklists
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function to increment domain check count
CREATE OR REPLACE FUNCTION increment_domain_check_count()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE domains
    SET check_count = check_count + 1,
        last_checked_at = NOW()
    WHERE id = NEW.domain_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-increment check count
CREATE TRIGGER increment_check_count AFTER INSERT ON check_history
    FOR EACH ROW EXECUTE FUNCTION increment_domain_check_count();

-- ============================================================================
-- INITIAL DATA
-- ============================================================================

-- Insert some common blacklist entries (example)
INSERT INTO blacklists (domain, reason, category, source, severity) VALUES
    ('known-phishing-site.com', 'Known phishing operation', 'phishing', 'manual', 'critical'),
    ('fake-news-urgent.com', 'Disinformation campaign', 'fake_news', 'manual', 'high'),
    ('malware-distribution.com', 'Malware distribution', 'malware', 'manual', 'critical')
ON CONFLICT (domain) DO NOTHING;

-- ============================================================================
-- GRANTS
-- ============================================================================

-- Grant permissions (adjust as needed)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dns_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dns_admin;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dns_admin;

-- ============================================================================
-- COMPLETION
-- ============================================================================

-- Log initialization
DO $$
BEGIN
    RAISE NOTICE 'Database initialization completed successfully!';
    RAISE NOTICE 'Total tables created: 10';
    RAISE NOTICE 'Total views created: 3';
    RAISE NOTICE 'Total functions created: 2';
END $$;