-- =====================================================
-- 2026企业CMS - 内置表单功能数据库结构
-- =====================================================

USE `cms2026`;

-- =====================================================
-- 1. 表单表 (forms)
-- =====================================================
CREATE TABLE IF NOT EXISTS `forms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '表单名称',
  `slug` varchar(100) NOT NULL COMMENT '表单标识',
  `description` text DEFAULT NULL COMMENT '表单描述',
  `type` enum('contact','registration','appointment','survey','job_application','custom') DEFAULT 'contact' COMMENT '表单类型',
  `fields` text NOT NULL COMMENT '表单字段（JSON格式）',
  `settings` text DEFAULT NULL COMMENT '表单设置（JSON格式）',
  `success_message` text DEFAULT NULL COMMENT '提交成功提示',
  `notification_email` varchar(255) DEFAULT NULL COMMENT '通知邮箱',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1启用,0禁用',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表单表';

-- 插入默认表单
INSERT INTO `forms` (`name`, `slug`, `description`, `type`, `fields`, `settings`, `success_message`) VALUES
('联系我们', 'contact', '网站联系表单', 'contact', 
'[{"name":"name","label":"姓名","type":"text","required":true,"placeholder":"请输入您的姓名"},{"name":"phone","label":"电话","type":"tel","required":true,"placeholder":"请输入联系电话"},{"name":"email","label":"邮箱","type":"email","required":false,"placeholder":"请输入邮箱"},{"name":"message","label":"留言内容","type":"textarea","required":true,"placeholder":"请输入您的留言"}]',
'{"show_title":true,"show_description":true,"submit_text":"提交留言"}',
'感谢您的留言，我们会尽快回复！'),

('课程报名表', 'course_registration', '课程报名表单', 'registration',
'[{"name":"student_name","label":"学员姓名","type":"text","required":true,"placeholder":"请输入学员姓名"},{"name":"parent_name","label":"家长姓名","type":"text","required":true,"placeholder":"请输入家长姓名"},{"name":"phone","label":"联系电话","type":"tel","required":true,"placeholder":"请输入联系电话"},{"name":"age","label":"学员年龄","type":"number","required":true,"placeholder":"请输入年龄"},{"name":"course","label":"报名课程","type":"select","required":true,"options":["钢琴课","舞蹈课","美术课","书法课"]},{"name":"schedule","label":"意向时间","type":"select","required":true,"options":["周末上午","周末下午","工作日晚上"]},{"name":"remark","label":"备注","type":"textarea","required":false,"placeholder":"其他需要说明的信息"}]',
'{"show_title":true,"show_description":true,"submit_text":"提交报名"}',
'报名成功！我们会尽快与您联系确认课程安排。'),

('在线预约表', 'appointment', '在线预约表单', 'appointment',
'[{"name":"name","label":"姓名","type":"text","required":true,"placeholder":"请输入您的姓名"},{"name":"phone","label":"联系电话","type":"tel","required":true,"placeholder":"请输入联系电话"},{"name":"service","label":"预约服务","type":"select","required":true,"options":["咨询服务","技术支持","上门维修","其他"]},{"name":"appointment_date","label":"预约日期","type":"date","required":true},{"name":"appointment_time","label":"预约时间","type":"time","required":true},{"name":"remark","label":"备注","type":"textarea","required":false,"placeholder":"请描述您的需求"}]',
'{"show_title":true,"show_description":true,"submit_text":"提交预约"}',
'预约成功！我们会尽快确认您的预约时间。'),

('问卷调查表', 'survey', '客户满意度调查', 'survey',
'[{"name":"name","label":"姓名","type":"text","required":false,"placeholder":"可选填"},{"name":"satisfaction","label":"整体满意度","type":"radio","required":true,"options":["非常满意","满意","一般","不满意"]},{"name":"service_quality","label":"服务质量","type":"radio","required":true,"options":["非常好","好","一般","差"]},{"name":"improvement","label":"改进建议","type":"checkbox","required":false,"options":["响应速度","服务态度","专业能力","价格优惠"]},{"name":"suggestion","label":"其他建议","type":"textarea","required":false,"placeholder":"请提出您的宝贵建议"}]',
'{"show_title":true,"show_description":true,"submit_text":"提交问卷"}',
'感谢您参与调查！您的反馈对我们非常重要。'),

('招聘申请表', 'job_application', '职位申请表', 'job_application',
'[{"name":"name","label":"姓名","type":"text","required":true,"placeholder":"请输入姓名"},{"name":"phone","label":"联系电话","type":"tel","required":true,"placeholder":"请输入联系电话"},{"name":"email","label":"邮箱","type":"email","required":true,"placeholder":"请输入邮箱"},{"name":"position","label":"应聘职位","type":"select","required":true,"options":["PHP开发工程师","前端开发工程师","UI设计师","销售经理","客服专员"]},{"name":"education","label":"最高学历","type":"select","required":true,"options":["高中","大专","本科","硕士","博士"]},{"name":"experience","label":"工作经验","type":"textarea","required":true,"placeholder":"请描述您的工作经验"},{"name":"resume","label":"简历上传","type":"file","required":true,"accept":".pdf,.doc,.docx"}]',
'{"show_title":true,"show_description":true,"submit_text":"提交申请"}',
'申请已收到！我们会尽快审核您的简历，合则约见。');

-- =====================================================
-- 2. 表单提交表 (form_submissions)
-- =====================================================
CREATE TABLE IF NOT EXISTS `form_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL COMMENT '表单ID',
  `data` text NOT NULL COMMENT '提交数据（JSON格式）',
  `ip` varchar(45) DEFAULT NULL COMMENT '提交者IP',
  `user_agent` text DEFAULT NULL COMMENT '用户代理',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `form_id` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表单提交表';

-- =====================================================
-- 3. 自定义表单字段类型说明
-- =====================================================

-- 字段类型支持：
-- text: 单行文本
-- textarea: 多行文本
-- email: 邮箱
-- tel: 电话
-- number: 数字
-- date: 日期
-- time: 时间
-- datetime: 日期时间
-- select: 下拉选择
-- multiselect: 多选
-- radio: 单选
-- checkbox: 多选框
-- file: 文件上传

-- =====================================================
-- 4. 示例：查看表单提交
-- =====================================================

-- 查看所有表单提交
-- SELECT fs.*, f.name as form_name 
-- FROM form_submissions fs 
-- LEFT JOIN forms f ON fs.form_id = f.id 
-- ORDER BY fs.created_at DESC;

-- 查看某个表单的提交数据（解析JSON）
-- SELECT id, form_id, data, created_at 
-- FROM form_submissions 
-- WHERE form_id = 1 
-- ORDER BY created_at DESC;
