-- V011 Master Data Management
CREATE TABLE IF NOT EXISTS master_entities_v011 (id varchar(36) PRIMARY KEY,entity_type varchar(40) NOT NULL,master_code varchar(80) NOT NULL,legal_name varchar(300) DEFAULT '',display_name varchar(300) DEFAULT '',tax_id varchar(80) DEFAULT '',country_code varchar(3) DEFAULT 'BRA',status varchar(30) DEFAULT 'draft',lifecycle_status varchar(30) DEFAULT 'active',department_id varchar(36),steward_id varchar(36),golden_data json NOT NULL,quality_score int DEFAULT 0,source_count int DEFAULT 0,approved_by varchar(36),approved_at timestamp,created_by varchar(36) NOT NULL,created_at timestamp NOT NULL,updated_at timestamp NOT NULL,CONSTRAINT uq_mdm_type_code UNIQUE(entity_type,master_code));
CREATE TABLE IF NOT EXISTS master_source_records_v011 (id varchar(36) PRIMARY KEY,master_entity_id varchar(36),entity_type varchar(40) NOT NULL,source_system varchar(120) NOT NULL,source_key varchar(200) NOT NULL,source_data json NOT NULL,normalized_data json NOT NULL,source_priority int DEFAULT 100,active boolean DEFAULT true,last_seen_at timestamp NOT NULL,created_at timestamp NOT NULL,CONSTRAINT uq_mdm_source_key UNIQUE(source_system,source_key));
CREATE TABLE IF NOT EXISTS match_rules_v011 (id varchar(36) PRIMARY KEY,name varchar(180),entity_type varchar(40),fields json NOT NULL,weights json NOT NULL,exact_fields json NOT NULL,threshold int DEFAULT 85,auto_merge_threshold int DEFAULT 98,enabled boolean DEFAULT true,created_by varchar(36),created_at timestamp NOT NULL);
CREATE TABLE IF NOT EXISTS duplicate_candidates_v011 (id varchar(36) PRIMARY KEY,entity_type varchar(40),left_entity_id varchar(36),right_entity_id varchar(36),rule_id varchar(36),score int DEFAULT 0,evidence json NOT NULL,status varchar(30) DEFAULT 'pending',reviewed_by varchar(36),reviewed_at timestamp,created_at timestamp NOT NULL,CONSTRAINT uq_mdm_duplicate_pair UNIQUE(left_entity_id,right_entity_id));
CREATE TABLE IF NOT EXISTS survivorship_rules_v011 (id varchar(36) PRIMARY KEY,entity_type varchar(40),field_name varchar(120),strategy varchar(40),source_order json NOT NULL,required boolean DEFAULT false,enabled boolean DEFAULT true,created_by varchar(36));
CREATE TABLE IF NOT EXISTS master_change_requests_v011 (id varchar(36) PRIMARY KEY,master_entity_id varchar(36),requested_by varchar(36),change_type varchar(40),proposed_data json NOT NULL,reason text,status varchar(30),reviewed_by varchar(36),reviewed_at timestamp,created_at timestamp NOT NULL);
CREATE TABLE IF NOT EXISTS master_audit_events_v011 (id varchar(36) PRIMARY KEY,master_entity_id varchar(36),actor_id varchar(36),action varchar(80),before_data json NOT NULL,after_data json NOT NULL,details json NOT NULL,created_at timestamp NOT NULL);