CREATE TABLE application_security_controls (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    control_type text NOT NULL CHECK (control_type IN (
        'managed_secret_storage',
        'key_rotation',
        'edge_waf_tls',
        'dast',
        'penetration_test',
        'secure_logging',
        'csrf',
        'vulnerability_disclosure'
    )),
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'implemented', 'approved', 'failed', 'retired')),
    environment text NOT NULL DEFAULT 'staging' CHECK (environment IN ('development', 'test', 'ci', 'staging', 'production')),
    owner text NOT NULL DEFAULT '',
    provider text NOT NULL DEFAULT '',
    policy_reference text NOT NULL DEFAULT '',
    evidence_reference text NOT NULL DEFAULT '',
    last_verified_at timestamptz,
    next_review_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (status <> 'approved' OR evidence_reference <> ''),
    UNIQUE (control_type, environment)
);

CREATE INDEX application_security_controls_type_status_idx
    ON application_security_controls (control_type, status, environment);

CREATE TRIGGER application_security_controls_set_updated_at
BEFORE UPDATE ON application_security_controls
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE key_rotation_runs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    secret_name text NOT NULL,
    secret_category text NOT NULL CHECK (secret_category IN ('jwt', 'mfa', 'card', 'webhook', 'provider_credential', 'database', 'other')),
    status text NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'in_progress', 'completed', 'failed', 'canceled')),
    old_key_reference text NOT NULL DEFAULT '',
    new_key_reference text NOT NULL,
    rotation_reason text NOT NULL,
    scheduled_for timestamptz NOT NULL,
    completed_at timestamptz,
    evidence_reference text NOT NULL DEFAULT '',
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    approved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (status <> 'completed' OR (completed_at IS NOT NULL AND evidence_reference <> ''))
);

CREATE INDEX key_rotation_runs_status_scheduled_idx
    ON key_rotation_runs (status, scheduled_for);

CREATE TRIGGER key_rotation_runs_set_updated_at
BEFORE UPDATE ON key_rotation_runs
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO application_security_controls (
    control_type, status, environment, owner, provider, policy_reference, evidence_reference, metadata
) VALUES
    ('managed_secret_storage', 'draft', 'staging', 'security', 'managed-secret-store', 'docs/security/application-security.md', '', '{"required_config":["BANKING_SECRET_PROVIDER","BANKING_KMS_KEY_REFERENCE"]}'::jsonb),
    ('key_rotation', 'draft', 'staging', 'security', 'kms', 'docs/security/application-security.md', '', '{"secrets":["jwt","mfa","card","webhook","provider_credential"]}'::jsonb),
    ('edge_waf_tls', 'draft', 'staging', 'platform', 'waf/reverse-proxy', 'docs/security/application-security.md', '', '{"required_config":["BANKING_WAF_MODE=enforce","BANKING_TLS_REQUIRED=true","BANKING_HSTS_ENABLED=true"]}'::jsonb),
    ('dast', 'draft', 'staging', 'security', 'zaproxy', '.github/workflows/dast.yml', '', '{}'::jsonb),
    ('penetration_test', 'draft', 'production', 'security', 'external-pentest', 'docs/security/application-security.md', '', '{}'::jsonb),
    ('secure_logging', 'implemented', 'staging', 'security', 'internal-redaction', 'backend/internal/platform/logger/redaction.go', 'go test ./internal/platform/logger', '{"redacts":["password","secret","token","email","pan","cvv"]}'::jsonb),
    ('csrf', 'implemented', 'staging', 'security', 'double-submit-cookie', 'backend/internal/httpapi/middleware/appsec.go', 'go test ./internal/httpapi/middleware', '{}'::jsonb),
    ('vulnerability_disclosure', 'draft', 'production', 'security', 'responsible-disclosure', 'SECURITY.md', '', '{}'::jsonb)
ON CONFLICT DO NOTHING;

INSERT INTO privacy_data_inventory (
    table_name, field_name, data_category, classification, lawful_basis, retention_policy, residency_scope, encrypted_at_rest, owner, notes
) VALUES
    ('application_security_controls', '*', 'audit', 'confidential', 'security/legal_obligation', 'audit_logs_default', 'default', false, 'security', 'Application security control status and evidence references'),
    ('key_rotation_runs', '*', 'secret', 'restricted', 'security', 'secret_material_default', 'default', true, 'security', 'Key rotation metadata and key references only; no raw secrets')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;