-- MySQL Schema for Wamessager

CREATE TABLE clients (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  subscription_status ENUM('active', 'expired', 'grace_period', 'admin_granted') NOT NULL DEFAULT 'expired',
  current_plan ENUM('monthly', 'annual', 'none') NOT NULL DEFAULT 'none',
  access_granted_by_admin TINYINT(1) NOT NULL DEFAULT 0,
  admin_grant_expiry DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_roles (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  role ENUM('admin', 'client') NOT NULL DEFAULT 'client',
  UNIQUE KEY unique_user_role (user_id, role)
) ENGINE=InnoDB;

CREATE TABLE admin_settings (
  id CHAR(36) PRIMARY KEY,
  admin_password TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE customers (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL DEFAULT '',
  phone_number VARCHAR(255) NOT NULL,
  tags JSON NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  last_message_at DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_client_phone (client_id, phone_number),
  INDEX idx_client_last_message (client_id, last_message_at),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE messages (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  phone_number VARCHAR(255) NOT NULL,
  message_content TEXT NOT NULL,
  direction VARCHAR(50) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_client_phone_created (client_id, phone_number, created_at),
  FULLTEXT idx_message_content (message_content),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE conversation_settings (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  phone_number VARCHAR(255) NOT NULL,
  automation_paused TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_conv_setting (client_id, phone_number),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE global_buttons (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  label VARCHAR(255) NOT NULL,
  source_type VARCHAR(100) NOT NULL DEFAULT 'automation',
  source_id CHAR(36),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE activity_logs (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  category VARCHAR(100) NOT NULL DEFAULT 'general',
  event_type VARCHAR(100) NOT NULL DEFAULT 'info',
  message TEXT NOT NULL,
  phone_number VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_client_created (client_id, created_at),
  INDEX idx_client_category (client_id, category),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE whatsapp_config (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL UNIQUE,
  meta_access_token TEXT NOT NULL,
  phone_number_id VARCHAR(255) NOT NULL DEFAULT '',
  webhook_verify_token VARCHAR(255) NOT NULL DEFAULT '',
  waba_id VARCHAR(255) NOT NULL DEFAULT '',
  default_reply TEXT NOT NULL,
  payment_mode VARCHAR(100) NOT NULL DEFAULT 'online',
  delivery_confirmation_phrase TEXT NOT NULL,
  partial_payment_phrase TEXT NOT NULL,
  paid_trigger_phrase TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE whatsapp_templates (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  language VARCHAR(50) NOT NULL DEFAULT 'en_US',
  category VARCHAR(100) NOT NULL DEFAULT 'MARKETING',
  header_type VARCHAR(50) NOT NULL DEFAULT 'NONE',
  header_text TEXT,
  body_text TEXT,
  footer_text TEXT,
  buttons JSON NOT NULL,
  meta_template_id VARCHAR(255),
  status ENUM('LOCAL','PENDING','APPROVED','REJECTED','PAUSED','DISABLED') NOT NULL DEFAULT 'LOCAL',
  rejection_reason TEXT,
  submitted_at DATETIME,
  approved_at DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_template_name (client_id, name),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE automation_rules (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  keyword TEXT NOT NULL,
  match_type ENUM('exact', 'contains', 'starts_with') NOT NULL DEFAULT 'contains',
  response_type ENUM('text', 'image', 'video', 'audio', 'document', 'template', 'buttons') NOT NULL DEFAULT 'text',
  response_content JSON NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  trigger_type ENUM('keyword', 'button', 'fallback') NOT NULL DEFAULT 'keyword',
  keywords JSON NOT NULL,
  trigger_button_id VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_client_active (client_id, is_active),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE ai_settings (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL UNIQUE,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  openai_api_key TEXT NOT NULL,
  model VARCHAR(100) NOT NULL DEFAULT 'gpt-4o-mini',
  system_instructions TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE ai_global_settings (
  id CHAR(36) PRIMARY KEY,
  openai_api_key TEXT NOT NULL,
  model VARCHAR(100) NOT NULL DEFAULT 'gpt-4o-mini',
  system_instructions TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE ai_knowledge_sources (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  source_type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT '',
  content TEXT NOT NULL,
  source_url TEXT,
  file_path TEXT,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  purpose VARCHAR(50) NOT NULL DEFAULT 'reply',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_client_active (client_id, is_active),
  FULLTEXT idx_content (content),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE ai_product_media (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  media_type VARCHAR(50) NOT NULL DEFAULT 'image',
  media_url TEXT NOT NULL,
  file_path TEXT,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE broadcasts (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  message TEXT NOT NULL,
  audience JSON NOT NULL,
  status ENUM('draft', 'scheduled', 'sending', 'paused', 'completed', 'failed') NOT NULL DEFAULT 'draft',
  total_recipients INT NOT NULL DEFAULT 0,
  sent_count INT NOT NULL DEFAULT 0,
  failed_count INT NOT NULL DEFAULT 0,
  type VARCHAR(50) NOT NULL DEFAULT 'text',
  scheduled_dates JSON NOT NULL,
  scheduled_status VARCHAR(50) NOT NULL DEFAULT 'immediate',
  recipient_phones JSON NOT NULL,
  last_sent_index INT NOT NULL DEFAULT -1,
  last_progress_at DATETIME,
  send_mode VARCHAR(50) NOT NULL DEFAULT 'text',
  template_id CHAR(36),
  template_variables JSON NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status_progress (status, last_progress_at),
  INDEX idx_client_status (client_id, status),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE broadcast_buttons (
  id CHAR(36) PRIMARY KEY,
  broadcast_id CHAR(36) NOT NULL,
  label VARCHAR(255) NOT NULL,
  action_type VARCHAR(100) NOT NULL DEFAULT 'trigger_automation',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE customer_followups (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL DEFAULT 'Sequence',
  customer_selection VARCHAR(100) NOT NULL DEFAULT 'all',
  selected_tags JSON NOT NULL,
  start_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  interval_days INT NOT NULL DEFAULT 1,
  message TEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  use_ai TINYINT(1) NOT NULL DEFAULT 0,
  trigger_mode VARCHAR(50) NOT NULL DEFAULT 'schedule',
  inactivity_days INT NOT NULL DEFAULT 3,
  ai_goal TEXT NOT NULL,
  last_run_at DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE customer_followup_log (
  id CHAR(36) PRIMARY KEY,
  followup_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  phone_number VARCHAR(255) NOT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  message TEXT NOT NULL,
  INDEX idx_followup_phone (followup_id, phone_number, sent_at),
  FOREIGN KEY (followup_id) REFERENCES customer_followups(id) ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE administrators (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL DEFAULT '',
  phone_number VARCHAR(255) NOT NULL,
  send_time VARCHAR(50) NOT NULL DEFAULT '20:00',
  timezone VARCHAR(100) NOT NULL DEFAULT 'UTC',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_sent_date DATE,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  plan_type ENUM('none', 'monthly', 'annual') NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  payment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expiry_date DATETIME NOT NULL,
  status ENUM('active', 'expired', 'trial') NOT NULL DEFAULT 'active',
  payment_method VARCHAR(100) NOT NULL DEFAULT 'pesapal',
  transaction_id VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_client_expiry (client_id, expiry_date),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payment_logs (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'success',
  transaction_reference VARCHAR(255),
  merchant_reference VARCHAR(255),
  pesapal_tracking_id VARCHAR(255),
  payment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  plan_type ENUM('none', 'monthly', 'annual') NOT NULL DEFAULT 'monthly',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE client_billing (
  id CHAR(36) PRIMARY KEY,
  client_id CHAR(36) NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  payment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expiration_date DATETIME,
  status VARCHAR(50) NOT NULL DEFAULT 'paid',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE pesapal_config (
  id CHAR(36) PRIMARY KEY,
  consumer_key TEXT NOT NULL,
  consumer_secret TEXT NOT NULL,
  monthly_price DECIMAL(10,2) NOT NULL DEFAULT 10,
  environment VARCHAR(50) NOT NULL DEFAULT 'live',
  ipn_id VARCHAR(255) DEFAULT '',
  pesapal_enabled TINYINT(1) NOT NULL DEFAULT 0,
  manual_whatsapp_number VARCHAR(255) NOT NULL DEFAULT '+256745078436',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;