-- V012 Enterprise Data Quality
CREATE TABLE IF NOT EXISTS quality_profiles_v012 (id varchar(36) PRIMARY KEY,dataset_id varchar(36) NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,run_id varchar(36) REFERENCES ingestion_runs(id) ON DELETE SET NULL,row_count integer NOT NULL DEFAULT 0,column_count integer NOT NULL DEFAULT 0,profile jsonb NOT NULL DEFAULT '{}'::jsonb,anomaly_summary jsonb NOT NULL DEFAULT '{}'::jsonb,created_by varchar(36) NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS enterprise_quality_rules_v012 (id varchar(36) PRIMARY KEY,dataset_id varchar(36) NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,name varchar(180) NOT NULL,description text NOT NULL DEFAULT '',dimension varchar(40) NOT NULL DEFAULT 'validity',rule_type varchar(60) NOT NULL,column_name varchar(255),config jsonb NOT NULL DEFAULT '{}'::jsonb,severity varchar(20) NOT NULL DEFAULT 'warning',weight integer NOT NULL DEFAULT 10,schedule_cron varchar(120),quarantine_on_failure boolean NOT NULL DEFAULT false,enabled boolean NOT NULL DEFAULT true,owner_id varchar(36) NOT NULL REFERENCES users(id),last_status varchar(30) NOT NULL DEFAULT 'not_run',last_score integer NOT NULL DEFAULT 0,last_run_at timestamptz,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS quality_runs_v012 (id varchar(36) PRIMARY KEY,dataset_id varchar(36) NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,trigger_type varchar(30) NOT NULL DEFAULT 'manual',status varchar(30) NOT NULL DEFAULT 'running',overall_score integer NOT NULL DEFAULT 0,dimension_scores jsonb NOT NULL DEFAULT '{}'::jsonb,total_rules integer NOT NULL DEFAULT 0,passed_rules integer NOT NULL DEFAULT 0,failed_rules integer NOT NULL DEFAULT 0,error text NOT NULL DEFAULT '',initiated_by varchar(36) NOT NULL REFERENCES users(id),started_at timestamptz NOT NULL DEFAULT now(),finished_at timestamptz);
CREATE TABLE IF NOT EXISTS quality_results_v012 (id varchar(36) PRIMARY KEY,quality_run_id varchar(36) NOT NULL REFERENCES quality_runs_v012(id) ON DELETE CASCADE,rule_id varchar(36) NOT NULL REFERENCES enterprise_quality_rules_v012(id) ON DELETE CASCADE,status varchar(30) NOT NULL,score integer NOT NULL DEFAULT 0,checked_rows integer NOT NULL DEFAULT 0,failed_rows integer NOT NULL DEFAULT 0,failure_rate integer NOT NULL DEFAULT 0,sample_failures jsonb NOT NULL DEFAULT '[]'::jsonb,metrics jsonb NOT NULL DEFAULT '{}'::jsonb,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS quality_incidents_v012 (id varchar(36) PRIMARY KEY,dataset_id varchar(36) NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,quality_run_id varchar(36) REFERENCES quality_runs_v012(id) ON DELETE SET NULL,rule_id varchar(36) REFERENCES enterprise_quality_rules_v012(id) ON DELETE SET NULL,title varchar(250) NOT NULL,description text NOT NULL DEFAULT '',severity varchar(20) NOT NULL DEFAULT 'warning',status varchar(30) NOT NULL DEFAULT 'open',owner_id varchar(36) REFERENCES users(id),due_at timestamptz,resolution text NOT NULL DEFAULT '',resolved_by varchar(36) REFERENCES users(id),resolved_at timestamptz,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS quality_quarantine_v012 (id varchar(36) PRIMARY KEY,dataset_id varchar(36) NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,quality_run_id varchar(36) NOT NULL REFERENCES quality_runs_v012(id) ON DELETE CASCADE,rule_id varchar(36) NOT NULL REFERENCES enterprise_quality_rules_v012(id) ON DELETE CASCADE,row_reference varchar(300) NOT NULL DEFAULT '',row_data jsonb NOT NULL DEFAULT '{}'::jsonb,reason text NOT NULL DEFAULT '',status varchar(30) NOT NULL DEFAULT 'quarantined',released_by varchar(36) REFERENCES users(id),released_at timestamptz,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS quality_remediation_plans_v012 (id varchar(36) PRIMARY KEY,incident_id varchar(36) NOT NULL REFERENCES quality_incidents_v012(id) ON DELETE CASCADE,title varchar(250) NOT NULL,actions jsonb NOT NULL DEFAULT '[]'::jsonb,owner_id varchar(36) NOT NULL REFERENCES users(id),status varchar(30) NOT NULL DEFAULT 'planned',target_date timestamptz,progress integer NOT NULL DEFAULT 0,created_by varchar(36) NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now(),updated_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS ix_quality_runs_dataset_v012 ON quality_runs_v012(dataset_id,started_at DESC);
CREATE INDEX IF NOT EXISTS ix_quality_incidents_status_v012 ON quality_incidents_v012(status,severity);