-- SmartForm Funnel Builder - Database Schema
-- Version: 1.0.0

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─── FORMS ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `forms` (
  `id`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`              VARCHAR(255) NOT NULL,
  `description`       TEXT,
  `slug`              VARCHAR(255) NOT NULL UNIQUE,
  `whatsapp_number`   VARCHAR(30) DEFAULT '',
  `thank_you_message` TEXT,
  `redirect_url`      VARCHAR(500) DEFAULT '',
  `settings`          JSON,
  `created_at`        DATETIME NOT NULL,
  `updated_at`        DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── FIELDS ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `fields` (
  `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `form_id`     INT(11) UNSIGNED NOT NULL,
  `label`       VARCHAR(255) NOT NULL,
  `field_type`  ENUM('text','email','phone','number','textarea','select','radio','checkbox','date','file','image') NOT NULL DEFAULT 'text',
  `placeholder` VARCHAR(255) DEFAULT '',
  `required`    TINYINT(1) NOT NULL DEFAULT 0,
  `options`     TEXT COMMENT 'Comma separated options for select/radio/checkbox',
  `settings`    JSON,
  `sort_order`  INT(11) NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_form_id` (`form_id`),
  CONSTRAINT `fk_fields_form` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── RESPONSES ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `responses` (
  `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `form_id`     INT(11) UNSIGNED NOT NULL,
  `ip_address`  VARCHAR(50) DEFAULT '',
  `user_agent`  TEXT,
  `created_at`  DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_form_id` (`form_id`),
  CONSTRAINT `fk_responses_form` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── RESPONSE VALUES ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `response_values` (
  `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `response_id` INT(11) UNSIGNED NOT NULL,
  `field_id`    INT(11) UNSIGNED NOT NULL,
  `value`       TEXT,
  PRIMARY KEY (`id`),
  KEY `idx_response_id` (`response_id`),
  KEY `idx_field_id` (`field_id`),
  CONSTRAINT `fk_rv_response` FOREIGN KEY (`response_id`) REFERENCES `responses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rv_field`    FOREIGN KEY (`field_id`)    REFERENCES `fields` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── FUNNELS ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `funnels` (
  `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(255) NOT NULL,
  `description` TEXT,
  `slug`        VARCHAR(255) NOT NULL UNIQUE,
  `created_at`  DATETIME NOT NULL,
  `updated_at`  DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── FUNNEL PAGES ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `funnel_pages` (
  `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `funnel_id`   INT(11) UNSIGNED NOT NULL,
  `title`       VARCHAR(255) NOT NULL,
  `content`     LONGTEXT COMMENT 'JSON sections for landing builder',
  `form_id`     INT(11) UNSIGNED DEFAULT NULL,
  `step_order`  INT(11) NOT NULL DEFAULT 0,
  `page_type`   ENUM('landing','form','thankyou') NOT NULL DEFAULT 'landing',
  `settings`    JSON,
  `created_at`  DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_funnel_id` (`funnel_id`),
  CONSTRAINT `fk_pages_funnel` FOREIGN KEY (`funnel_id`) REFERENCES `funnels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pages_form`   FOREIGN KEY (`form_id`)   REFERENCES `forms` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
