-- Create new Form Builder tables

CREATE TABLE IF NOT EXISTS forms (
  id SERIAL PRIMARY KEY,
  tenant_id INTEGER,
  branch_id INTEGER,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS form_elements (
  id SERIAL PRIMARY KEY,
  form_id INTEGER NOT NULL,
  tenant_id INTEGER,
  branch_id INTEGER,
  label VARCHAR(100) NOT NULL,
  field_name VARCHAR(100) NOT NULL,
  data_column VARCHAR(100),
  field_type VARCHAR(50) NOT NULL,
  is_required BOOLEAN NOT NULL DEFAULT FALSE,
  validation JSON,
  order_index INTEGER,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS element_types (
  id SERIAL PRIMARY KEY,
  tenant_id INTEGER,
  branch_id INTEGER,
  name VARCHAR(100) NOT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS element_options (
  id SERIAL PRIMARY KEY,
  element_id INTEGER NOT NULL,
  tenant_id INTEGER,
  branch_id INTEGER,
  label VARCHAR(100) NOT NULL,
  value VARCHAR(100) NOT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS form_submissions (
  id SERIAL PRIMARY KEY,
  form_id INTEGER NOT NULL,
  tenant_id INTEGER,
  branch_id INTEGER,
  submitted_by INTEGER,
  submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS form_submission_values (
  id SERIAL PRIMARY KEY,
  submission_id INTEGER NOT NULL,
  element_id INTEGER NOT NULL,
  tenant_id INTEGER,
  branch_id INTEGER,
  value TEXT,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
