-- Database tables for Ecogram Lifestyle contact form

-- Table for storing contact form submissions
CREATE TABLE IF NOT EXISTS `contact_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `message` text,
  `submission_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `status` enum('new','read','replied','spam') NOT NULL DEFAULT 'new',
  `notes` text,
  PRIMARY KEY (`id`),
  KEY `email` (`email`),
  KEY `status` (`status`),
  KEY `submission_date` (`submission_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for tracking email communications
CREATE TABLE IF NOT EXISTS `contact_communications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submission_id` int(11) NOT NULL,
  `type` enum('incoming','outgoing') NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int(11) DEFAULT NULL COMMENT 'Admin user ID if sent by admin',
  PRIMARY KEY (`id`),
  KEY `submission_id` (`submission_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `fk_communication_submission` FOREIGN KEY (`submission_id`) REFERENCES `contact_submissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for contact form settings
CREATE TABLE IF NOT EXISTS `contact_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text,
  `description` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default settings
INSERT INTO `contact_settings` (`setting_key`, `setting_value`, `description`) VALUES
('notification_email', 'info@ecogramlifestyle.com', 'Email address to receive contact form notifications'),
('auto_reply', '1', 'Whether to send auto-reply emails (1 = Yes, 0 = No)'),
('auto_reply_subject', 'Thank you for contacting Ecogram Lifestyle', 'Subject for auto-reply emails'),
('auto_reply_message', 'Thank you for contacting Ecogram Lifestyle. We have received your message and our team will get back to you shortly.', 'Message for auto-reply emails'),
('form_title', 'Get In Touch', 'Contact form title'),
('form_description', 'Fill the form below and our team will assist you with plot availability, site visits and investment details.', 'Contact form description'),
('recaptcha_site_key', '6LeH5kcsAAAAAGFkQp_Z_iyeUxcuTRAdX2v7jJeo', 'reCAPTCHA site key'),
('recaptcha_secret_key', 'YOUR_RECAPTCHA_SECRET_KEY', 'reCAPTCHA secret key');

-- Table for tracking form submissions (for analytics)
CREATE TABLE IF NOT EXISTS `form_submission_analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_type` varchar(50) NOT NULL COMMENT 'e.g., contact, newsletter',
  `submission_date` date NOT NULL,
  `hour` tinyint(2) NOT NULL,
  `count` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `form_date_hour` (`form_type`,`submission_date`,`hour`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
