-- EDM (Exact Data Match) Classifiers Migration
-- 014_edm_classifiers.sql

-- EDM Classifiers Table
CREATE TABLE IF NOT EXISTS edm_classifiers (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	name VARCHAR(255) NOT NULL,
	description TEXT,
	schema_name VARCHAR(255) NOT NULL, -- e.g., 'employee_id', 'customer_id', 'patient_mrn'
	primary_key_column VARCHAR(255) NOT NULL, -- Column name that uniquely identifies records
	sensitive_columns TEXT[] NOT NULL, -- Array of column names containing sensitive data
	source_table VARCHAR(255), -- Optional: source table name
	data_source_id UUID REFERENCES sources(id) ON DELETE SET NULL,
	status VARCHAR(50) NOT NULL DEFAULT 'draft', -- 'draft', 'active', 'inactive'
	record_count INT DEFAULT 0,
	last_sync_at TIMESTAMPTZ,
	hash_algorithm VARCHAR(50) DEFAULT 'sha256', -- 'sha256', 'sha512', 'md5'
	created_by UUID REFERENCES users(id) ON DELETE SET NULL,
	created_at TIMESTAMPTZ DEFAULT NOW(),
	updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- EDM Data Table (stores hashed sensitive values)
CREATE TABLE IF NOT EXISTS edm_data (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	classifier_id UUID REFERENCES edm_classifiers(id) ON DELETE CASCADE,
	primary_key_hash VARCHAR(64) NOT NULL, -- SHA256 of primary key value
	sensitive_value_hashes JSONB NOT NULL, -- {column_name: hash_value}
	created_at TIMESTAMPTZ DEFAULT NOW(),
	UNIQUE(classifier_id, primary_key_hash)
);

-- EDM Matches Table (tracks when EDM values are found in documents)
CREATE TABLE IF NOT EXISTS edm_matches (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	classifier_id UUID REFERENCES edm_classifiers(id) ON DELETE CASCADE,
	document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
	primary_key_hash VARCHAR(64) NOT NULL,
	matched_column VARCHAR(255) NOT NULL,
	matched_text_hash VARCHAR(64) NOT NULL, -- SHA256 of matched text snippet
	confidence FLOAT DEFAULT 1.0,
	matched_at TIMESTAMPTZ DEFAULT NOW(),
	created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_edm_classifiers_status ON edm_classifiers(status);
CREATE INDEX IF NOT EXISTS idx_edm_data_classifier ON edm_data(classifier_id);
CREATE INDEX IF NOT EXISTS idx_edm_data_primary_key ON edm_data(classifier_id, primary_key_hash);
CREATE INDEX IF NOT EXISTS idx_edm_matches_classifier ON edm_matches(classifier_id);
CREATE INDEX IF NOT EXISTS idx_edm_matches_document ON edm_matches(document_id);
CREATE INDEX IF NOT EXISTS idx_edm_matches_primary_key ON edm_matches(classifier_id, primary_key_hash);

