-- PostgreSQL schema for the model fine-tuning platform.
-- Recommended PostgreSQL version: 14+.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

CREATE SCHEMA IF NOT EXISTS ft_platform;
SET search_path TO ft_platform, public;

-- =========================
-- Common helpers
-- =========================

CREATE OR REPLACE FUNCTION ft_platform.set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ft_platform.touch_updated_at(table_name regclass)
RETURNS void AS $$
BEGIN
  EXECUTE format('DROP TRIGGER IF EXISTS trg_set_updated_at ON %s', table_name);
  EXECUTE format(
    'CREATE TRIGGER trg_set_updated_at BEFORE UPDATE ON %s
     FOR EACH ROW EXECUTE FUNCTION ft_platform.set_updated_at()',
    table_name
  );
END;
$$ LANGUAGE plpgsql;

-- =========================
-- Enums
-- =========================

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_role') THEN
    CREATE TYPE user_role AS ENUM ('admin', 'operator', 'viewer');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_status') THEN
    CREATE TYPE user_status AS ENUM ('active', 'disabled');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'task_status') THEN
    CREATE TYPE task_status AS ENUM ('pending', 'running', 'completed', 'failed', 'stopped');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'model_type') THEN
    CREATE TYPE model_type AS ENUM ('LLM', 'CV', 'NLP', 'Embedding', 'Other');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'model_purpose') THEN
    CREATE TYPE model_purpose AS ENUM ('training', 'inference', 'evaluation');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'model_source') THEN
    CREATE TYPE model_source AS ENUM ('local', 'api');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'dataset_type') THEN
    CREATE TYPE dataset_type AS ENUM ('train', 'test', 'eval', 'val', 'other');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'dataset_storage') THEN
    CREATE TYPE dataset_storage AS ENUM ('local', 'minio', 'cloud');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'dataset_source') THEN
    CREATE TYPE dataset_source AS ENUM ('upload', 'task');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'train_type') THEN
    CREATE TYPE train_type AS ENUM ('SFT', 'DPO', 'CPT');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'train_method') THEN
    CREATE TYPE train_method AS ENUM ('lora', 'qlora', 'full', 'prefix', 'adapter', 'peft', 'adalora', 'longlora');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'process_type') THEN
    CREATE TYPE process_type AS ENUM ('structured', 'unstructured', 'external');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'eval_type') THEN
    CREATE TYPE eval_type AS ENUM ('custom', 'baseline');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'dimension_type') THEN
    CREATE TYPE dimension_type AS ENUM ('classification', 'metric', 'text_similarity');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'result_status') THEN
    CREATE TYPE result_status AS ENUM ('valid', 'modified', 'invalid');
  END IF;
END $$;

-- =========================
-- User center and RBAC
-- =========================

CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  username citext NOT NULL UNIQUE,
  display_name varchar(100) NOT NULL,
  password_hash text NOT NULL,
  role user_role NOT NULL DEFAULT 'viewer',
  status user_status NOT NULL DEFAULT 'active',
  protected boolean NOT NULL DEFAULT false,
  last_login_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('users');

CREATE TABLE IF NOT EXISTS permissions (
  code varchar(64) PRIMARY KEY,
  name varchar(100) NOT NULL,
  description text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS role_permissions (
  role user_role NOT NULL,
  permission_code varchar(64) NOT NULL REFERENCES permissions(code) ON DELETE CASCADE,
  PRIMARY KEY (role, permission_code)
);

CREATE TABLE IF NOT EXISTS user_permissions (
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  permission_code varchar(64) NOT NULL REFERENCES permissions(code) ON DELETE CASCADE,
  allowed boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, permission_code)
);

CREATE TABLE IF NOT EXISTS login_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_jti uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  ip inet,
  user_agent text,
  expires_at timestamptz NOT NULL,
  revoked_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_login_sessions_user_created ON login_sessions(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_login_sessions_expires ON login_sessions(expires_at) WHERE revoked_at IS NULL;

INSERT INTO permissions(code, name, description) VALUES
  ('dashboard', '服务看板', '访问服务看板'),
  ('fine-tune', '模型训练', '创建和管理微调任务'),
  ('model-eval', '模型评测', '创建和管理评测任务'),
  ('model-inference', '模型推理', '创建推理与模型对比任务'),
  ('model-manage', '模型管理', '登记、编辑、删除模型'),
  ('dataset', '数据集管理', '上传、编辑、下载数据集'),
  ('data-process', '数据处理', '创建和管理数据处理任务'),
  ('data-convert', '数据转换/工具', '使用数据转换和工具中心'),
  ('hardware', '平台性能', '查看硬件和系统监控'),
  ('logs', '查看日志', '查看系统与训练日志'),
  ('user-settings', '用户设置', '管理用户和权限')
ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, description = EXCLUDED.description;

INSERT INTO role_permissions(role, permission_code)
SELECT 'admin'::user_role, code FROM permissions
ON CONFLICT DO NOTHING;

INSERT INTO role_permissions(role, permission_code) VALUES
  ('operator', 'dashboard'),
  ('operator', 'fine-tune'),
  ('operator', 'model-eval'),
  ('operator', 'model-inference'),
  ('operator', 'model-manage'),
  ('operator', 'dataset'),
  ('operator', 'data-process'),
  ('operator', 'data-convert'),
  ('operator', 'hardware'),
  ('operator', 'logs'),
  ('viewer', 'dashboard'),
  ('viewer', 'model-eval'),
  ('viewer', 'model-inference'),
  ('viewer', 'dataset'),
  ('viewer', 'hardware'),
  ('viewer', 'logs')
ON CONFLICT DO NOTHING;

-- Replace this password hash during deployment.
INSERT INTO users(username, display_name, password_hash, role, status, protected)
VALUES ('admin', '系统管理员', '$argon2id$replace-with-real-hash', 'admin', 'active', true)
ON CONFLICT (username) DO NOTHING;

-- =========================
-- Audit and operation logs
-- =========================

CREATE TABLE IF NOT EXISTS audit_logs (
  id bigserial PRIMARY KEY,
  user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  username citext,
  action varchar(100) NOT NULL,
  resource_type varchar(80) NOT NULL,
  resource_id text,
  request_method varchar(12),
  request_path text,
  ip inet,
  user_agent text,
  success boolean NOT NULL DEFAULT true,
  error_message text,
  before_data jsonb,
  after_data jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_created ON audit_logs(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created ON audit_logs(created_at DESC);

CREATE TABLE IF NOT EXISTS web_logs (
  id bigserial PRIMARY KEY,
  user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  level varchar(20) NOT NULL,
  message text NOT NULL,
  page text,
  context jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_web_logs_created ON web_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_web_logs_level_created ON web_logs(level, created_at DESC);

-- =========================
-- Files and object storage metadata
-- =========================

CREATE TABLE IF NOT EXISTS storage_objects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  storage_type dataset_storage NOT NULL DEFAULT 'local',
  bucket varchar(128),
  object_key text NOT NULL,
  original_name text,
  mime_type varchar(200),
  byte_size bigint NOT NULL DEFAULT 0 CHECK (byte_size >= 0),
  checksum_sha256 char(64),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_storage_object_location
  ON storage_objects(storage_type, COALESCE(bucket, ''), object_key);
CREATE INDEX IF NOT EXISTS idx_storage_objects_checksum ON storage_objects(checksum_sha256);

-- =========================
-- Model registry
-- =========================

CREATE TABLE IF NOT EXISTS models (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(150) NOT NULL,
  type model_type NOT NULL DEFAULT 'LLM',
  purpose model_purpose NOT NULL,
  model_source model_source NOT NULL DEFAULT 'local',
  description text,
  path text,
  api_url text,
  api_key_encrypted text,
  online_model_name varchar(200),
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  CONSTRAINT ck_model_local_or_api CHECK (
    (model_source = 'local' AND path IS NOT NULL)
    OR
    (model_source = 'api' AND api_url IS NOT NULL AND online_model_name IS NOT NULL)
  )
);
SELECT touch_updated_at('models');
CREATE UNIQUE INDEX IF NOT EXISTS uq_models_name_alive ON models(name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_models_purpose ON models(purpose) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_models_type_source ON models(type, model_source) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS trained_models (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(150) NOT NULL,
  base_model_id uuid REFERENCES models(id) ON DELETE SET NULL,
  fine_tune_task_id uuid,
  train_method train_method,
  adapter_path text,
  merged boolean NOT NULL DEFAULT false,
  merging boolean NOT NULL DEFAULT false,
  merged_path text,
  export_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  metrics jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('trained_models');
CREATE UNIQUE INDEX IF NOT EXISTS uq_trained_models_name_alive ON trained_models(name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_trained_models_task ON trained_models(fine_tune_task_id);
CREATE INDEX IF NOT EXISTS idx_trained_models_base ON trained_models(base_model_id);

-- =========================
-- Datasets and versions
-- =========================

CREATE TABLE IF NOT EXISTS datasets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(150) NOT NULL,
  type dataset_type NOT NULL,
  storage_type dataset_storage NOT NULL DEFAULT 'local',
  source dataset_source NOT NULL DEFAULT 'upload',
  source_task_id uuid,
  size_bytes bigint NOT NULL DEFAULT 0 CHECK (size_bytes >= 0),
  record_count bigint NOT NULL DEFAULT 0 CHECK (record_count >= 0),
  description text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('datasets');
CREATE UNIQUE INDEX IF NOT EXISTS uq_datasets_name_alive ON datasets(name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_datasets_type_created ON datasets(type, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_datasets_source_task ON datasets(source_task_id) WHERE source = 'task';

CREATE TABLE IF NOT EXISTS dataset_files (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dataset_id uuid NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,
  name text NOT NULL,
  storage_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  current_version_id uuid,
  size_bytes bigint NOT NULL DEFAULT 0 CHECK (size_bytes >= 0),
  record_count bigint NOT NULL DEFAULT 0 CHECK (record_count >= 0),
  file_format varchar(40),
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('dataset_files');
CREATE UNIQUE INDEX IF NOT EXISTS uq_dataset_files_name_alive
  ON dataset_files(dataset_id, name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_dataset_files_dataset ON dataset_files(dataset_id) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS dataset_file_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dataset_file_id uuid NOT NULL REFERENCES dataset_files(id) ON DELETE CASCADE,
  version_no integer NOT NULL CHECK (version_no > 0),
  storage_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  content_preview text,
  description text,
  base_version_id uuid REFERENCES dataset_file_versions(id) ON DELETE SET NULL,
  size_bytes bigint NOT NULL DEFAULT 0 CHECK (size_bytes >= 0),
  record_count bigint NOT NULL DEFAULT 0 CHECK (record_count >= 0),
  checksum_sha256 char(64),
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_dataset_file_versions_no
  ON dataset_file_versions(dataset_file_id, version_no);
CREATE INDEX IF NOT EXISTS idx_dataset_file_versions_file_created
  ON dataset_file_versions(dataset_file_id, created_at DESC);

ALTER TABLE dataset_files
  DROP CONSTRAINT IF EXISTS fk_dataset_files_current_version;
ALTER TABLE dataset_files
  ADD CONSTRAINT fk_dataset_files_current_version
  FOREIGN KEY (current_version_id) REFERENCES dataset_file_versions(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS dataset_records (
  id bigserial PRIMARY KEY,
  dataset_id uuid NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,
  dataset_file_id uuid REFERENCES dataset_files(id) ON DELETE CASCADE,
  version_id uuid REFERENCES dataset_file_versions(id) ON DELETE CASCADE,
  line_no integer,
  split varchar(20),
  instruction text,
  input text,
  output text,
  raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  status result_status NOT NULL DEFAULT 'valid',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_dataset_records_dataset_id ON dataset_records(dataset_id, id);
CREATE INDEX IF NOT EXISTS idx_dataset_records_file_version ON dataset_records(dataset_file_id, version_id, line_no);
CREATE INDEX IF NOT EXISTS idx_dataset_records_split ON dataset_records(dataset_id, split);
CREATE INDEX IF NOT EXISTS idx_dataset_records_raw_gin ON dataset_records USING gin(raw);

-- =========================
-- Data processing
-- =========================

CREATE TABLE IF NOT EXISTS data_process_tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(150) NOT NULL,
  description text,
  status task_status NOT NULL DEFAULT 'pending',
  process_type process_type NOT NULL,
  source_dataset_id uuid REFERENCES datasets(id) ON DELETE SET NULL,
  output_dataset_id uuid REFERENCES datasets(id) ON DELETE SET NULL,
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  progress numeric(5,2) NOT NULL DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
  input_count bigint NOT NULL DEFAULT 0,
  output_count bigint NOT NULL DEFAULT 0,
  filtered_count bigint NOT NULL DEFAULT 0,
  duplicate_count bigint NOT NULL DEFAULT 0,
  error_count bigint NOT NULL DEFAULT 0,
  failure_reason text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('data_process_tasks');
CREATE UNIQUE INDEX IF NOT EXISTS uq_data_process_tasks_name_alive
  ON data_process_tasks(name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_data_process_tasks_status_created
  ON data_process_tasks(status, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_data_process_tasks_creator_created
  ON data_process_tasks(created_by, created_at DESC) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS data_process_source_files (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id uuid NOT NULL REFERENCES data_process_tasks(id) ON DELETE CASCADE,
  storage_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  name text NOT NULL,
  size_bytes bigint NOT NULL DEFAULT 0,
  record_count bigint NOT NULL DEFAULT 0,
  content_preview text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_data_process_source_files_task ON data_process_source_files(task_id);

CREATE TABLE IF NOT EXISTS data_process_preview_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id uuid NOT NULL REFERENCES data_process_tasks(id) ON DELETE CASCADE,
  source_file_id uuid REFERENCES data_process_source_files(id) ON DELETE CASCADE,
  original_content text NOT NULL DEFAULT '',
  edited_content text NOT NULL DEFAULT '',
  source_start integer,
  source_end integer,
  source_start_line integer,
  source_end_line integer,
  token_count integer NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'original',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('data_process_preview_items');
CREATE INDEX IF NOT EXISTS idx_data_process_preview_task_file
  ON data_process_preview_items(task_id, source_file_id, created_at);

CREATE TABLE IF NOT EXISTS data_process_results (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id uuid NOT NULL REFERENCES data_process_tasks(id) ON DELETE CASCADE,
  preview_item_id uuid REFERENCES data_process_preview_items(id) ON DELETE SET NULL,
  instruction text NOT NULL,
  input text NOT NULL DEFAULT '',
  output text NOT NULL,
  original_instruction text,
  original_input text,
  original_output text,
  status result_status NOT NULL DEFAULT 'valid',
  error text,
  split varchar(20),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('data_process_results');
CREATE INDEX IF NOT EXISTS idx_data_process_results_task_status
  ON data_process_results(task_id, status, id);
CREATE INDEX IF NOT EXISTS idx_data_process_results_task_split
  ON data_process_results(task_id, split);

-- =========================
-- Fine-tune tasks
-- =========================

CREATE TABLE IF NOT EXISTS fine_tune_tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(150) NOT NULL,
  description text,
  status task_status NOT NULL DEFAULT 'pending',
  train_type train_type NOT NULL,
  train_method train_method NOT NULL DEFAULT 'lora',
  template varchar(80) NOT NULL DEFAULT 'qwen',
  base_model_id uuid NOT NULL REFERENCES models(id) ON DELETE RESTRICT,
  train_dataset_id uuid REFERENCES datasets(id) ON DELETE SET NULL,
  auto_merge boolean NOT NULL DEFAULT false,
  output_model_name varchar(150),
  gpus integer[] NOT NULL DEFAULT '{}',
  params jsonb NOT NULL DEFAULT '{}'::jsonb,
  progress numeric(5,2) NOT NULL DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
  process_id integer,
  command text,
  output_dir text,
  log_file text,
  train_duration_seconds integer,
  failure_reason text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('fine_tune_tasks');
CREATE UNIQUE INDEX IF NOT EXISTS uq_fine_tune_tasks_name_alive
  ON fine_tune_tasks(name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_fine_tune_tasks_status_created
  ON fine_tune_tasks(status, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_fine_tune_tasks_dataset ON fine_tune_tasks(train_dataset_id);
CREATE INDEX IF NOT EXISTS idx_fine_tune_tasks_base_model ON fine_tune_tasks(base_model_id);

ALTER TABLE trained_models
  DROP CONSTRAINT IF EXISTS fk_trained_models_fine_tune_task;
ALTER TABLE trained_models
  ADD CONSTRAINT fk_trained_models_fine_tune_task
  FOREIGN KEY (fine_tune_task_id) REFERENCES fine_tune_tasks(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS fine_tune_metrics (
  id bigserial PRIMARY KEY,
  task_id uuid NOT NULL REFERENCES fine_tune_tasks(id) ON DELETE CASCADE,
  step integer,
  epoch numeric(10,4),
  loss numeric(18,8),
  learning_rate numeric(18,12),
  grad_norm numeric(18,8),
  metrics jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_fine_tune_metrics_task_step ON fine_tune_metrics(task_id, step);
CREATE INDEX IF NOT EXISTS idx_fine_tune_metrics_task_created ON fine_tune_metrics(task_id, created_at);

-- =========================
-- Inference and compare
-- =========================

CREATE TABLE IF NOT EXISTS inference_tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(150) NOT NULL,
  description text,
  status task_status NOT NULL DEFAULT 'pending',
  load_status jsonb NOT NULL DEFAULT '{"loaded_models":[]}'::jsonb,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('inference_tasks');
CREATE INDEX IF NOT EXISTS idx_inference_tasks_status_created
  ON inference_tasks(status, created_at DESC) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS inference_task_models (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id uuid NOT NULL REFERENCES inference_tasks(id) ON DELETE CASCADE,
  model_id uuid REFERENCES models(id) ON DELETE SET NULL,
  trained_model_id uuid REFERENCES trained_models(id) ON DELETE SET NULL,
  model_name varchar(150) NOT NULL,
  model_path text,
  gpu_id integer,
  source varchar(40),
  port integer,
  pid integer,
  status varchar(40) NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('inference_task_models');
CREATE INDEX IF NOT EXISTS idx_inference_task_models_task ON inference_task_models(task_id);
CREATE INDEX IF NOT EXISTS idx_inference_task_models_pid ON inference_task_models(pid) WHERE pid IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_inference_task_models_port_alive
  ON inference_task_models(port) WHERE port IS NOT NULL AND status IN ('loading', 'ready');

CREATE TABLE IF NOT EXISTS chat_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  inference_task_id uuid REFERENCES inference_tasks(id) ON DELETE SET NULL,
  title varchar(200),
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('chat_sessions');
CREATE INDEX IF NOT EXISTS idx_chat_sessions_task_created ON chat_sessions(inference_task_id, created_at DESC);

CREATE TABLE IF NOT EXISTS chat_messages (
  id bigserial PRIMARY KEY,
  session_id uuid NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
  model_id uuid REFERENCES models(id) ON DELETE SET NULL,
  trained_model_id uuid REFERENCES trained_models(id) ON DELETE SET NULL,
  role varchar(20) NOT NULL,
  content text NOT NULL,
  latency_ms integer,
  token_usage jsonb NOT NULL DEFAULT '{}'::jsonb,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_chat_messages_session_id ON chat_messages(session_id, id);

-- =========================
-- Evaluation
-- =========================

CREATE TABLE IF NOT EXISTS eval_dimensions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(150) NOT NULL,
  type dimension_type NOT NULL,
  description text,
  eval_model_id uuid REFERENCES models(id) ON DELETE SET NULL,
  eval_method jsonb NOT NULL DEFAULT '[]'::jsonb,
  eval_prompt text,
  is_active boolean NOT NULL DEFAULT true,
  is_default boolean NOT NULL DEFAULT false,
  bleu_n integer,
  output_precision integer NOT NULL DEFAULT 3,
  score_min numeric(12,4),
  score_max numeric(12,4),
  pass_threshold numeric(12,4),
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('eval_dimensions');
CREATE UNIQUE INDEX IF NOT EXISTS uq_eval_dimensions_name_alive
  ON eval_dimensions(name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_eval_dimensions_active ON eval_dimensions(is_active) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS eval_tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  eval_task_name varchar(150) NOT NULL,
  eval_type eval_type NOT NULL DEFAULT 'custom',
  model_id uuid REFERENCES models(id) ON DELETE SET NULL,
  trained_model_id uuid REFERENCES trained_models(id) ON DELETE SET NULL,
  dataset_id uuid REFERENCES datasets(id) ON DELETE SET NULL,
  dimension_id uuid REFERENCES eval_dimensions(id) ON DELETE SET NULL,
  gpu_id integer,
  data_source varchar(40) NOT NULL DEFAULT 'dataset',
  leaderboard boolean NOT NULL DEFAULT false,
  basic_metrics jsonb NOT NULL DEFAULT '{}'::jsonb,
  status task_status NOT NULL DEFAULT 'pending',
  metric varchar(80),
  score numeric(12,4),
  overall_score numeric(12,4),
  overall_score_max numeric(12,4),
  overall_evaluation text,
  improvement_suggestions jsonb NOT NULL DEFAULT '[]'::jsonb,
  sample_count integer NOT NULL DEFAULT 0,
  completed_count integer NOT NULL DEFAULT 0,
  passed_count integer NOT NULL DEFAULT 0,
  failure_reason text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('eval_tasks');
CREATE UNIQUE INDEX IF NOT EXISTS uq_eval_tasks_name_alive
  ON eval_tasks(eval_task_name) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_eval_tasks_status_created ON eval_tasks(status, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_eval_tasks_model_dataset ON eval_tasks(model_id, dataset_id);

CREATE TABLE IF NOT EXISTS eval_dimension_summaries (
  id bigserial PRIMARY KEY,
  eval_task_id uuid NOT NULL REFERENCES eval_tasks(id) ON DELETE CASCADE,
  name varchar(150) NOT NULL,
  score numeric(12,4),
  max_score numeric(12,4),
  pass_rate numeric(6,2),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_eval_dimension_summaries_task ON eval_dimension_summaries(eval_task_id);

CREATE TABLE IF NOT EXISTS eval_sample_results (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  eval_task_id uuid NOT NULL REFERENCES eval_tasks(id) ON DELETE CASCADE,
  sample_index integer NOT NULL,
  input text NOT NULL,
  reference_answer text,
  model_output text NOT NULL DEFAULT '',
  score numeric(12,4),
  max_score numeric(12,4),
  passed boolean,
  status task_status NOT NULL DEFAULT 'pending',
  judgement varchar(40),
  evaluation_reason text,
  error_type varchar(80),
  dimension_scores jsonb NOT NULL DEFAULT '[]'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('eval_sample_results');
CREATE UNIQUE INDEX IF NOT EXISTS uq_eval_sample_results_task_index
  ON eval_sample_results(eval_task_id, sample_index);
CREATE INDEX IF NOT EXISTS idx_eval_sample_results_task_status
  ON eval_sample_results(eval_task_id, status);

-- =========================
-- Data convert and custom tools
-- =========================

CREATE TABLE IF NOT EXISTS data_convert_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  convert_type varchar(40) NOT NULL DEFAULT 'json_to_jsonl',
  status task_status NOT NULL DEFAULT 'pending',
  output_name varchar(200) NOT NULL,
  encoding varchar(40) NOT NULL DEFAULT 'UTF-8',
  source_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  result_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  error_message text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('data_convert_jobs');
CREATE INDEX IF NOT EXISTS idx_data_convert_jobs_user_created ON data_convert_jobs(created_by, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_data_convert_jobs_status ON data_convert_jobs(status, created_at DESC);

CREATE TABLE IF NOT EXISTS custom_tools (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name varchar(100) NOT NULL,
  description text,
  url text NOT NULL,
  icon varchar(80) NOT NULL DEFAULT 'fa-cog',
  visibility varchar(20) NOT NULL DEFAULT 'private',
  owner_id uuid REFERENCES users(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('custom_tools');
CREATE INDEX IF NOT EXISTS idx_custom_tools_owner ON custom_tools(owner_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_custom_tools_visibility ON custom_tools(visibility) WHERE deleted_at IS NULL;

-- =========================
-- Metrics snapshots
-- =========================

CREATE TABLE IF NOT EXISTS system_metric_snapshots (
  id bigserial PRIMARY KEY,
  cpu jsonb NOT NULL DEFAULT '{}'::jsonb,
  memory jsonb NOT NULL DEFAULT '{}'::jsonb,
  disk jsonb NOT NULL DEFAULT '{}'::jsonb,
  gpu jsonb NOT NULL DEFAULT '[]'::jsonb,
  network jsonb NOT NULL DEFAULT '{}'::jsonb,
  system jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_system_metric_snapshots_created ON system_metric_snapshots(created_at DESC);

CREATE TABLE IF NOT EXISTS service_status_snapshots (
  id bigserial PRIMARY KEY,
  service_name varchar(100) NOT NULL,
  state varchar(30) NOT NULL,
  instances_online integer NOT NULL DEFAULT 0,
  instances_total integer NOT NULL DEFAULT 0,
  detail jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_service_status_snapshots_name_created
  ON service_status_snapshots(service_name, created_at DESC);

-- =========================
-- Useful views
-- =========================

CREATE OR REPLACE VIEW v_user_effective_permissions AS
SELECT
  u.id AS user_id,
  u.username,
  p.code AS permission_code,
  COALESCE(up.allowed, rp.permission_code IS NOT NULL, false) AS allowed
FROM users u
CROSS JOIN permissions p
LEFT JOIN role_permissions rp
  ON rp.role = u.role AND rp.permission_code = p.code
LEFT JOIN user_permissions up
  ON up.user_id = u.id AND up.permission_code = p.code
WHERE u.deleted_at IS NULL;

CREATE OR REPLACE VIEW v_dataset_summary AS
SELECT
  d.id,
  d.name,
  d.type,
  d.storage_type,
  d.source,
  d.source_task_id,
  d.size_bytes,
  d.record_count,
  d.description,
  count(df.id) FILTER (WHERE df.deleted_at IS NULL) AS file_count,
  d.created_at,
  d.updated_at
FROM datasets d
LEFT JOIN dataset_files df ON df.dataset_id = d.id
WHERE d.deleted_at IS NULL
GROUP BY d.id;

-- =========================
-- Maintenance notes
-- =========================

-- 1. For very large installations, convert audit_logs, web_logs,
--    system_metric_snapshots, fine_tune_metrics and eval_sample_results to
--    monthly/range partitions.
-- 2. Keep large file bodies in storage_objects, not in relational rows.
-- 3. Encrypt api_key_encrypted and external source secrets at the application layer
--    with KMS or a deployment secret.
-- 4. Use soft delete for user-facing resources to preserve audit trails.

-- =========================
-- Enterprise governance and compute extension
-- =========================

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'tenant_status') THEN
    CREATE TYPE tenant_status AS ENUM ('active', 'disabled');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'project_status') THEN
    CREATE TYPE project_status AS ENUM ('active', 'archived', 'disabled');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'project_role') THEN
    CREATE TYPE project_role AS ENUM ('owner', 'maintainer', 'developer', 'reviewer', 'viewer');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'approval_status') THEN
    CREATE TYPE approval_status AS ENUM ('not_required', 'pending', 'approved', 'rejected', 'cancelled', 'expired');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'compute_job_type') THEN
    CREATE TYPE compute_job_type AS ENUM ('fine_tune', 'eval', 'data_process', 'inference', 'merge', 'convert', 'import');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'job_priority') THEN
    CREATE TYPE job_priority AS ENUM ('low', 'normal', 'high', 'urgent');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'gpu_status') THEN
    CREATE TYPE gpu_status AS ENUM ('idle', 'reserved', 'running', 'draining', 'offline', 'error');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'service_level') THEN
    CREATE TYPE service_level AS ENUM ('test', 'production');
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code citext NOT NULL UNIQUE,
  name varchar(150) NOT NULL,
  status tenant_status NOT NULL DEFAULT 'active',
  owner_id uuid REFERENCES users(id) ON DELETE SET NULL,
  quota_config jsonb NOT NULL DEFAULT '{}'::jsonb,
  retention_config jsonb NOT NULL DEFAULT '{}'::jsonb,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('tenants');
CREATE INDEX IF NOT EXISTS idx_tenants_status ON tenants(status) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS tenant_users (
  tenant_id uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role user_role NOT NULL DEFAULT 'viewer',
  is_tenant_admin boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_tenant_users_user ON tenant_users(user_id);

CREATE TABLE IF NOT EXISTS projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  code citext NOT NULL,
  name varchar(150) NOT NULL,
  description text,
  status project_status NOT NULL DEFAULT 'active',
  owner_id uuid REFERENCES users(id) ON DELETE SET NULL,
  quota_config jsonb NOT NULL DEFAULT '{}'::jsonb,
  default_acl jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  archived_at timestamptz,
  deleted_at timestamptz,
  UNIQUE (tenant_id, code)
);
SELECT touch_updated_at('projects');
CREATE INDEX IF NOT EXISTS idx_projects_tenant_status ON projects(tenant_id, status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_projects_owner ON projects(owner_id) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS project_members (
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role project_role NOT NULL DEFAULT 'viewer',
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (project_id, user_id)
);
SELECT touch_updated_at('project_members');
CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members(user_id);

CREATE TABLE IF NOT EXISTS storage_nodes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code citext NOT NULL UNIQUE,
  name varchar(150) NOT NULL,
  node_type varchar(40) NOT NULL DEFAULT 'compute_local',
  base_path text NOT NULL,
  total_bytes bigint,
  used_bytes bigint NOT NULL DEFAULT 0,
  status varchar(40) NOT NULL DEFAULT 'online',
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('storage_nodes');

CREATE TABLE IF NOT EXISTS training_engines (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code citext NOT NULL UNIQUE,
  name varchar(150) NOT NULL,
  version varchar(80),
  engine_type varchar(60) NOT NULL DEFAULT 'llama_factory',
  executable_path text,
  python_env_path text,
  supported_task_types jsonb NOT NULL DEFAULT '[]'::jsonb,
  supported_methods jsonb NOT NULL DEFAULT '[]'::jsonb,
  supported_formats jsonb NOT NULL DEFAULT '[]'::jsonb,
  schema jsonb NOT NULL DEFAULT '{}'::jsonb,
  status varchar(40) NOT NULL DEFAULT 'enabled',
  last_health_check_at timestamptz,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('training_engines');

INSERT INTO training_engines(
  code, name, engine_type, supported_task_types, supported_methods, supported_formats, status
) VALUES (
  'llama_factory',
  'LLaMA-Factory',
  'llama_factory',
  '["SFT", "DPO", "CPT"]'::jsonb,
  '["lora", "qlora", "full"]'::jsonb,
  '["alpaca", "sharegpt", "dpo_pair", "pretrain_text"]'::jsonb,
  'enabled'
) ON CONFLICT (code) DO NOTHING;

CREATE TABLE IF NOT EXISTS quotas (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  subject_type varchar(40) NOT NULL,
  subject_id uuid,
  gpu_concurrency integer NOT NULL DEFAULT 0,
  storage_bytes bigint NOT NULL DEFAULT 0,
  max_running_jobs integer NOT NULL DEFAULT 0,
  max_projects integer NOT NULL DEFAULT 0,
  max_upload_file_bytes bigint NOT NULL DEFAULT 0,
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (subject_type, subject_id)
);
SELECT touch_updated_at('quotas');
CREATE INDEX IF NOT EXISTS idx_quotas_tenant_project ON quotas(tenant_id, project_id);

CREATE TABLE IF NOT EXISTS quota_usage (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  subject_type varchar(40) NOT NULL,
  subject_id uuid,
  gpu_running integer NOT NULL DEFAULT 0,
  storage_used_bytes bigint NOT NULL DEFAULT 0,
  running_jobs integer NOT NULL DEFAULT 0,
  usage_detail jsonb NOT NULL DEFAULT '{}'::jsonb,
  measured_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (subject_type, subject_id)
);
CREATE INDEX IF NOT EXISTS idx_quota_usage_tenant_project ON quota_usage(tenant_id, project_id);

CREATE TABLE IF NOT EXISTS retention_policies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  audit_days integer NOT NULL DEFAULT 180,
  login_log_days integer NOT NULL DEFAULT 180,
  training_log_days integer NOT NULL DEFAULT 90,
  metric_raw_days integer NOT NULL DEFAULT 30,
  temp_file_days integer NOT NULL DEFAULT 1,
  failed_job_workspace_days integer NOT NULL DEFAULT 14,
  checkpoint_policy jsonb NOT NULL DEFAULT '{"keep_last":3,"keep_best":2,"failed_job_days":14}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, project_id)
);
SELECT touch_updated_at('retention_policies');

CREATE TABLE IF NOT EXISTS approval_templates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  action varchar(80) NOT NULL,
  name varchar(150) NOT NULL,
  enabled boolean NOT NULL DEFAULT true,
  approver_rules jsonb NOT NULL DEFAULT '[]'::jsonb,
  risk_level varchar(40) NOT NULL DEFAULT 'medium',
  expire_hours integer NOT NULL DEFAULT 24,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('approval_templates');
CREATE INDEX IF NOT EXISTS idx_approval_templates_scope_action
  ON approval_templates(tenant_id, project_id, action);

CREATE TABLE IF NOT EXISTS approval_instances (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  template_id uuid REFERENCES approval_templates(id) ON DELETE SET NULL,
  action varchar(80) NOT NULL,
  resource_type varchar(80) NOT NULL,
  resource_id text NOT NULL,
  reason text,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  status approval_status NOT NULL DEFAULT 'pending',
  requested_by uuid REFERENCES users(id) ON DELETE SET NULL,
  decided_by uuid REFERENCES users(id) ON DELETE SET NULL,
  decided_at timestamptz,
  expires_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('approval_instances');
CREATE INDEX IF NOT EXISTS idx_approval_instances_status_created
  ON approval_instances(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_approval_instances_scope
  ON approval_instances(tenant_id, project_id, action, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_approval_instances_resource
  ON approval_instances(resource_type, resource_id);

CREATE TABLE IF NOT EXISTS approval_steps (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  approval_id uuid NOT NULL REFERENCES approval_instances(id) ON DELETE CASCADE,
  step_no integer NOT NULL,
  approver_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  approver_role varchar(80),
  status approval_status NOT NULL DEFAULT 'pending',
  comment text,
  decided_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (approval_id, step_no)
);
CREATE INDEX IF NOT EXISTS idx_approval_steps_approver
  ON approval_steps(approver_user_id, status);

CREATE TABLE IF NOT EXISTS compute_nodes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code citext NOT NULL UNIQUE,
  name varchar(150) NOT NULL,
  host varchar(200) NOT NULL,
  api_base_url text NOT NULL,
  storage_node_id uuid REFERENCES storage_nodes(id) ON DELETE SET NULL,
  status varchar(40) NOT NULL DEFAULT 'online',
  agent_version varchar(80),
  gpu_count integer NOT NULL DEFAULT 0,
  last_heartbeat_at timestamptz,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('compute_nodes');
CREATE INDEX IF NOT EXISTS idx_compute_nodes_status ON compute_nodes(status);

CREATE TABLE IF NOT EXISTS gpu_devices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  compute_node_id uuid NOT NULL REFERENCES compute_nodes(id) ON DELETE CASCADE,
  gpu_index integer NOT NULL,
  uuid varchar(120) NOT NULL,
  name varchar(150) NOT NULL,
  status gpu_status NOT NULL DEFAULT 'idle',
  memory_total_mb integer NOT NULL DEFAULT 0,
  memory_used_mb integer NOT NULL DEFAULT 0,
  utilization_percent numeric(5,2) NOT NULL DEFAULT 0,
  temperature numeric(5,2),
  power_w numeric(8,2),
  driver_version varchar(80),
  partition_type varchar(40) NOT NULL DEFAULT 'full',
  parent_gpu_uuid varchar(120),
  current_job_id uuid,
  last_seen_at timestamptz,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (compute_node_id, gpu_index),
  UNIQUE (uuid)
);
SELECT touch_updated_at('gpu_devices');
CREATE INDEX IF NOT EXISTS idx_gpu_devices_node_status ON gpu_devices(compute_node_id, status);

CREATE TABLE IF NOT EXISTS compute_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  app_task_type varchar(80) NOT NULL,
  app_task_id uuid,
  tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL,
  project_id uuid REFERENCES projects(id) ON DELETE SET NULL,
  compute_node_id uuid REFERENCES compute_nodes(id) ON DELETE SET NULL,
  engine_id uuid REFERENCES training_engines(id) ON DELETE SET NULL,
  job_type compute_job_type NOT NULL,
  status task_status NOT NULL DEFAULT 'pending',
  priority job_priority NOT NULL DEFAULT 'normal',
  resource_request jsonb NOT NULL DEFAULT '{}'::jsonb,
  workspace_root text,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  result jsonb NOT NULL DEFAULT '{}'::jsonb,
  progress numeric(5,2) NOT NULL DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
  pid integer,
  port integer,
  failure_reason text,
  requested_by uuid REFERENCES users(id) ON DELETE SET NULL,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('compute_jobs');
CREATE INDEX IF NOT EXISTS idx_compute_jobs_scope_status
  ON compute_jobs(tenant_id, project_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_compute_jobs_node_status
  ON compute_jobs(compute_node_id, status, priority, created_at);
CREATE INDEX IF NOT EXISTS idx_compute_jobs_app_task
  ON compute_jobs(app_task_type, app_task_id);

CREATE TABLE IF NOT EXISTS gpu_allocations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  compute_job_id uuid NOT NULL REFERENCES compute_jobs(id) ON DELETE CASCADE,
  gpu_device_id uuid NOT NULL REFERENCES gpu_devices(id) ON DELETE RESTRICT,
  tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL,
  project_id uuid REFERENCES projects(id) ON DELETE SET NULL,
  status varchar(40) NOT NULL DEFAULT 'reserved',
  allocated_at timestamptz NOT NULL DEFAULT now(),
  released_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_gpu_allocations_job ON gpu_allocations(compute_job_id);
CREATE INDEX IF NOT EXISTS idx_gpu_allocations_scope ON gpu_allocations(tenant_id, project_id, allocated_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS uq_gpu_allocations_active_gpu
  ON gpu_allocations(gpu_device_id) WHERE released_at IS NULL;

-- Multi compute-node scheduling and local-cache metadata.
-- Each GPU server is modeled as one compute node. Nodes do not call each other;
-- the application platform schedules jobs and syncs resources through each node's File Gateway.
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS file_gateway_url text;
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS enabled boolean NOT NULL DEFAULT true;
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS scheduler_status varchar(40) NOT NULL DEFAULT 'online';
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS scheduler_weight integer NOT NULL DEFAULT 100 CHECK (scheduler_weight >= 0);
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS tags text[] NOT NULL DEFAULT '{}';
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS service_token_encrypted text;
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS data_root text NOT NULL DEFAULT '/data/yg-ft';
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS model_root text NOT NULL DEFAULT '/data/yg-ft/models';
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS log_root text NOT NULL DEFAULT '/opt/yg-ft/logs/compute';
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS max_parallel_jobs integer NOT NULL DEFAULT 1 CHECK (max_parallel_jobs >= 0);
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS current_running_jobs integer NOT NULL DEFAULT 0 CHECK (current_running_jobs >= 0);
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS last_health_check_at timestamptz;
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS health_detail jsonb NOT NULL DEFAULT '{}'::jsonb;
ALTER TABLE compute_nodes ADD COLUMN IF NOT EXISTS drain_reason text;
CREATE INDEX IF NOT EXISTS idx_compute_nodes_scheduler
  ON compute_nodes(enabled, scheduler_status, scheduler_weight DESC, last_health_check_at DESC);
CREATE INDEX IF NOT EXISTS idx_compute_nodes_tags_gin
  ON compute_nodes USING gin(tags);

ALTER TABLE compute_jobs ADD COLUMN IF NOT EXISTS scheduler_mode varchar(40) NOT NULL DEFAULT 'auto';
ALTER TABLE compute_jobs ADD COLUMN IF NOT EXISTS requested_node_id uuid REFERENCES compute_nodes(id) ON DELETE SET NULL;
ALTER TABLE compute_jobs ADD COLUMN IF NOT EXISTS assigned_at timestamptz;
ALTER TABLE compute_jobs ADD COLUMN IF NOT EXISTS scheduler_reason text;
CREATE INDEX IF NOT EXISTS idx_compute_jobs_requested_node
  ON compute_jobs(requested_node_id, status, created_at DESC);

CREATE TABLE IF NOT EXISTS compute_node_engines (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  compute_node_id uuid NOT NULL REFERENCES compute_nodes(id) ON DELETE CASCADE,
  engine_id uuid REFERENCES training_engines(id) ON DELETE SET NULL,
  engine_code varchar(80) NOT NULL,
  engine_version varchar(80),
  home_path text,
  status varchar(40) NOT NULL DEFAULT 'available',
  capability jsonb NOT NULL DEFAULT '{}'::jsonb,
  last_health_check_at timestamptz,
  health_detail jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (compute_node_id, engine_code)
);
SELECT touch_updated_at('compute_node_engines');
CREATE INDEX IF NOT EXISTS idx_compute_node_engines_node_status
  ON compute_node_engines(compute_node_id, status, engine_code);

CREATE TABLE IF NOT EXISTS resource_replicas (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL,
  project_id uuid REFERENCES projects(id) ON DELETE SET NULL,
  resource_type varchar(80) NOT NULL,
  resource_id uuid NOT NULL,
  storage_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  compute_node_id uuid NOT NULL REFERENCES compute_nodes(id) ON DELETE CASCADE,
  local_path text NOT NULL,
  status varchar(40) NOT NULL DEFAULT 'available',
  sync_status varchar(40) NOT NULL DEFAULT 'synced',
  checksum_sha256 char(64),
  byte_size bigint NOT NULL DEFAULT 0 CHECK (byte_size >= 0),
  version varchar(120),
  pinned boolean NOT NULL DEFAULT false,
  last_verified_at timestamptz,
  expires_at timestamptz,
  failure_reason text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (resource_type, resource_id, compute_node_id)
);
SELECT touch_updated_at('resource_replicas');
CREATE INDEX IF NOT EXISTS idx_resource_replicas_resource
  ON resource_replicas(resource_type, resource_id, status);
CREATE INDEX IF NOT EXISTS idx_resource_replicas_node_status
  ON resource_replicas(compute_node_id, status, sync_status, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_resource_replicas_scope
  ON resource_replicas(tenant_id, project_id, resource_type, updated_at DESC);

CREATE TABLE IF NOT EXISTS resource_sync_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL,
  project_id uuid REFERENCES projects(id) ON DELETE SET NULL,
  target_compute_node_id uuid NOT NULL REFERENCES compute_nodes(id) ON DELETE CASCADE,
  source_compute_node_id uuid REFERENCES compute_nodes(id) ON DELETE SET NULL,
  resource_type varchar(80) NOT NULL,
  resource_id uuid NOT NULL,
  storage_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  status task_status NOT NULL DEFAULT 'pending',
  transfer_mode varchar(40) NOT NULL DEFAULT 'app_proxy',
  source_uri text,
  target_path text NOT NULL,
  byte_size bigint NOT NULL DEFAULT 0 CHECK (byte_size >= 0),
  checksum_sha256 char(64),
  progress numeric(5,2) NOT NULL DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
  failure_reason text,
  requested_by uuid REFERENCES users(id) ON DELETE SET NULL,
  started_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('resource_sync_jobs');
CREATE INDEX IF NOT EXISTS idx_resource_sync_jobs_status
  ON resource_sync_jobs(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_resource_sync_jobs_target
  ON resource_sync_jobs(target_compute_node_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_resource_sync_jobs_resource
  ON resource_sync_jobs(resource_type, resource_id, status);

CREATE TABLE IF NOT EXISTS resource_acl (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  resource_type varchar(80) NOT NULL,
  resource_id text NOT NULL,
  subject_type varchar(40) NOT NULL,
  subject_id text NOT NULL,
  permissions text[] NOT NULL DEFAULT '{}',
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (resource_type, resource_id, subject_type, subject_id)
);
SELECT touch_updated_at('resource_acl');
CREATE INDEX IF NOT EXISTS idx_resource_acl_resource
  ON resource_acl(resource_type, resource_id);
CREATE INDEX IF NOT EXISTS idx_resource_acl_subject
  ON resource_acl(subject_type, subject_id);

CREATE TABLE IF NOT EXISTS file_upload_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  storage_node_id uuid REFERENCES storage_nodes(id) ON DELETE SET NULL,
  original_name text NOT NULL,
  byte_size bigint NOT NULL DEFAULT 0,
  checksum_sha256 char(64),
  part_size_bytes integer NOT NULL DEFAULT 8388608,
  uploaded_parts jsonb NOT NULL DEFAULT '[]'::jsonb,
  status varchar(40) NOT NULL DEFAULT 'uploading',
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  expires_at timestamptz NOT NULL DEFAULT now() + interval '1 day',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('file_upload_sessions');
CREATE INDEX IF NOT EXISTS idx_file_upload_sessions_scope_status
  ON file_upload_sessions(tenant_id, project_id, status, created_at DESC);

CREATE TABLE IF NOT EXISTS local_import_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  compute_node_id uuid REFERENCES compute_nodes(id) ON DELETE SET NULL,
  import_type varchar(40) NOT NULL,
  source_path text NOT NULL,
  target_resource_id uuid,
  status task_status NOT NULL DEFAULT 'pending',
  scan_result jsonb NOT NULL DEFAULT '{}'::jsonb,
  failure_reason text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
SELECT touch_updated_at('local_import_jobs');
CREATE INDEX IF NOT EXISTS idx_local_import_jobs_scope_status
  ON local_import_jobs(tenant_id, project_id, status, created_at DESC);

CREATE TABLE IF NOT EXISTS fine_tune_checkpoints (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  fine_tune_task_id uuid NOT NULL REFERENCES fine_tune_tasks(id) ON DELETE CASCADE,
  storage_object_id uuid REFERENCES storage_objects(id) ON DELETE SET NULL,
  checkpoint_name varchar(200) NOT NULL,
  step integer,
  metric_name varchar(80),
  metric_value numeric(18,8),
  is_best boolean NOT NULL DEFAULT false,
  protected boolean NOT NULL DEFAULT false,
  size_bytes bigint NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_fine_tune_checkpoints_task_step
  ON fine_tune_checkpoints(fine_tune_task_id, step DESC);
CREATE INDEX IF NOT EXISTS idx_fine_tune_checkpoints_best
  ON fine_tune_checkpoints(fine_tune_task_id, is_best) WHERE is_best;

CREATE TABLE IF NOT EXISTS model_services (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  model_id uuid REFERENCES models(id) ON DELETE SET NULL,
  trained_model_id uuid REFERENCES trained_models(id) ON DELETE SET NULL,
  compute_job_id uuid REFERENCES compute_jobs(id) ON DELETE SET NULL,
  name varchar(150) NOT NULL,
  service_level service_level NOT NULL DEFAULT 'test',
  status task_status NOT NULL DEFAULT 'pending',
  gpu_device_id uuid REFERENCES gpu_devices(id) ON DELETE SET NULL,
  port integer,
  max_concurrency integer NOT NULL DEFAULT 1,
  timeout_seconds integer NOT NULL DEFAULT 120,
  max_context_tokens integer,
  approval_id uuid REFERENCES approval_instances(id) ON DELETE SET NULL,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  started_at timestamptz,
  stopped_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
SELECT touch_updated_at('model_services');
CREATE INDEX IF NOT EXISTS idx_model_services_scope_status
  ON model_services(tenant_id, project_id, status, created_at DESC) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS cleanup_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE,
  cleanup_type varchar(80) NOT NULL,
  status task_status NOT NULL DEFAULT 'pending',
  target jsonb NOT NULL DEFAULT '{}'::jsonb,
  result jsonb NOT NULL DEFAULT '{}'::jsonb,
  failure_reason text,
  created_at timestamptz NOT NULL DEFAULT now(),
  started_at timestamptz,
  completed_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_cleanup_jobs_status_created ON cleanup_jobs(status, created_at DESC);

-- User identity provider extension. First release uses local accounts;
-- OIDC/LDAP can be enabled later without changing resource ownership tables.
ALTER TABLE users ADD COLUMN IF NOT EXISTS auth_provider varchar(40) NOT NULL DEFAULT 'local';
ALTER TABLE users ADD COLUMN IF NOT EXISTS external_id text;
ALTER TABLE users ADD COLUMN IF NOT EXISTS default_tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_users_external_provider ON users(auth_provider, external_id);
CREATE INDEX IF NOT EXISTS idx_users_default_tenant ON users(default_tenant_id);

-- Add tenant/project/resource governance columns to existing resource tables.
ALTER TABLE storage_objects ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE storage_objects ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE storage_objects ADD COLUMN IF NOT EXISTS storage_node_id uuid REFERENCES storage_nodes(id) ON DELETE SET NULL;
ALTER TABLE storage_objects ADD COLUMN IF NOT EXISTS visibility varchar(40) NOT NULL DEFAULT 'project';
ALTER TABLE storage_objects ADD COLUMN IF NOT EXISTS deleted_at timestamptz;
CREATE INDEX IF NOT EXISTS idx_storage_objects_scope
  ON storage_objects(tenant_id, project_id, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE models ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE models ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE models ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE models ADD COLUMN IF NOT EXISTS visibility varchar(40) NOT NULL DEFAULT 'project';
ALTER TABLE models ADD COLUMN IF NOT EXISTS approval_status approval_status NOT NULL DEFAULT 'not_required';
CREATE INDEX IF NOT EXISTS idx_models_scope_status
  ON models(tenant_id, project_id, approval_status, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE trained_models ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE trained_models ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE trained_models ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE trained_models ADD COLUMN IF NOT EXISTS visibility varchar(40) NOT NULL DEFAULT 'project';
ALTER TABLE trained_models ADD COLUMN IF NOT EXISTS approval_status approval_status NOT NULL DEFAULT 'not_required';
CREATE INDEX IF NOT EXISTS idx_trained_models_scope
  ON trained_models(tenant_id, project_id, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE datasets ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE datasets ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE datasets ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE datasets ADD COLUMN IF NOT EXISTS visibility varchar(40) NOT NULL DEFAULT 'project';
ALTER TABLE datasets ADD COLUMN IF NOT EXISTS approval_status approval_status NOT NULL DEFAULT 'not_required';
CREATE INDEX IF NOT EXISTS idx_datasets_scope_status
  ON datasets(tenant_id, project_id, type, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE dataset_files ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE dataset_files ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_dataset_files_scope
  ON dataset_files(tenant_id, project_id, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE data_process_tasks ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE data_process_tasks ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE data_process_tasks ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE data_process_tasks ADD COLUMN IF NOT EXISTS approval_status approval_status NOT NULL DEFAULT 'not_required';
ALTER TABLE data_process_tasks ADD COLUMN IF NOT EXISTS compute_job_id uuid REFERENCES compute_jobs(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_data_process_tasks_scope_status
  ON data_process_tasks(tenant_id, project_id, status, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE fine_tune_tasks ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE fine_tune_tasks ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE fine_tune_tasks ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE fine_tune_tasks ADD COLUMN IF NOT EXISTS approval_status approval_status NOT NULL DEFAULT 'not_required';
ALTER TABLE fine_tune_tasks ADD COLUMN IF NOT EXISTS compute_job_id uuid REFERENCES compute_jobs(id) ON DELETE SET NULL;
ALTER TABLE fine_tune_tasks ADD COLUMN IF NOT EXISTS resume_checkpoint_id uuid REFERENCES fine_tune_checkpoints(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_fine_tune_tasks_scope_status
  ON fine_tune_tasks(tenant_id, project_id, status, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE inference_tasks ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE inference_tasks ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE inference_tasks ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE inference_tasks ADD COLUMN IF NOT EXISTS approval_status approval_status NOT NULL DEFAULT 'not_required';
ALTER TABLE inference_tasks ADD COLUMN IF NOT EXISTS compute_job_id uuid REFERENCES compute_jobs(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_inference_tasks_scope_status
  ON inference_tasks(tenant_id, project_id, status, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE eval_tasks ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE eval_tasks ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE eval_tasks ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE eval_tasks ADD COLUMN IF NOT EXISTS approval_status approval_status NOT NULL DEFAULT 'not_required';
ALTER TABLE eval_tasks ADD COLUMN IF NOT EXISTS compute_job_id uuid REFERENCES compute_jobs(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_eval_tasks_scope_status
  ON eval_tasks(tenant_id, project_id, status, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE data_convert_jobs ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE data_convert_jobs ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE data_convert_jobs ADD COLUMN IF NOT EXISTS owner_id uuid REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE data_convert_jobs ADD COLUMN IF NOT EXISTS compute_job_id uuid REFERENCES compute_jobs(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_data_convert_jobs_scope_status
  ON data_convert_jobs(tenant_id, project_id, status, created_at DESC);

ALTER TABLE custom_tools ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE custom_tools ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_custom_tools_scope
  ON custom_tools(tenant_id, project_id, visibility, created_at DESC) WHERE deleted_at IS NULL;

ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS approval_id uuid REFERENCES approval_instances(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_audit_logs_scope_created
  ON audit_logs(tenant_id, project_id, created_at DESC);

ALTER TABLE web_logs ADD COLUMN IF NOT EXISTS tenant_id uuid REFERENCES tenants(id) ON DELETE SET NULL;
ALTER TABLE web_logs ADD COLUMN IF NOT EXISTS project_id uuid REFERENCES projects(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_web_logs_scope_created
  ON web_logs(tenant_id, project_id, created_at DESC);

CREATE OR REPLACE VIEW v_project_member_permissions AS
SELECT
  p.tenant_id,
  pm.project_id,
  pm.user_id,
  pm.role,
  CASE pm.role
    WHEN 'owner' THEN ARRAY['read','write','execute','download','delete','manage_acl']
    WHEN 'maintainer' THEN ARRAY['read','write','execute','download','delete']
    WHEN 'developer' THEN ARRAY['read','write','execute','download']
    WHEN 'reviewer' THEN ARRAY['read','download']
    ELSE ARRAY['read']
  END AS permissions
FROM project_members pm
JOIN projects p ON p.id = pm.project_id
WHERE p.deleted_at IS NULL;