-- Sensitivity Labels Extended Schema
-- 010_sensitivity_labels.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Sensitivity Labels (extended labels table with Microsoft Purview-like features)
CREATE TABLE IF NOT EXISTS sensitivity_labels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  priority INT DEFAULT 0,
  scope TEXT[] DEFAULT '{}', -- ['files', 'emails', 'groups_sites']
  description_for_users TEXT,
  description_for_admins TEXT,
  color VARCHAR(7), -- hex color
  control_access BOOLEAN DEFAULT FALSE,
  apply_content_marking BOOLEAN DEFAULT FALSE,
  auto_labeling_enabled BOOLEAN DEFAULT FALSE,
  auto_labeling_conditions JSONB DEFAULT '{}',
  privacy_settings JSONB DEFAULT '{}',
  external_sharing_settings JSONB DEFAULT '{}',
  groups_sites_settings JSONB DEFAULT '{}',
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Label Publishing Policies
CREATE TABLE IF NOT EXISTS label_publishing_policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  label_ids UUID[] DEFAULT '{}',
  admin_unit_ids UUID[] DEFAULT '{}',
  user_ids UUID[] DEFAULT '{}',
  group_ids UUID[] DEFAULT '{}',
  default_label_documents UUID REFERENCES sensitivity_labels(id) ON DELETE SET NULL,
  default_label_emails UUID REFERENCES sensitivity_labels(id) ON DELETE SET NULL,
  default_label_meetings UUID REFERENCES sensitivity_labels(id) ON DELETE SET NULL,
  status VARCHAR(50) DEFAULT 'draft', -- 'draft', 'published', 'archived'
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Discovery Strategies
CREATE TABLE IF NOT EXISTS discovery_strategies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  objectives TEXT,
  business_problem TEXT,
  desired_insights TEXT,
  data_storage_scope UUID[] DEFAULT '{}', -- source_ids
  discovery_approach VARCHAR(50), -- 'manual', 'automated', 'hybrid'
  priority VARCHAR(20), -- 'high', 'medium', 'low'
  status VARCHAR(50) DEFAULT 'draft', -- 'draft', 'active', 'completed', 'archived'
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_sensitivity_labels_priority ON sensitivity_labels(priority DESC);
CREATE INDEX IF NOT EXISTS idx_sensitivity_labels_scope ON sensitivity_labels USING GIN(scope);
CREATE INDEX IF NOT EXISTS idx_sensitivity_labels_created_by ON sensitivity_labels(created_by);
CREATE INDEX IF NOT EXISTS idx_label_publishing_policies_status ON label_publishing_policies(status);
CREATE INDEX IF NOT EXISTS idx_label_publishing_policies_label_ids ON label_publishing_policies USING GIN(label_ids);
CREATE INDEX IF NOT EXISTS idx_discovery_strategies_status ON discovery_strategies(status);
CREATE INDEX IF NOT EXISTS idx_discovery_strategies_created_by ON discovery_strategies(created_by);

-- Link sensitivity_labels to existing labels table (optional, for backward compatibility)
-- We can use labels table for basic labels and sensitivity_labels for extended features
-- Or merge them - for now, we'll keep both and link them via name

