-- Sensitive Information Types Migration
-- 016_sensitive_info_types.sql

-- Sensitive Info Types Table
CREATE TABLE IF NOT EXISTS sensitive_info_types (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	name VARCHAR(255) NOT NULL UNIQUE,
	display_name VARCHAR(255) NOT NULL,
	category VARCHAR(50) NOT NULL, -- 'pii', 'pci', 'health', 'financial', 'custom'
	detection_type VARCHAR(100) NOT NULL, -- e.g., 'pii.email', 'pci.pan', 'health.mrn'
	pattern TEXT, -- Regex pattern or ML model reference
	description TEXT,
	severity VARCHAR(20) NOT NULL DEFAULT 'medium', -- 'high', 'medium', 'low'
	created_at TIMESTAMPTZ DEFAULT NOW(),
	updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Link table between Sensitive Info Types and Labels
CREATE TABLE IF NOT EXISTS sensitive_info_type_labels (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	info_type_id UUID NOT NULL REFERENCES sensitive_info_types(id) ON DELETE CASCADE,
	label_id UUID NOT NULL REFERENCES sensitivity_labels(id) ON DELETE CASCADE,
	created_at TIMESTAMPTZ DEFAULT NOW(),
	UNIQUE(info_type_id, label_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_sensitive_info_types_category ON sensitive_info_types(category);
CREATE INDEX IF NOT EXISTS idx_sensitive_info_types_detection_type ON sensitive_info_types(detection_type);
CREATE INDEX IF NOT EXISTS idx_sensitive_info_types_severity ON sensitive_info_types(severity);
CREATE INDEX IF NOT EXISTS idx_sensitive_info_type_labels_info_type ON sensitive_info_type_labels(info_type_id);
CREATE INDEX IF NOT EXISTS idx_sensitive_info_type_labels_label ON sensitive_info_type_labels(label_id);

-- Insert default sensitive info types
INSERT INTO sensitive_info_types (name, display_name, category, detection_type, pattern, description, severity)
VALUES
	('pii.email', 'Email Address', 'pii', 'pii.email', '\\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}\\b', 'Email addresses', 'high'),
	('pii.phone', 'Phone Number', 'pii', 'pii.phone', '\\b\\d{3}[-.]?\\d{3}[-.]?\\d{4}\\b', 'Phone numbers', 'high'),
	('pii.ssn', 'Social Security Number', 'pii', 'pii.ssn', '\\b\\d{3}-\\d{2}-\\d{4}\\b', 'US Social Security Numbers', 'high'),
	('pci.pan', 'Credit Card Number', 'pci', 'pci.pan', '\\b\\d{4}[\\s-]?\\d{4}[\\s-]?\\d{4}[\\s-]?\\d{4}\\b', 'Credit card numbers', 'high'),
	('health.mrn', 'Medical Record Number', 'health', 'health.mrn', NULL, 'Medical record numbers', 'high'),
	('financial.iban', 'IBAN', 'financial', 'financial.iban', '\\b[A-Z]{2}\\d{2}[A-Z0-9]{4}\\d{7}([A-Z0-9]?){0,16}\\b', 'International Bank Account Numbers', 'medium')
ON CONFLICT (name) DO NOTHING;

