-- QuizFunnel - Single Tenant Edition
-- One installation per client on their own hosting
-- No multi-tenant complexity - client owns everything

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------
-- Site Settings
-- --------------------------------------------------------

CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key_name` varchar(100) NOT NULL,
  `value` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_name` (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`key_name`, `value`) VALUES
-- Branding
('site_name', 'Quiz Funnel'),
('site_logo', NULL),
('primary_color', '#6366f1'),
('secondary_color', '#8b5cf6'),
('custom_css', NULL),

-- Analytics & Tracking Pixels
('ga4_measurement_id', NULL),
('meta_pixel_id', NULL),
('tiktok_pixel_id', NULL),
('linkedin_partner_id', NULL),
('linkedin_conversion_id', NULL),
('pinterest_tag_id', NULL),
('google_ads_conversion_id', NULL),
('google_ads_conversion_label', NULL),
('hotjar_site_id', NULL),
('clarity_project_id', NULL),

-- Storage
('bunny_storage_zone', NULL),
('bunny_api_key', NULL),
('bunny_cdn_url', NULL),

-- Email Services
('sendgrid_api_key', NULL),
('sendgrid_from_email', NULL),

-- Mailchimp
('mailchimp_api_key', NULL),
('mailchimp_list_id', NULL),
('mailchimp_tag_on_signup', NULL),

-- Kit (ConvertKit)
('kit_api_key', NULL),
('kit_form_id', NULL),
('kit_tag_on_signup', NULL),

-- Webhooks
('zapier_webhook_url', NULL),
('zapier_webhook_new_lead', NULL),
('zapier_webhook_quiz_complete', NULL),
('pabbly_webhook_url', NULL),
('pabbly_webhook_new_lead', NULL),
('pabbly_webhook_quiz_complete', NULL);

-- --------------------------------------------------------
-- Users (Admin + Team)
-- --------------------------------------------------------

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `role` enum('admin','editor','viewer') DEFAULT 'admin',
  `last_login` datetime DEFAULT NULL,
  `reset_token` varchar(100) DEFAULT NULL,
  `reset_token_expires` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Quizzes
-- --------------------------------------------------------

CREATE TABLE `quizzes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `design` json DEFAULT NULL,
  `seo` json DEFAULT NULL,
  `scoring_type` enum('none','sum','weighted','category') DEFAULT 'sum',
  `lead_capture_position` enum('on_landing','before_quiz','after_question','before_results','none') DEFAULT 'on_landing',
  `lead_capture_after_question` int(11) DEFAULT 2,
  `published` tinyint(1) DEFAULT 0,
  `views` int(11) DEFAULT 0,
  `starts` int(11) DEFAULT 0,
  `completions` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Pages (Landing, Lead Capture, Results)
-- --------------------------------------------------------

CREATE TABLE `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `type` enum('landing','quiz_wrapper','lead_capture','result') NOT NULL,
  `slug` varchar(100) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `blocks` json NOT NULL DEFAULT '[]',
  `settings` json DEFAULT NULL,
  `is_default` tinyint(1) DEFAULT 0,
  `mailchimp_tag` varchar(255) DEFAULT NULL,
  `kit_tag` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `pages_quiz_fk` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Questions
-- --------------------------------------------------------

CREATE TABLE `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `uuid` varchar(36) NOT NULL,
  `order_index` int(11) NOT NULL DEFAULT 0,
  `question_text` text NOT NULL,
  `question_type` enum('single','multiple','text','email','scale','image_choice') NOT NULL DEFAULT 'single',
  `description` text DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `required` tinyint(1) DEFAULT 1,
  `settings` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `questions_quiz_fk` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Question Options
-- --------------------------------------------------------

CREATE TABLE `question_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) NOT NULL,
  `uuid` varchar(36) NOT NULL,
  `order_index` int(11) NOT NULL DEFAULT 0,
  `option_text` varchar(500) NOT NULL,
  `option_image` varchar(500) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `points` int(11) DEFAULT 0,
  `score` int(11) DEFAULT 0,
  `tags` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `options_question_fk` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Logic Rules
-- --------------------------------------------------------

CREATE TABLE `logic_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `uuid` varchar(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `conditions` json NOT NULL,
  `condition_match` enum('all','any') DEFAULT 'all',
  `action_type` enum('show_result','skip_to_question','add_tags','add_score') NOT NULL,
  `action_value` json NOT NULL,
  `priority` int(11) DEFAULT 0,
  `enabled` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `logic_quiz_fk` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Lead Fields (Custom per quiz)
-- --------------------------------------------------------

CREATE TABLE `lead_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_label` varchar(255) NOT NULL,
  `field_type` enum('text','email','phone','select','checkbox','hidden') NOT NULL DEFAULT 'text',
  `placeholder` varchar(255) DEFAULT NULL,
  `options` json DEFAULT NULL,
  `required` tinyint(1) DEFAULT 0,
  `order_index` int(11) DEFAULT 0,
  `enabled` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `fields_quiz_fk` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Leads
-- --------------------------------------------------------

CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `result_page_id` int(11) DEFAULT NULL,
  `uuid` varchar(36) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `custom_fields` json DEFAULT NULL,
  `answers` json NOT NULL DEFAULT '{}',
  `tags` json DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `referrer` varchar(500) DEFAULT NULL,
  `utm_source` varchar(255) DEFAULT NULL,
  `utm_medium` varchar(255) DEFAULT NULL,
  `utm_campaign` varchar(255) DEFAULT NULL,
  `completed_quiz` tinyint(1) DEFAULT 0,
  `synced_mailchimp` tinyint(1) DEFAULT 0,
  `synced_kit` tinyint(1) DEFAULT 0,
  `synced_sheets` tinyint(1) DEFAULT 0,
  `synced_webhook` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `quiz_id` (`quiz_id`),
  KEY `email` (`email`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `leads_quiz_fk` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Integrations
-- --------------------------------------------------------

CREATE TABLE `integrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `platform` enum('mailchimp','kit','sheets','webhook') NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `credentials` text DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT 0,
  `last_sync` datetime DEFAULT NULL,
  `last_error` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform` (`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Quiz-Integration Mapping
-- --------------------------------------------------------

CREATE TABLE `quiz_integrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `integration_id` int(11) NOT NULL,
  `list_id` varchar(100) DEFAULT NULL,
  `form_id` varchar(100) DEFAULT NULL,
  `sheet_name` varchar(100) DEFAULT NULL,
  `tag_mapping` json DEFAULT NULL,
  `field_mapping` json DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quiz_integration` (`quiz_id`, `integration_id`),
  CONSTRAINT `qi_quiz_fk` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `qi_integration_fk` FOREIGN KEY (`integration_id`) REFERENCES `integrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Quiz Sessions
-- --------------------------------------------------------

CREATE TABLE `quiz_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `session_token` varchar(64) NOT NULL,
  `lead_id` int(11) DEFAULT NULL,
  `current_question` int(11) DEFAULT 0,
  `answers` json DEFAULT NULL,
  `started_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_activity` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completed` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_token` (`session_token`),
  KEY `quiz_id` (`quiz_id`),
  CONSTRAINT `sessions_quiz_fk` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
