init-db.sql

-- Database initialization script for Solidity Security Scanner
-- Run this on first database creation

-- Create enum types
CREATE TYPE subscription_status AS ENUM (
    'none', 'active', 'past_due', 'canceled', 'trialing', 'incomplete'
);

CREATE TYPE billing_tier AS ENUM (
    'free',
    'pro_monthly', 'pro_yearly',
    'team_monthly', 'team_yearly',
    'enterprise_monthly', 'enterprise_yearly'
);

-- Create customers table (if not exists via SQLAlchemy)
-- This is handled by SQLAlchemy models, but adding indexes and constraints here

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_customers_email ON customers(email);
CREATE INDEX IF NOT EXISTS idx_customers_wallet ON customers(wallet_address);
CREATE INDEX IF NOT EXISTS idx_customers_stripe ON customers(stripe_customer_id);
CREATE INDEX IF NOT EXISTS idx_customers_enterprise_key ON customers(enterprise_key);
CREATE INDEX IF NOT EXISTS idx_customers_tier ON customers(tier);

CREATE INDEX IF NOT EXISTS idx_scan_jobs_customer ON scan_jobs(customer_id);
CREATE INDEX IF NOT EXISTS idx_scan_jobs_status ON scan_jobs(status);
CREATE INDEX IF NOT EXISTS idx_scan_jobs_created ON scan_jobs(created_at);

CREATE INDEX IF NOT EXISTS idx_webhook_events_source ON webhook_events(source);
CREATE INDEX IF NOT EXISTS idx_webhook_events_processed ON webhook_events(processed);
CREATE INDEX IF NOT EXISTS idx_webhook_events_created ON webhook_events(created_at);

-- Add updated_at trigger for customers
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

DROP TRIGGER IF EXISTS update_customers_updated_at ON customers;
CREATE TRIGGER update_customers_updated_at
    BEFORE UPDATE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Add updated_at trigger for scan_jobs
DROP TRIGGER IF EXISTS update_scan_jobs_updated_at ON scan_jobs;
CREATE TRIGGER update_scan_jobs_updated_at
    BEFORE UPDATE ON scan_jobs
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- View for active subscriptions
CREATE OR REPLACE VIEW active_subscriptions AS
SELECT 
    c.id,
    c.email,
    c.tier,
    c.subscription_status,
    c.scans_used,
    c.scans_limit,
    c.current_period_end,
    c.created_at
FROM customers c
WHERE c.subscription_status IN ('active', 'trialing')
  AND c.tier != 'free';

-- View for scan analytics
CREATE OR REPLACE VIEW scan_analytics AS
SELECT 
    DATE_TRUNC('day', sj.created_at) as date,
    COUNT(*) as total_scans,
    COUNT(*) FILTER (WHERE sj.status = 'completed') as completed_scans,
    COUNT(*) FILTER (WHERE sj.status = 'failed') as failed_scans,
    AVG(CASE WHEN sj.gas_savings IS NOT NULL THEN sj.gas_savings END) as avg_gas_savings
FROM scan_jobs sj
GROUP BY DATE_TRUNC('day', sj.created_at)
ORDER BY date DESC;

-- Function to get customer by enterprise key
CREATE OR REPLACE FUNCTION get_customer_by_enterprise_key(p_key TEXT)
RETURNS SETOF customers AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM customers WHERE enterprise_key = p_key;
END;
$$ LANGUAGE plpgsql;

-- Function to check and increment scan usage atomically
CREATE OR REPLACE FUNCTION check_and_increment_scan(p_customer_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    v_scans_used INTEGER;
    v_scans_limit INTEGER;
BEGIN
    SELECT scans_used, scans_limit INTO v_scans_used, v_scans_limit
    FROM customers WHERE id = p_customer_id;
    
    IF v_scans_limit = -1 THEN
        RETURN TRUE; -- Unlimited
    END IF;
    
    IF v_scans_used >= v_scans_limit THEN
        RETURN FALSE; -- Limit reached
    END IF;
    
    UPDATE customers SET scans_used = scans_used + 1, updated_at = NOW()
    WHERE id = p_customer_id;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;