Compliance

-- Enable SOC 2 controls
ALTER SYSTEM SET compliance_framework = 'soc2';

-- Enable required audit logging
ALTER SYSTEM SET audit_logging = on;
ALTER SYSTEM SET audit_log_authentication = on;
ALTER SYSTEM SET audit_log_data_access = on;
ALTER SYSTEM SET audit_log_schema_changes = on;
ALTER SYSTEM SET audit_log_retention = '365d';

-- Configure access controls (CC6.1)
CREATE ROLE application_user;
GRANT SELECT, INSERT, UPDATE ON customer_data TO application_user;

-- Implement encryption (CC6.1)
ALTER SYSTEM SET encryption_at_rest = on;
ALTER SYSTEM SET encryption_in_transit = 'required';

-- Configure backup retention (A1.2)
ALTER SYSTEM SET backup_retention_days = 90;
ALTER SYSTEM SET backup_encryption = on;

-- Enable monitoring (CC7.2)
ALTER SYSTEM SET monitoring_enabled = on;
ALTER SYSTEM SET alerting_enabled = on;

-- Generate SOC 2 compliance report
SELECT entropy_generate_compliance_report(
  framework => 'soc2',
  start_date => '2024-01-01',
  end_date => '2024-12-31',
  output_format => 'pdf',
  output_path => '/reports/soc2_2024.pdf'
);

-- Enable HIPAA mode
ALTER SYSTEM SET compliance_framework = 'hipaa';

-- Configure PHI encryption (§164.312(a)(2)(iv))
CREATE TABLE patient_records (
  patient_id UUID PRIMARY KEY,
  ssn TEXT ENCRYPTED,
  medical_record_number TEXT ENCRYPTED,
  diagnosis TEXT ENCRYPTED,
  treatment_plan TEXT ENCRYPTED
);

-- Implement access controls (§164.312(a)(1))
CREATE ROLE physician;
CREATE ROLE nurse;
CREATE ROLE billing_staff;

-- PHI access policies
CREATE POLICY physician_access ON patient_records
  FOR ALL TO physician
  USING (true);

CREATE POLICY nurse_access ON patient_records
  FOR SELECT TO nurse
  USING (assigned_nurse = current_user);

-- Audit all PHI access (§164.312(b))
CREATE AUDIT POLICY hipaa_phi_audit
  ON patient_records
  FOR ALL
  AUDIT ALL;

-- Implement automatic logoff (§164.312(a)(2)(iii))
ALTER SYSTEM SET idle_session_timeout = '15min';

-- Backup requirements (§164.308(a)(7)(ii)(A))
ALTER SYSTEM SET backup_frequency = 'hourly';
ALTER SYSTEM SET backup_retention_days = 2555;  -- 7 years

-- Generate HIPAA audit report
SELECT entropy_generate_compliance_report(
  framework => 'hipaa',
  include_phi_access_log => true,
  include_breach_incidents => true
);

-- Enable GDPR mode
ALTER SYSTEM SET compliance_framework = 'gdpr';
ALTER SYSTEM SET data_residency = 'eu';

-- Right to access (Article 15)
CREATE FUNCTION get_user_data(user_email TEXT)
RETURNS JSON AS $$
  SELECT json_build_object(
    'personal_info', (SELECT row_to_json(users.*) FROM users WHERE email = user_email),
    'orders', (SELECT json_agg(orders.*) FROM orders WHERE customer_email = user_email),
    'activity', (SELECT json_agg(activity_log.*) FROM activity_log WHERE user_email = user_email)
  );
$$ LANGUAGE SQL;

-- Right to erasure (Article 17)
CREATE FUNCTION erase_user_data(user_email TEXT)
RETURNS VOID AS $$
BEGIN
  -- Anonymize user data
  UPDATE users 
  SET 
    first_name = 'DELETED',
    last_name = 'DELETED',
    email = CONCAT('deleted_', user_id, '@example.com'),
    phone = NULL,
    address = NULL,
    deleted_at = NOW()
  WHERE email = user_email;
  
  -- Delete associated data
  DELETE FROM sessions WHERE user_email = user_email;
  DELETE FROM activity_log WHERE user_email = user_email;
  
  -- Audit the deletion
  INSERT INTO gdpr_deletion_log (email, deleted_at, deleted_by)
  VALUES (user_email, NOW(), current_user);
END;
$$ LANGUAGE plpgsql;

-- Right to data portability (Article 20)
CREATE FUNCTION export_user_data(user_email TEXT)
RETURNS TEXT AS $$
  COPY (
    SELECT * FROM get_user_data(user_email)
  ) TO '/exports/user_data.json';
  
  RETURN '/exports/user_data.json';
$$ LANGUAGE SQL;

-- Consent management (Article 7)
CREATE TABLE consent_records (
  consent_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_email TEXT,
  purpose TEXT,
  consented_at TIMESTAMP,
  consent_method TEXT,
  ip_address INET,
  user_agent TEXT
);

-- Data retention policies (Article 5)
CREATE TABLE retention_policies (
  table_name TEXT,
  retention_days INTEGER,
  deletion_method TEXT  -- 'delete' or 'anonymize'
);

-- Automated retention enforcement
CREATE FUNCTION enforce_retention_policies()
RETURNS VOID AS $$
DECLARE
  policy RECORD;
BEGIN
  FOR policy IN SELECT * FROM retention_policies LOOP
    EXECUTE format(
      'DELETE FROM %I WHERE created_at < NOW() - INTERVAL ''%s days''',
      policy.table_name, policy.retention_days
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Schedule retention enforcement
CREATE CRON JOB enforce_retention
  SCHEDULE '0 2 * * *'  -- Daily at 2 AM
  EXECUTE 'SELECT enforce_retention_policies()';

-- Enable ISO 27001 controls
ALTER SYSTEM SET compliance_framework = 'iso27001';

-- A.9.2.1: User registration and de-registration
CREATE TABLE user_lifecycle_log (
  event_id UUID PRIMARY KEY,
  username TEXT,
  event_type TEXT,  -- 'created', 'modified', 'disabled', 'deleted'
  performed_by TEXT,
  performed_at TIMESTAMP,
  details JSONB
);

-- A.9.2.4: Management of secret authentication
ALTER SYSTEM SET password_min_length = 12;
ALTER SYSTEM SET password_complexity = 'high';
ALTER SYSTEM SET password_expiration_days = 90;
ALTER SYSTEM SET password_history = 10;

-- A.9.4.1: Information access restriction
CREATE POLICY restrict_sensitive_data ON financial_data
  FOR ALL
  USING (
    user_has_clearance(current_user, 'financial') OR
    department = user_department(current_user)
  );

-- A.12.3.1: Information backup
ALTER SYSTEM SET backup_frequency = 'daily';
ALTER SYSTEM SET backup_verification = on;
ALTER SYSTEM SET offsite_backup = on;

-- A.12.4.1: Event logging
ALTER SYSTEM SET audit_logging = on;
ALTER SYSTEM SET audit_log_all_queries = on;
ALTER SYSTEM SET audit_log_retention = '730d';  -- 2 years

-- A.18.1.1: Compliance review
CREATE FUNCTION iso27001_compliance_check()
RETURNS TABLE(control TEXT, status TEXT, details TEXT) AS $$
  SELECT 'A.9.2.1' as control, 
         CASE WHEN EXISTS (SELECT 1 FROM user_lifecycle_log) 
              THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END,
         'User lifecycle tracking';
  -- Add more control checks
$$ LANGUAGE SQL;

-- Automated compliance monitoring
CREATE TABLE compliance_checks (
  check_id UUID PRIMARY KEY,
  framework TEXT,  -- 'soc2', 'hipaa', 'gdpr', 'iso27001'
  control_id TEXT,
  check_name TEXT,
  check_query TEXT,
  frequency INTERVAL,
  last_run TIMESTAMP,
  last_status TEXT  -- 'pass', 'fail', 'warning'
);

-- Example compliance checks
INSERT INTO compliance_checks (framework, control_id, check_name, check_query, frequency) VALUES
('soc2', 'CC6.1', 'Encryption enabled', 
 'SELECT CASE WHEN current_setting(''encryption_at_rest'') = ''on'' THEN ''pass'' ELSE ''fail'' END', 
 '1 day'),
 
('hipaa', '164.312(b)', 'PHI audit logging', 
 'SELECT CASE WHEN COUNT(*) > 0 THEN ''pass'' ELSE ''fail'' END FROM entropy_audit_policies WHERE table_name = ''patient_records''', 
 '1 day'),
 
('gdpr', 'Article 17', 'Data retention enforcement', 
 'SELECT CASE WHEN COUNT(*) = 0 THEN ''pass'' ELSE ''fail'' END FROM users WHERE created_at < NOW() - INTERVAL ''5 years'' AND deleted_at IS NULL', 
 '1 week');

-- Run compliance checks
CREATE FUNCTION run_compliance_checks()
RETURNS TABLE(framework TEXT, control_id TEXT, status TEXT) AS $$
DECLARE
  check RECORD;
  result TEXT;
BEGIN
  FOR check IN SELECT * FROM compliance_checks LOOP
    EXECUTE check.check_query INTO result;
    
    UPDATE compliance_checks
    SET last_run = NOW(), last_status = result
    WHERE check_id = check.check_id;
    
    RETURN QUERY SELECT check.framework, check.control_id, result;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Schedule automated checks
CREATE CRON JOB compliance_monitoring
  SCHEDULE '0 */6 * * *'  -- Every 6 hours
  EXECUTE 'SELECT run_compliance_checks()';

-- Generate compliance dashboard
SELECT 
  framework,
  COUNT(*) as total_checks,
  COUNT(*) FILTER (WHERE last_status = 'pass') as passed,
  COUNT(*) FILTER (WHERE last_status = 'fail') as failed,
  COUNT(*) FILTER (WHERE last_status = 'warning') as warnings
FROM compliance_checks
GROUP BY framework;