CREATE TABLE IF NOT EXISTS form_template (
  id UUID PRIMARY KEY,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  domain TEXT NOT NULL,
  status TEXT NOT NULL,
  current_version_code TEXT,
  tenant_id TEXT NOT NULL,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, code)
);

CREATE TABLE IF NOT EXISTS form_version (
  id UUID PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  form_template_code TEXT NOT NULL,
  semver TEXT NOT NULL,
  is_live BOOLEAN NOT NULL DEFAULT FALSE,
  schema_checksum TEXT NOT NULL,
  survey_json JSONB NOT NULL,
  normalized_schema_json JSONB NOT NULL,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (form_template_code, semver)
);

CREATE TABLE IF NOT EXISTS form_submission (
  id UUID PRIMARY KEY,
  submission_code TEXT NOT NULL UNIQUE,
  form_code TEXT NOT NULL,
  form_version TEXT NOT NULL,
  tenant_id TEXT NOT NULL,
  status TEXT NOT NULL,
  submitted_by TEXT NOT NULL,
  submitted_at TIMESTAMPTZ NOT NULL,
  channel TEXT NOT NULL,
  schema_checksum TEXT NOT NULL,
  deleted_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_submission_form ON form_submission (tenant_id, form_code, submitted_at DESC);
CREATE INDEX IF NOT EXISTS idx_submission_user ON form_submission (submitted_by, submitted_at DESC);

CREATE TABLE IF NOT EXISTS form_submission_answer (
  submission_id UUID NOT NULL REFERENCES form_submission(id),
  question_code TEXT NOT NULL,
  answer_type TEXT NOT NULL,
  answer_text TEXT,
  answer_number DOUBLE PRECISION,
  answer_boolean BOOLEAN,
  answer_date TIMESTAMPTZ,
  answer_json JSONB,
  PRIMARY KEY (submission_id, question_code)
);

CREATE TABLE IF NOT EXISTS form_submission_audit (
  id UUID PRIMARY KEY,
  submission_id UUID NOT NULL REFERENCES form_submission(id),
  event_type TEXT NOT NULL,
  event_at TIMESTAMPTZ NOT NULL,
  event_by TEXT NOT NULL,
  event_meta_json JSONB NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_audit_submission ON form_submission_audit (submission_id, event_at DESC);

CREATE TABLE IF NOT EXISTS master_data_domain (
  id UUID PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  hierarchical BOOLEAN NOT NULL DEFAULT FALSE,
  tenant_scoped BOOLEAN NOT NULL DEFAULT TRUE,
  version TEXT NOT NULL,
  deleted_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS master_data_item (
  id UUID PRIMARY KEY,
  domain_code TEXT NOT NULL,
  code TEXT NOT NULL,
  label TEXT NOT NULL,
  parent_code TEXT,
  path TEXT NOT NULL,
  depth INT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  sort_order INT NOT NULL DEFAULT 0,
  search_vector tsvector,
  payload_json JSONB NOT NULL,
  deleted_at TIMESTAMPTZ,
  UNIQUE (domain_code, code)
);

CREATE INDEX IF NOT EXISTS idx_master_parent ON master_data_item (domain_code, parent_code, sort_order);
CREATE INDEX IF NOT EXISTS idx_master_search_vector ON master_data_item USING GIN (search_vector);
