CREATE TABLE IF NOT EXISTS discovery_jobs (
  id UUID PRIMARY KEY,
  source_id UUID REFERENCES sources(id),
  scan_type VARCHAR(20) NOT NULL,
  status VARCHAR(20) NOT NULL,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  eta TIMESTAMPTZ,
  total_files INT DEFAULT 0,
  processed_files INT DEFAULT 0,
  sensitive_detected INT DEFAULT 0,
  ocr_enabled BOOLEAN DEFAULT FALSE,
  ml_model VARCHAR(50),
  confidence_threshold FLOAT DEFAULT 0.75,
  created_by UUID,
  error_summary JSONB
);

CREATE TABLE IF NOT EXISTS documents (
  id UUID PRIMARY KEY,
  source_id UUID REFERENCES sources(id),
  file_path VARCHAR(1000) NOT NULL,
  file_name VARCHAR(500),
  file_type VARCHAR(50),
  file_size BIGINT,
  file_hash VARCHAR(64) UNIQUE,
  sensitivity VARCHAR(20),
  labels TEXT[],
  ocr_text_id UUID,
  metadata JSONB,
  ingested_at TIMESTAMPTZ DEFAULT NOW(),
  indexed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS document_texts (
  id UUID PRIMARY KEY,
  document_id UUID REFERENCES documents(id),
  source VARCHAR(20),
  full_text TEXT,
  language VARCHAR(10),
  page_count INT,
  extracted_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE documents
  ADD CONSTRAINT fk_documents_texts
  FOREIGN KEY (ocr_text_id) REFERENCES document_texts(id);

CREATE TABLE IF NOT EXISTS discovery_detections (
  id UUID PRIMARY KEY,
  job_id UUID REFERENCES discovery_jobs(id),
  document_id UUID REFERENCES documents(id),
  detection_type VARCHAR(50) NOT NULL,
  field_path VARCHAR(500),
  confidence FLOAT NOT NULL,
  matched_text_hash VARCHAR(64),
  severity VARCHAR(20),
  rule_id UUID,
  model_version VARCHAR(50),
  detected_at TIMESTAMPTZ DEFAULT NOW()
);


