-- V11 schema

CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','manager','agent') NOT NULL DEFAULT 'manager', is_active TINYINT(1) NOT NULL DEFAULT 1, two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0, two_factor_secret TEXT NULL, two_factor_confirmed_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS contacts (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NULL, phone VARCHAR(30) NULL, email VARCHAR(190) NULL, company VARCHAR(190) NULL, tags VARCHAR(255) NULL, timezone VARCHAR(80) NULL, lifecycle_stage VARCHAR(50) NOT NULL DEFAULT 'lead', score INT NOT NULL DEFAULT 0, owner_user_id INT NULL, unsubscribed_sms TINYINT(1) NOT NULL DEFAULT 0, unsubscribed_email TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_contacts_phone (phone), INDEX idx_contacts_email (email), INDEX idx_contacts_stage (lifecycle_stage), INDEX idx_contacts_score (score));

CREATE TABLE IF NOT EXISTS templates (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, type ENUM('sms','email') NOT NULL, subject VARCHAR(255) NULL, body MEDIUMTEXT NOT NULL, media_path VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS campaigns (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, channel ENUM('sms','email') NOT NULL, template_id INT NULL, created_by INT NULL, status ENUM('draft','queued','processing','sent','failed') NOT NULL DEFAULT 'draft', recipient_filter VARCHAR(190) NULL, scheduled_at DATETIME NULL, subject_override VARCHAR(255) NULL, body_override MEDIUMTEXT NULL, segment_name VARCHAR(190) NULL, approval_status VARCHAR(30) NOT NULL DEFAULT 'not_required', approved_by INT NULL, approved_at DATETIME NULL, ab_enabled TINYINT(1) NOT NULL DEFAULT 0, variant_a_subject VARCHAR(255) NULL, variant_a_body MEDIUMTEXT NULL, variant_b_subject VARCHAR(255) NULL, variant_b_body MEDIUMTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS messages (id INT AUTO_INCREMENT PRIMARY KEY, campaign_id INT NULL, contact_id INT NULL, channel ENUM('sms','email') NOT NULL, direction ENUM('inbound','outbound') NOT NULL, subject VARCHAR(255) NULL, body MEDIUMTEXT NULL, from_address VARCHAR(255) NULL, to_address VARCHAR(255) NULL, status VARCHAR(50) NOT NULL DEFAULT 'queued', external_id VARCHAR(255) NULL, media_url VARCHAR(500) NULL, attachment_path VARCHAR(255) NULL, metadata_json LONGTEXT NULL, thread_key VARCHAR(255) NULL, in_reply_to_external_id VARCHAR(255) NULL, references_header TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_messages_contact (contact_id), INDEX idx_messages_external (external_id), INDEX idx_messages_created (created_at), INDEX idx_messages_status (status), INDEX idx_messages_thread (thread_key));

CREATE TABLE IF NOT EXISTS campaign_recipients (id INT AUTO_INCREMENT PRIMARY KEY, campaign_id INT NOT NULL, contact_id INT NOT NULL, address VARCHAR(255) NOT NULL, status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued', message_id INT NULL, error_message TEXT NULL, processed_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS password_resets (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, email VARCHAR(190) NOT NULL, token_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_password_resets_hash (token_hash));

CREATE TABLE IF NOT EXISTS settings (id INT AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(190) NOT NULL UNIQUE, setting_value TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS message_events (id INT AUTO_INCREMENT PRIMARY KEY, message_id INT NOT NULL, event_type ENUM('open','click','delivery','bounce','other') NOT NULL, event_value VARCHAR(500) NULL, metadata_json LONGTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_message_events_message (message_id), INDEX idx_message_events_type (event_type));

CREATE TABLE IF NOT EXISTS audit_logs (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, action VARCHAR(190) NOT NULL, entity_type VARCHAR(100) NOT NULL, entity_id INT NULL, details_json LONGTEXT NULL, ip_address VARCHAR(64) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_audit_logs_action (action), INDEX idx_audit_logs_entity (entity_type, entity_id));

CREATE TABLE IF NOT EXISTS queue_jobs (id INT AUTO_INCREMENT PRIMARY KEY, job_type VARCHAR(100) NOT NULL, payload_json LONGTEXT NULL, status ENUM('queued','processing','done','failed') NOT NULL DEFAULT 'queued', attempts INT NOT NULL DEFAULT 0, last_error TEXT NULL, available_at DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_queue_jobs_lookup (status, job_type, available_at));

CREATE TABLE IF NOT EXISTS queue_dead_letters (id INT AUTO_INCREMENT PRIMARY KEY, queue_job_id INT NULL, job_type VARCHAR(100) NOT NULL, payload_json LONGTEXT NULL, last_error TEXT NULL, attempts INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS segments (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, channel ENUM('all','sms','email') NOT NULL DEFAULT 'all', tag_filter VARCHAR(190) NULL, company_filter VARCHAR(190) NULL, rule_json LONGTEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS user_permissions (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, permission_key VARCHAR(190) NOT NULL, is_allowed TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_user_permission (user_id, permission_key));

CREATE TABLE IF NOT EXISTS suppression_list (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NULL, message_id INT NULL, channel ENUM('sms','email') NOT NULL, address VARCHAR(255) NOT NULL, normalized_address VARCHAR(255) NOT NULL, reason VARCHAR(190) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_suppression (channel, normalized_address));

CREATE TABLE IF NOT EXISTS user_integration_profiles (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL UNIQUE, preferred_channel ENUM('sms','email') NOT NULL DEFAULT 'email', use_personal_twilio TINYINT(1) NOT NULL DEFAULT 0, twilio_sid VARCHAR(255) NULL, twilio_token TEXT NULL, twilio_from_number VARCHAR(50) NULL, use_personal_mail TINYINT(1) NOT NULL DEFAULT 0, mail_from_name VARCHAR(190) NULL, mail_from_email VARCHAR(190) NULL, smtp_enabled TINYINT(1) NOT NULL DEFAULT 0, smtp_host VARCHAR(190) NULL, smtp_port INT NOT NULL DEFAULT 587, smtp_username VARCHAR(190) NULL, smtp_password TEXT NULL, smtp_secure VARCHAR(20) NULL, reply_to_email VARCHAR(190) NULL, default_sms_footer TEXT NULL, default_email_footer TEXT NULL, signature_name VARCHAR(190) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS custom_fields (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, field_key VARCHAR(190) NOT NULL UNIQUE, field_type VARCHAR(50) NOT NULL DEFAULT 'text', sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS contact_custom_values (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, field_key VARCHAR(190) NOT NULL, field_value LONGTEXT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_contact_field (contact_id, field_key));

CREATE TABLE IF NOT EXISTS consent_logs (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, channel ENUM('sms','email') NOT NULL, status VARCHAR(50) NOT NULL, source VARCHAR(190) NULL, proof_note TEXT NULL, ip_address VARCHAR(64) NULL, user_agent VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS workflows (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, trigger_event VARCHAR(80) NOT NULL DEFAULT 'contact_created', channel ENUM('sms','email') NOT NULL DEFAULT 'email', template_id INT NULL, delay_minutes INT NOT NULL DEFAULT 0, target_segment_id INT NULL, filters_json LONGTEXT NULL, action_json LONGTEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS contact_tasks (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, title VARCHAR(255) NOT NULL, due_at DATETIME NULL, status ENUM('open','done') NOT NULL DEFAULT 'open', assigned_user_id INT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS contact_notes (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, note_body MEDIUMTEXT NOT NULL, visibility VARCHAR(30) NOT NULL DEFAULT 'internal', created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS canned_replies (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(190) NOT NULL, channel ENUM('sms','email') NOT NULL DEFAULT 'email', subject VARCHAR(255) NULL, body MEDIUMTEXT NOT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS conversation_states (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL UNIQUE, status ENUM('open','pending','closed') NOT NULL DEFAULT 'open', assigned_user_id INT NULL, internal_note TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);



CREATE TABLE IF NOT EXISTS accounts (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, industry VARCHAR(190) NULL, website VARCHAR(255) NULL, owner_user_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS pipelines (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, stage_order_json LONGTEXT NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS reminders (id INT AUTO_INCREMENT PRIMARY KEY, contact_id INT NOT NULL, title VARCHAR(255) NOT NULL, remind_at DATETIME NULL, status ENUM('open','done') NOT NULL DEFAULT 'open', assigned_user_id INT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE contacts ADD COLUMN IF NOT EXISTS account_id INT NULL AFTER owner_user_id;
ALTER TABLE contacts ADD COLUMN IF NOT EXISTS pipeline_id INT NULL AFTER account_id;
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS recurring_enabled TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS recurring_rule VARCHAR(80) NULL;
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS send_time_optimization TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS builder_json LONGTEXT NULL;

CREATE TABLE IF NOT EXISTS contact_lists (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, channel ENUM('both','sms','email') NOT NULL DEFAULT 'both', description VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS contact_list_members (id INT AUTO_INCREMENT PRIMARY KEY, list_id INT NOT NULL, contact_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_list_member (list_id, contact_id));
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS contact_list_id INT NULL AFTER segment_id;

CREATE TABLE IF NOT EXISTS mail_folders (id INT AUTO_INCREMENT PRIMARY KEY, parent_id INT NULL, folder_key VARCHAR(190) NOT NULL UNIQUE, name VARCHAR(190) NOT NULL, is_system TINYINT(1) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 100, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS mail_conversations (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL UNIQUE, folder_key VARCHAR(190) NULL, focus_bucket VARCHAR(20) NOT NULL DEFAULT 'focused', is_starred TINYINT(1) NOT NULL DEFAULT 0, is_archived TINYINT(1) NOT NULL DEFAULT 0, snoozed_until DATETIME NULL, assigned_user_id INT NULL, shared_mailbox_id INT NULL, sla_status VARCHAR(30) NOT NULL DEFAULT 'normal', approval_status VARCHAR(30) NOT NULL DEFAULT 'not_required', approval_requested_user_id INT NULL, approval_decided_user_id INT NULL, approval_decided_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS mail_labels (id INT AUTO_INCREMENT PRIMARY KEY, label_key VARCHAR(190) NOT NULL UNIQUE, name VARCHAR(190) NOT NULL, color_token VARCHAR(30) NOT NULL DEFAULT 'blue', created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS mail_conversation_labels (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL, label_id INT NOT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_mail_conversation_label (conversation_key, label_id));
CREATE TABLE IF NOT EXISTS mail_rules (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, match_field VARCHAR(50) NOT NULL DEFAULT 'from_address', match_operator VARCHAR(20) NOT NULL DEFAULT 'contains', match_value VARCHAR(255) NOT NULL, target_folder_key VARCHAR(190) NULL, target_bucket VARCHAR(20) NULL, target_label_key VARCHAR(190) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 100, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS mail_drafts (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, draft_type VARCHAR(30) NOT NULL DEFAULT 'compose', conversation_key VARCHAR(255) NULL, contact_id INT NULL, to_address VARCHAR(255) NULL, subject VARCHAR(255) NULL, body MEDIUMTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS mail_internal_notes (id INT AUTO_INCREMENT PRIMARY KEY, conversation_key VARCHAR(255) NOT NULL, note_body MEDIUMTEXT NOT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS shared_mailboxes (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, email_address VARCHAR(190) NOT NULL, description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
