-- UTF-8 Encoding für deutsche Sonderzeichen sicherstellen
SET client_encoding = 'UTF8';

-- Zeitzone auf Europe/Berlin setzen
SET timezone = 'Europe/Berlin';

CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    is_fake BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_email UNIQUE (email)
);

CREATE TABLE IF NOT EXISTS licenses (
    id SERIAL PRIMARY KEY,
    license_key TEXT UNIQUE NOT NULL,
    customer_id INTEGER REFERENCES customers(id),
    license_type TEXT NOT NULL,
    valid_from DATE NOT NULL,
    valid_until DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_fake BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sessions (
    id SERIAL PRIMARY KEY,
    license_id INTEGER REFERENCES licenses(id),
    license_key VARCHAR(60),  -- Denormalized for performance
    session_id TEXT UNIQUE NOT NULL,
    username VARCHAR(50),
    computer_name VARCHAR(100),
    hardware_id VARCHAR(100),
    ip_address TEXT,
    user_agent TEXT,
    app_version VARCHAR(20),
    login_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,  -- Alias for started_at
    last_activity TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,  -- Alias for last_heartbeat
    logout_time TIMESTAMP WITH TIME ZONE,  -- Alias for ended_at
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_heartbeat TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT TRUE,
    active BOOLEAN DEFAULT TRUE  -- Alias for is_active
);

-- Audit-Log-Tabelle für Änderungsprotokolle
CREATE TABLE IF NOT EXISTS audit_log (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    username TEXT NOT NULL,
    action TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id INTEGER,
    old_values JSONB,
    new_values JSONB,
    ip_address TEXT,
    user_agent TEXT,
    additional_info TEXT
);

-- Index für bessere Performance bei Abfragen
CREATE INDEX idx_audit_log_timestamp ON audit_log(timestamp DESC);
CREATE INDEX idx_audit_log_username ON audit_log(username);
CREATE INDEX idx_audit_log_entity ON audit_log(entity_type, entity_id);

-- Backup-Historie-Tabelle
CREATE TABLE IF NOT EXISTS backup_history (
    id SERIAL PRIMARY KEY,
    filename TEXT NOT NULL,
    filepath TEXT NOT NULL,
    filesize BIGINT,
    backup_type TEXT NOT NULL, -- 'manual' oder 'scheduled'
    status TEXT NOT NULL, -- 'success', 'failed', 'in_progress'
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT NOT NULL,
    tables_count INTEGER,
    records_count INTEGER,
    duration_seconds NUMERIC,
    is_encrypted BOOLEAN DEFAULT TRUE
);

-- Index für bessere Performance
CREATE INDEX idx_backup_history_created_at ON backup_history(created_at DESC);
CREATE INDEX idx_backup_history_status ON backup_history(status);

-- Login-Attempts-Tabelle für Rate-Limiting
CREATE TABLE IF NOT EXISTS login_attempts (
    ip_address VARCHAR(45) PRIMARY KEY,
    attempt_count INTEGER DEFAULT 0,
    first_attempt TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_attempt TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    blocked_until TIMESTAMP WITH TIME ZONE NULL,
    last_username_tried TEXT,
    last_error_message TEXT
);

-- Index für schnelle Abfragen
CREATE INDEX idx_login_attempts_blocked_until ON login_attempts(blocked_until);
CREATE INDEX idx_login_attempts_last_attempt ON login_attempts(last_attempt DESC);

-- Migration: Füge created_at zu licenses hinzu, falls noch nicht vorhanden
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'created_at') THEN
        ALTER TABLE licenses ADD COLUMN created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;
        
        -- Setze created_at für bestehende Einträge auf das valid_from Datum
        UPDATE licenses SET created_at = valid_from WHERE created_at IS NULL;
    END IF;
END $$;

-- ===================== RESOURCE POOL SYSTEM =====================

-- Haupttabelle für den Resource Pool
CREATE TABLE IF NOT EXISTS resource_pools (
    id SERIAL PRIMARY KEY,
    resource_type VARCHAR(20) NOT NULL CHECK (resource_type IN ('domain', 'ipv4', 'phone')),
    resource_value VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'available' CHECK (status IN ('available', 'allocated', 'quarantine')),
    allocated_to_license INTEGER REFERENCES licenses(id) ON DELETE SET NULL,
    status_changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status_changed_by VARCHAR(50),
    quarantine_reason VARCHAR(100) CHECK (quarantine_reason IN ('abuse', 'defect', 'maintenance', 'blacklisted', 'expired', 'review', NULL)),
    quarantine_until TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    is_fake BOOLEAN DEFAULT FALSE,
    UNIQUE(resource_type, resource_value)
);

-- Resource History für vollständige Nachverfolgbarkeit
CREATE TABLE IF NOT EXISTS resource_history (
    id SERIAL PRIMARY KEY,
    resource_id INTEGER REFERENCES resource_pools(id) ON DELETE CASCADE,
    license_id INTEGER REFERENCES licenses(id) ON DELETE SET NULL,
    action VARCHAR(50) NOT NULL CHECK (action IN ('allocated', 'deallocated', 'quarantined', 'released', 'created', 'deleted')),
    action_by VARCHAR(50) NOT NULL,
    action_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    details JSONB,
    ip_address TEXT
);

-- Resource Metrics für Performance-Tracking und ROI
CREATE TABLE IF NOT EXISTS resource_metrics (
    id SERIAL PRIMARY KEY,
    resource_id INTEGER REFERENCES resource_pools(id) ON DELETE CASCADE,
    metric_date DATE NOT NULL,
    usage_count INTEGER DEFAULT 0,
    performance_score DECIMAL(5,2) DEFAULT 0.00,
    cost DECIMAL(10,2) DEFAULT 0.00,
    revenue DECIMAL(10,2) DEFAULT 0.00,
    issues_count INTEGER DEFAULT 0,
    availability_percent DECIMAL(5,2) DEFAULT 100.00,
    UNIQUE(resource_id, metric_date)
);

-- Zuordnungstabelle zwischen Lizenzen und Ressourcen
CREATE TABLE IF NOT EXISTS license_resources (
    id SERIAL PRIMARY KEY,
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    resource_id INTEGER REFERENCES resource_pools(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    assigned_by VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    UNIQUE(license_id, resource_id)
);

-- Erweiterung der licenses Tabelle um Resource-Counts
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'domain_count') THEN
        ALTER TABLE licenses 
        ADD COLUMN domain_count INTEGER DEFAULT 1 CHECK (domain_count >= 0 AND domain_count <= 10),
        ADD COLUMN ipv4_count INTEGER DEFAULT 1 CHECK (ipv4_count >= 0 AND ipv4_count <= 10),
        ADD COLUMN phone_count INTEGER DEFAULT 1 CHECK (phone_count >= 0 AND phone_count <= 10);
    END IF;
END $$;

-- Erweiterung der licenses Tabelle um device_limit
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'device_limit') THEN
        ALTER TABLE licenses 
        ADD COLUMN device_limit INTEGER DEFAULT 3 CHECK (device_limit >= 1 AND device_limit <= 10);
    END IF;
END $$;

-- Tabelle für Geräte-Registrierungen
CREATE TABLE IF NOT EXISTS device_registrations (
    id SERIAL PRIMARY KEY,
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    hardware_id TEXT NOT NULL,
    device_name TEXT,
    operating_system TEXT,
    first_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    deactivated_at TIMESTAMP WITH TIME ZONE,
    deactivated_by TEXT,
    ip_address TEXT,
    user_agent TEXT,
    UNIQUE(license_id, hardware_id)
);

-- Indizes für device_registrations
CREATE INDEX IF NOT EXISTS idx_device_license ON device_registrations(license_id);
CREATE INDEX IF NOT EXISTS idx_device_hardware ON device_registrations(hardware_id);
CREATE INDEX IF NOT EXISTS idx_device_active ON device_registrations(license_id, is_active) WHERE is_active = TRUE;

-- Indizes für Performance
CREATE INDEX IF NOT EXISTS idx_resource_status ON resource_pools(status);
CREATE INDEX IF NOT EXISTS idx_resource_type_status ON resource_pools(resource_type, status);
CREATE INDEX IF NOT EXISTS idx_resource_allocated ON resource_pools(allocated_to_license) WHERE allocated_to_license IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_resource_quarantine ON resource_pools(quarantine_until) WHERE status = 'quarantine';
CREATE INDEX IF NOT EXISTS idx_resource_history_date ON resource_history(action_at DESC);
CREATE INDEX IF NOT EXISTS idx_resource_history_resource ON resource_history(resource_id);
CREATE INDEX IF NOT EXISTS idx_resource_metrics_date ON resource_metrics(metric_date DESC);
CREATE INDEX IF NOT EXISTS idx_license_resources_active ON license_resources(license_id) WHERE is_active = TRUE;

-- Users table for authentication with password and 2FA support
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    totp_secret VARCHAR(32),
    totp_enabled BOOLEAN DEFAULT FALSE,
    backup_codes TEXT, -- JSON array of hashed backup codes
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_password_change TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    password_reset_token VARCHAR(64),
    password_reset_expires TIMESTAMP WITH TIME ZONE,
    failed_2fa_attempts INTEGER DEFAULT 0,
    last_failed_2fa TIMESTAMP WITH TIME ZONE
);

-- Index for faster login lookups
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_reset_token ON users(password_reset_token) WHERE password_reset_token IS NOT NULL;

-- Migration: Add is_fake column to licenses if it doesn't exist
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'is_fake') THEN
        ALTER TABLE licenses ADD COLUMN is_fake BOOLEAN DEFAULT FALSE;
        
        -- Mark all existing licenses as fake data
        UPDATE licenses SET is_fake = TRUE;
        
        -- Add index for better performance when filtering fake data
        CREATE INDEX idx_licenses_is_fake ON licenses(is_fake);
    END IF;
END $$;

-- Migration: Add is_fake column to customers if it doesn't exist
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'customers' AND column_name = 'is_fake') THEN
        ALTER TABLE customers ADD COLUMN is_fake BOOLEAN DEFAULT FALSE;
        
        -- Mark all existing customers as fake data
        UPDATE customers SET is_fake = TRUE;
        
        -- Add index for better performance
        CREATE INDEX idx_customers_is_fake ON customers(is_fake);
    END IF;
END $$;

-- Migration: Add is_fake column to resource_pools if it doesn't exist
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'resource_pools' AND column_name = 'is_fake') THEN
        ALTER TABLE resource_pools ADD COLUMN is_fake BOOLEAN DEFAULT FALSE;
        
        -- Mark all existing resources as fake data
        UPDATE resource_pools SET is_fake = TRUE;
        
        -- Add index for better performance
        CREATE INDEX idx_resource_pools_is_fake ON resource_pools(is_fake);
    END IF;
END $$;

-- Migration: Add missing columns to sessions table
DO $$
BEGIN
    -- Add license_key column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'license_key') THEN
        ALTER TABLE sessions ADD COLUMN license_key VARCHAR(60);
    END IF;
    
    -- Add username column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'username') THEN
        ALTER TABLE sessions ADD COLUMN username VARCHAR(50);
    END IF;
    
    -- Add computer_name column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'computer_name') THEN
        ALTER TABLE sessions ADD COLUMN computer_name VARCHAR(100);
    END IF;
    
    -- Add hardware_id column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'hardware_id') THEN
        ALTER TABLE sessions ADD COLUMN hardware_id VARCHAR(100);
    END IF;
    
    -- Add app_version column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'app_version') THEN
        ALTER TABLE sessions ADD COLUMN app_version VARCHAR(20);
    END IF;
    
    -- Add login_time as alias for started_at
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'login_time') THEN
        ALTER TABLE sessions ADD COLUMN login_time TIMESTAMP WITH TIME ZONE;
        UPDATE sessions SET login_time = started_at;
    END IF;
    
    -- Add last_activity as alias for last_heartbeat
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'last_activity') THEN
        ALTER TABLE sessions ADD COLUMN last_activity TIMESTAMP WITH TIME ZONE;
        UPDATE sessions SET last_activity = last_heartbeat;
    END IF;
    
    -- Add logout_time as alias for ended_at
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'logout_time') THEN
        ALTER TABLE sessions ADD COLUMN logout_time TIMESTAMP WITH TIME ZONE;
        UPDATE sessions SET logout_time = ended_at;
    END IF;
    
    -- Add active as alias for is_active
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sessions' AND column_name = 'active') THEN
        ALTER TABLE sessions ADD COLUMN active BOOLEAN DEFAULT TRUE;
        UPDATE sessions SET active = is_active;
    END IF;
END $$;

-- ===================== LICENSE SERVER TABLES =====================
-- Following best practices: snake_case for DB fields, clear naming conventions

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- License tokens for offline validation
CREATE TABLE IF NOT EXISTS license_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    token VARCHAR(512) NOT NULL UNIQUE,
    hardware_id VARCHAR(255) NOT NULL,
    valid_until TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_validated TIMESTAMP,
    validation_count INTEGER DEFAULT 0
);

CREATE INDEX idx_token ON license_tokens(token);
CREATE INDEX idx_hardware ON license_tokens(hardware_id);
CREATE INDEX idx_valid_until ON license_tokens(valid_until);

-- Heartbeat tracking with partitioning support
CREATE TABLE IF NOT EXISTS license_heartbeats (
    id BIGSERIAL,
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    hardware_id VARCHAR(255) NOT NULL,
    ip_address INET,
    user_agent VARCHAR(500),
    app_version VARCHAR(50),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_data JSONB,
    PRIMARY KEY (id, timestamp)
) PARTITION BY RANGE (timestamp);

-- Create partitions for the current and next month
CREATE TABLE IF NOT EXISTS license_heartbeats_2025_01 PARTITION OF license_heartbeats
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE IF NOT EXISTS license_heartbeats_2025_02 PARTITION OF license_heartbeats
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Add June 2025 partition for current month
CREATE TABLE IF NOT EXISTS license_heartbeats_2025_06 PARTITION OF license_heartbeats
    FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

CREATE INDEX idx_heartbeat_license_time ON license_heartbeats(license_id, timestamp DESC);
CREATE INDEX idx_heartbeat_hardware_time ON license_heartbeats(hardware_id, timestamp DESC);

-- Activation events tracking
CREATE TABLE IF NOT EXISTS activation_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL CHECK (event_type IN ('activation', 'deactivation', 'reactivation', 'transfer')),
    hardware_id VARCHAR(255),
    previous_hardware_id VARCHAR(255),
    ip_address INET,
    user_agent VARCHAR(500),
    success BOOLEAN DEFAULT true,
    error_message TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_license_events ON activation_events(license_id, created_at DESC);
CREATE INDEX idx_event_type ON activation_events(event_type, created_at DESC);

-- API rate limiting
CREATE TABLE IF NOT EXISTS api_rate_limits (
    id SERIAL PRIMARY KEY,
    api_key VARCHAR(255) NOT NULL UNIQUE,
    requests_per_minute INTEGER DEFAULT 60,
    requests_per_hour INTEGER DEFAULT 1000,
    requests_per_day INTEGER DEFAULT 10000,
    burst_size INTEGER DEFAULT 100,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Anomaly detection
CREATE TABLE IF NOT EXISTS anomaly_detections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    license_id INTEGER REFERENCES licenses(id),
    anomaly_type VARCHAR(100) NOT NULL CHECK (anomaly_type IN ('multiple_ips', 'rapid_hardware_change', 'suspicious_pattern', 'concurrent_use', 'geo_anomaly')),
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    details JSONB NOT NULL,
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved BOOLEAN DEFAULT false,
    resolved_at TIMESTAMP,
    resolved_by VARCHAR(255),
    action_taken TEXT
);

CREATE INDEX idx_unresolved ON anomaly_detections(resolved, severity, detected_at DESC);
CREATE INDEX idx_license_anomalies ON anomaly_detections(license_id, detected_at DESC);

-- API clients for authentication
CREATE TABLE IF NOT EXISTS api_clients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_name VARCHAR(255) NOT NULL,
    api_key VARCHAR(255) NOT NULL UNIQUE,
    secret_key VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    allowed_endpoints TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Feature flags for gradual rollout
CREATE TABLE IF NOT EXISTS feature_flags (
    id SERIAL PRIMARY KEY,
    feature_name VARCHAR(100) NOT NULL UNIQUE,
    is_enabled BOOLEAN DEFAULT false,
    rollout_percentage INTEGER DEFAULT 0 CHECK (rollout_percentage >= 0 AND rollout_percentage <= 100),
    whitelist_license_ids INTEGER[],
    blacklist_license_ids INTEGER[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default feature flags
INSERT INTO feature_flags (feature_name, is_enabled, rollout_percentage) VALUES
    ('anomaly_detection', true, 100),
    ('offline_tokens', true, 100),
    ('advanced_analytics', false, 0),
    ('geo_restriction', false, 0)
ON CONFLICT (feature_name) DO NOTHING;

-- Session management for concurrent use tracking
CREATE TABLE IF NOT EXISTS active_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    hardware_id VARCHAR(255) NOT NULL,
    session_token VARCHAR(512) NOT NULL UNIQUE,
    ip_address INET,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_session_license ON active_sessions(license_id);
CREATE INDEX idx_session_expires ON active_sessions(expires_at);

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

CREATE TRIGGER update_api_rate_limits_updated_at BEFORE UPDATE ON api_rate_limits
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_api_clients_updated_at BEFORE UPDATE ON api_clients
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_feature_flags_updated_at BEFORE UPDATE ON feature_flags
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function to automatically create monthly partitions for heartbeats
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    start_date date;
    end_date date;
    partition_name text;
BEGIN
    start_date := date_trunc('month', CURRENT_DATE + interval '1 month');
    end_date := start_date + interval '1 month';
    partition_name := 'license_heartbeats_' || to_char(start_date, 'YYYY_MM');
    
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF license_heartbeats FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date);
END;
$$ LANGUAGE plpgsql;

-- Migration: Add max_devices column to licenses if it doesn't exist
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'max_devices') THEN
        ALTER TABLE licenses ADD COLUMN max_devices INTEGER DEFAULT 3 CHECK (max_devices >= 1);
    END IF;
END $$;

-- Migration: Add expires_at column to licenses if it doesn't exist
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'expires_at') THEN
        ALTER TABLE licenses ADD COLUMN expires_at TIMESTAMP;
        -- Set expires_at based on valid_until for existing licenses
        UPDATE licenses SET expires_at = valid_until::timestamp WHERE expires_at IS NULL;
    END IF;
END $$;

-- Migration: Add features column to licenses if it doesn't exist
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'features') THEN
        ALTER TABLE licenses ADD COLUMN features TEXT[] DEFAULT '{}';
    END IF;
END $$;

-- Migration: Add updated_at column to licenses if it doesn't exist
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'licenses' AND column_name = 'updated_at') THEN
        ALTER TABLE licenses ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
        CREATE TRIGGER update_licenses_updated_at BEFORE UPDATE ON licenses
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    END IF;
END $$;


-- Migration: Add device_type column to device_registrations table
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'device_registrations' AND column_name = 'device_type') THEN
        ALTER TABLE device_registrations ADD COLUMN device_type VARCHAR(50) DEFAULT 'unknown';
        
        -- Update existing records to have a device_type based on operating system
        UPDATE device_registrations 
        SET device_type = CASE 
            WHEN operating_system ILIKE '%windows%' THEN 'desktop'
            WHEN operating_system ILIKE '%mac%' THEN 'desktop'
            WHEN operating_system ILIKE '%linux%' THEN 'desktop'
            WHEN operating_system ILIKE '%android%' THEN 'mobile'
            WHEN operating_system ILIKE '%ios%' THEN 'mobile'
            ELSE 'unknown'
        END
        WHERE device_type IS NULL OR device_type = 'unknown';
    END IF;
END $$;

-- Client configuration table for Account Forger
CREATE TABLE IF NOT EXISTS client_configs (
    id SERIAL PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL DEFAULT 'Account Forger',
    api_key VARCHAR(255) NOT NULL,
    heartbeat_interval INTEGER DEFAULT 30, -- seconds
    session_timeout INTEGER DEFAULT 60, -- seconds (2x heartbeat)
    current_version VARCHAR(20) NOT NULL,
    minimum_version VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- License sessions for single-session enforcement
CREATE TABLE IF NOT EXISTS license_sessions (
    id SERIAL PRIMARY KEY,
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    hardware_id VARCHAR(255) NOT NULL,
    ip_address INET,
    client_version VARCHAR(20),
    session_token VARCHAR(255) UNIQUE NOT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_heartbeat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(license_id) -- Only one active session per license
);

-- Session history for debugging
CREATE TABLE IF NOT EXISTS session_history (
    id SERIAL PRIMARY KEY,
    license_id INTEGER REFERENCES licenses(id) ON DELETE CASCADE,
    hardware_id VARCHAR(255) NOT NULL,
    ip_address INET,
    client_version VARCHAR(20),
    started_at TIMESTAMP,
    ended_at TIMESTAMP,
    end_reason VARCHAR(50) -- 'normal', 'timeout', 'forced', 'replaced'
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_license_sessions_license_id ON license_sessions(license_id);
CREATE INDEX IF NOT EXISTS idx_license_sessions_last_heartbeat ON license_sessions(last_heartbeat);
CREATE INDEX IF NOT EXISTS idx_session_history_license_id ON session_history(license_id);
CREATE INDEX IF NOT EXISTS idx_session_history_ended_at ON session_history(ended_at);

-- Insert default client configuration if not exists
INSERT INTO client_configs (client_name, api_key, current_version, minimum_version)
VALUES ('Account Forger', 'AF-' || gen_random_uuid()::text, '1.0.0', '1.0.0')
ON CONFLICT DO NOTHING;

-- ===================== SYSTEM API KEY TABLE =====================
-- Single API key for system-wide authentication
CREATE TABLE IF NOT EXISTS system_api_key (
    id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),  -- Ensures single row
    api_key VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    regenerated_at TIMESTAMP WITH TIME ZONE,
    last_used_at TIMESTAMP WITH TIME ZONE,
    usage_count INTEGER DEFAULT 0,
    created_by VARCHAR(50),
    regenerated_by VARCHAR(50)
);

-- Function to generate API key with AF-YYYY- prefix
CREATE OR REPLACE FUNCTION generate_api_key() RETURNS VARCHAR AS $$
DECLARE
    year_part VARCHAR(4);
    random_part VARCHAR(32);
BEGIN
    year_part := to_char(CURRENT_DATE, 'YYYY');
    random_part := upper(substring(md5(random()::text || clock_timestamp()::text) from 1 for 32));
    RETURN 'AF-' || year_part || '-' || random_part;
END;
$$ LANGUAGE plpgsql;

-- Initialize with a default API key if none exists
INSERT INTO system_api_key (api_key, created_by)
SELECT generate_api_key(), 'system'
WHERE NOT EXISTS (SELECT 1 FROM system_api_key);

-- Audit trigger for API key changes
CREATE OR REPLACE FUNCTION audit_api_key_changes() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND OLD.api_key != NEW.api_key THEN
        INSERT INTO audit_log (
            timestamp,
            username,
            action,
            entity_type,
            entity_id,
            old_values,
            new_values,
            additional_info
        ) VALUES (
            CURRENT_TIMESTAMP,
            COALESCE(NEW.regenerated_by, 'system'),
            'api_key_regenerated',
            'system_api_key',
            NEW.id,
            jsonb_build_object('api_key', LEFT(OLD.api_key, 8) || '...'),
            jsonb_build_object('api_key', LEFT(NEW.api_key, 8) || '...'),
            'API Key regenerated'
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_system_api_key_changes
AFTER UPDATE ON system_api_key
FOR EACH ROW EXECUTE FUNCTION audit_api_key_changes();