CREATE TABLE IF NOT EXISTS `#__mokosuiteforms_forms` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `alias` VARCHAR(400) NOT NULL DEFAULT '',
    `description` TEXT,
    `config` TEXT COMMENT 'JSON form configuration',
    `email_admin` VARCHAR(255) DEFAULT NULL,
    `email_submitter_field` INT UNSIGNED DEFAULT NULL,
    `css_class` VARCHAR(255) DEFAULT '',
    `published` TINYINT(1) NOT NULL DEFAULT 1,
    `ordering` INT NOT NULL DEFAULT 0,
    `checked_out` INT UNSIGNED DEFAULT NULL,
    `checked_out_time` DATETIME DEFAULT NULL,
    `created_by` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuiteforms_forms_published` (`published`),
    KEY `idx_mokosuiteforms_forms_alias` (`alias`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteforms_fields` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `form_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `name` VARCHAR(255) NOT NULL DEFAULT '',
    `field_type` VARCHAR(50) NOT NULL DEFAULT 'text',
    `config` TEXT COMMENT 'JSON field config (placeholder, options, validation)',
    `required` TINYINT(1) NOT NULL DEFAULT 0,
    `css_class` VARCHAR(255) DEFAULT '',
    `conditional_field_id` INT UNSIGNED DEFAULT NULL,
    `conditional_value` VARCHAR(255) DEFAULT NULL,
    `page_number` INT UNSIGNED NOT NULL DEFAULT 1,
    `published` TINYINT(1) NOT NULL DEFAULT 1,
    `ordering` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuiteforms_fields_form` (`form_id`),
    KEY `idx_mokosuiteforms_fields_ordering` (`form_id`, `ordering`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteforms_submissions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `form_id` INT UNSIGNED NOT NULL,
    `submitted_by` INT NOT NULL DEFAULT 0,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(512) DEFAULT NULL,
    `data` MEDIUMTEXT COMMENT 'JSON field_id => value pairs',
    `status` VARCHAR(20) NOT NULL DEFAULT 'new',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuiteforms_submissions_form` (`form_id`),
    KEY `idx_mokosuiteforms_submissions_status` (`status`),
    KEY `idx_mokosuiteforms_submissions_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteforms_notifications` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `form_id` INT UNSIGNED NOT NULL,
    `event` VARCHAR(50) NOT NULL DEFAULT 'on_submit',
    `recipient_type` VARCHAR(20) NOT NULL DEFAULT 'admin',
    `recipient_email` VARCHAR(255) DEFAULT NULL,
    `subject_template` VARCHAR(500) DEFAULT NULL,
    `body_template` TEXT,
    `published` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuiteforms_notifications_form` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;