-- PostgreSQL initialization script for Ad-tlas application
-- This script sets up the database, users, and initial configuration

-- Create application database
CREATE DATABASE adtlas_db;

-- Create application user
CREATE USER adtlas_user WITH PASSWORD 'adtlas_password';

-- Grant privileges to application user
GRANT ALL PRIVILEGES ON DATABASE adtlas_db TO adtlas_user;

-- Connect to the application database
\c adtlas_db;

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO adtlas_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO adtlas_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO adtlas_user;

-- Set default privileges for future tables and sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO adtlas_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO adtlas_user;

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
CREATE EXTENSION IF NOT EXISTS "btree_gist";
CREATE EXTENSION IF NOT EXISTS "hstore";
CREATE EXTENSION IF NOT EXISTS "citext";

-- Create custom types for the application
CREATE TYPE campaign_status AS ENUM ('draft', 'active', 'paused', 'completed', 'cancelled');
CREATE TYPE advertiser_status AS ENUM ('active', 'inactive', 'suspended', 'pending');
CREATE TYPE channel_type AS ENUM ('web', 'mobile', 'social', 'email', 'display', 'video', 'search');
CREATE TYPE activity_type AS ENUM ('login', 'logout', 'create', 'update', 'delete', 'view', 'export', 'import');
CREATE TYPE notification_type AS ENUM ('info', 'warning', 'error', 'success');
CREATE TYPE user_role AS ENUM ('admin', 'manager', 'analyst', 'viewer');

-- Create indexes for better performance
-- Note: These will be created by Django migrations, but we can prepare the database

-- Create a function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create a function to generate slugs
CREATE OR REPLACE FUNCTION generate_slug(input_text TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN lower(regexp_replace(regexp_replace(trim(input_text), '[^a-zA-Z0-9\s-]', '', 'g'), '\s+', '-', 'g'));
END;
$$ LANGUAGE plpgsql;

-- Create a function for full-text search
CREATE OR REPLACE FUNCTION create_search_vector(title TEXT, description TEXT, tags TEXT DEFAULT '')
RETURNS tsvector AS $$
BEGIN
    RETURN to_tsvector('english', 
        COALESCE(title, '') || ' ' || 
        COALESCE(description, '') || ' ' || 
        COALESCE(tags, '')
    );
END;
$$ LANGUAGE plpgsql;

-- Create a function to validate email addresses
CREATE OR REPLACE FUNCTION is_valid_email(email TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END;
$$ LANGUAGE plpgsql;

-- Create a function to calculate campaign ROI
CREATE OR REPLACE FUNCTION calculate_roi(revenue DECIMAL, cost DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
    IF cost = 0 OR cost IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN ROUND(((revenue - cost) / cost * 100), 2);
END;
$$ LANGUAGE plpgsql;

-- Create a function to get date range statistics
CREATE OR REPLACE FUNCTION get_date_range_stats(start_date DATE, end_date DATE)
RETURNS TABLE(
    total_days INTEGER,
    business_days INTEGER,
    weekends INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        (end_date - start_date + 1)::INTEGER as total_days,
        (SELECT COUNT(*) FROM generate_series(start_date, end_date, '1 day'::interval) d 
         WHERE EXTRACT(dow FROM d) NOT IN (0, 6))::INTEGER as business_days,
        (SELECT COUNT(*) FROM generate_series(start_date, end_date, '1 day'::interval) d 
         WHERE EXTRACT(dow FROM d) IN (0, 6))::INTEGER as weekends;
END;
$$ LANGUAGE plpgsql;

-- Create audit log table for tracking changes
CREATE TABLE IF NOT EXISTS audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INTEGER,
    action VARCHAR(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values JSONB,
    new_values JSONB,
    user_id INTEGER,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for audit log
CREATE INDEX IF NOT EXISTS idx_audit_log_table_name ON audit_log(table_name);
CREATE INDEX IF NOT EXISTS idx_audit_log_record_id ON audit_log(record_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action);
CREATE INDEX IF NOT EXISTS idx_audit_log_user_id ON audit_log(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_created_at ON audit_log(created_at);

-- Create a generic audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
BEGIN
    IF TG_OP = 'DELETE' THEN
        old_data = to_jsonb(OLD);
        new_data = NULL;
        INSERT INTO audit_log (table_name, record_id, action, old_values, new_values)
        VALUES (TG_TABLE_NAME, OLD.id, TG_OP, old_data, new_data);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        old_data = to_jsonb(OLD);
        new_data = to_jsonb(NEW);
        INSERT INTO audit_log (table_name, record_id, action, old_values, new_values)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, old_data, new_data);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        old_data = NULL;
        new_data = to_jsonb(NEW);
        INSERT INTO audit_log (table_name, record_id, action, old_values, new_values)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, old_data, new_data);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create system settings table
CREATE TABLE IF NOT EXISTS system_settings (
    id SERIAL PRIMARY KEY,
    key VARCHAR(100) UNIQUE NOT NULL,
    value TEXT,
    description TEXT,
    data_type VARCHAR(20) DEFAULT 'string' CHECK (data_type IN ('string', 'integer', 'float', 'boolean', 'json')),
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create trigger for system_settings updated_at
CREATE TRIGGER update_system_settings_updated_at
    BEFORE UPDATE ON system_settings
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Insert default system settings
INSERT INTO system_settings (key, value, description, data_type, is_public) VALUES
('app_name', 'Ad-tlas', 'Application name', 'string', true),
('app_version', '1.0.0', 'Application version', 'string', true),
('maintenance_mode', 'false', 'Enable maintenance mode', 'boolean', false),
('max_upload_size', '10485760', 'Maximum file upload size in bytes', 'integer', false),
('session_timeout', '3600', 'Session timeout in seconds', 'integer', false),
('email_notifications', 'true', 'Enable email notifications', 'boolean', false),
('default_timezone', 'UTC', 'Default application timezone', 'string', true),
('currency_code', 'USD', 'Default currency code', 'string', true),
('date_format', 'Y-m-d', 'Default date format', 'string', true),
('items_per_page', '25', 'Default items per page', 'integer', true)
ON CONFLICT (key) DO NOTHING;

-- Create performance monitoring view
CREATE OR REPLACE VIEW performance_stats AS
SELECT 
    schemaname,
    tablename,
    attname as column_name,
    n_distinct,
    correlation,
    most_common_vals,
    most_common_freqs
FROM pg_stats 
WHERE schemaname = 'public'
ORDER BY schemaname, tablename, attname;

-- Create database size monitoring view
CREATE OR REPLACE VIEW database_size_stats AS
SELECT 
    pg_database.datname as database_name,
    pg_size_pretty(pg_database_size(pg_database.datname)) as size,
    pg_database_size(pg_database.datname) as size_bytes
FROM pg_database
WHERE pg_database.datname = current_database();

-- Create table size monitoring view
CREATE OR REPLACE VIEW table_size_stats AS
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size,
    pg_total_relation_size(schemaname||'.'||tablename) as total_size_bytes
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Grant permissions on views to application user
GRANT SELECT ON performance_stats TO adtlas_user;
GRANT SELECT ON database_size_stats TO adtlas_user;
GRANT SELECT ON table_size_stats TO adtlas_user;

-- Grant permissions on system tables
GRANT ALL PRIVILEGES ON system_settings TO adtlas_user;
GRANT ALL PRIVILEGES ON audit_log TO adtlas_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO adtlas_user;

-- Create connection limit for application user (optional)
-- ALTER USER adtlas_user CONNECTION LIMIT 20;

-- Set timezone
SET timezone = 'UTC';

-- Optimize PostgreSQL settings for the application
-- Note: These should be set in postgresql.conf for production
-- ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- ALTER SYSTEM SET track_activity_query_size = 2048;
-- ALTER SYSTEM SET pg_stat_statements.track = 'all';

-- Create a cleanup function for old audit logs
CREATE OR REPLACE FUNCTION cleanup_old_audit_logs(days_to_keep INTEGER DEFAULT 90)
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM audit_log 
    WHERE created_at < CURRENT_TIMESTAMP - (days_to_keep || ' days')::INTERVAL;
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Create a function to get database health stats
CREATE OR REPLACE FUNCTION get_database_health()
RETURNS TABLE(
    metric_name TEXT,
    metric_value TEXT,
    status TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        'Database Size'::TEXT,
        pg_size_pretty(pg_database_size(current_database()))::TEXT,
        'OK'::TEXT
    UNION ALL
    SELECT 
        'Active Connections'::TEXT,
        COUNT(*)::TEXT,
        CASE WHEN COUNT(*) > 80 THEN 'WARNING' ELSE 'OK' END::TEXT
    FROM pg_stat_activity 
    WHERE state = 'active'
    UNION ALL
    SELECT 
        'Idle Connections'::TEXT,
        COUNT(*)::TEXT,
        'INFO'::TEXT
    FROM pg_stat_activity 
    WHERE state = 'idle'
    UNION ALL
    SELECT 
        'Database Uptime'::TEXT,
        (CURRENT_TIMESTAMP - pg_postmaster_start_time())::TEXT,
        'OK'::TEXT;
END;
$$ LANGUAGE plpgsql;

-- Grant execute permissions on functions
GRANT EXECUTE ON FUNCTION cleanup_old_audit_logs(INTEGER) TO adtlas_user;
GRANT EXECUTE ON FUNCTION get_database_health() TO adtlas_user;
GRANT EXECUTE ON FUNCTION calculate_roi(DECIMAL, DECIMAL) TO adtlas_user;
GRANT EXECUTE ON FUNCTION get_date_range_stats(DATE, DATE) TO adtlas_user;
GRANT EXECUTE ON FUNCTION is_valid_email(TEXT) TO adtlas_user;
GRANT EXECUTE ON FUNCTION generate_slug(TEXT) TO adtlas_user;
GRANT EXECUTE ON FUNCTION create_search_vector(TEXT, TEXT, TEXT) TO adtlas_user;

-- Final message
DO $$
BEGIN
    RAISE NOTICE 'Ad-tlas database initialization completed successfully!';
    RAISE NOTICE 'Database: adtlas_db';
    RAISE NOTICE 'User: adtlas_user';
    RAISE NOTICE 'Extensions and functions have been created.';
END $$;