-- Fill (online) schema — run this once in phpMyAdmin against your database.
-- Questions and branching logic are stored as JSON inside the forms table rather
-- than as separate rows — this mirrors how the form-renderer.js expects data
-- (a single form_schema blob), and keeps reads to one query per form.

CREATE TABLE IF NOT EXISTS fill_forms (
  id VARCHAR(24) PRIMARY KEY,
  owner_token VARCHAR(64) NOT NULL,        -- ties a form to whoever created it (see auth note in README)
  title VARCHAR(255) NOT NULL DEFAULT 'Untitled form',
  display_mode ENUM('one_at_a_time','single_page','stack') NOT NULL DEFAULT 'one_at_a_time',
  thank_you_message VARCHAR(500) DEFAULT NULL,
  schema_json LONGTEXT NOT NULL,           -- the full { questions: [...] } structure form-renderer.js consumes
  is_published TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_owner (owner_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fill_responses (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  form_id VARCHAR(24) NOT NULL,
  answers_json LONGTEXT NOT NULL,          -- { "q1": "Jane", "q2": "No", ... }
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  respondent_ip VARCHAR(45) DEFAULT NULL,  -- stored only for spam/abuse triage, never shown in the dashboard by default
  FOREIGN KEY (form_id) REFERENCES fill_forms(id) ON DELETE CASCADE,
  INDEX idx_form (form_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
