-- CRM Contacts module

CREATE TABLE IF NOT EXISTS contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(180) NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  primary_phone VARCHAR(64) NOT NULL,
  secondary_phone VARCHAR(64) NULL,
  fax VARCHAR(64) NULL,
  date_of_birth DATE NULL,
  source VARCHAR(120) NULL,
  industry VARCHAR(120) NULL,
  language VARCHAR(64) NULL,
  description TEXT NULL,
  street_address VARCHAR(255) NULL,
  city VARCHAR(120) NULL,
  state VARCHAR(120) NULL,
  country VARCHAR(120) NULL,
  zip_code VARCHAR(32) NULL,
  facebook VARCHAR(255) NULL,
  microsoft_teams VARCHAR(255) NULL,
  linkedin VARCHAR(255) NULL,
  twitter VARCHAR(255) NULL,
  whatsapp VARCHAR(64) NULL,
  instagram VARCHAR(255) NULL,
  shipping_street_address VARCHAR(255) NULL,
  shipping_city VARCHAR(120) NULL,
  shipping_state VARCHAR(120) NULL,
  shipping_country VARCHAR(120) NULL,
  shipping_zip_code VARCHAR(32) NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  owner_id INT NOT NULL,
  created_by INT NOT NULL,
  updated_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_contacts_slug_active (slug, deleted_at),
  UNIQUE KEY uq_contacts_email_active (email, deleted_at),
  INDEX idx_contacts_owner (owner_id),
  INDEX idx_contacts_name (name),
  CONSTRAINT fk_contacts_owner FOREIGN KEY (owner_id) REFERENCES users(id),
  CONSTRAINT fk_contacts_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_contacts_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS contact_activities (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  activity_type VARCHAR(64) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  metadata JSON NULL,
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ca_contact (contact_id, created_at DESC),
  CONSTRAINT fk_ca_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  CONSTRAINT fk_ca_user FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS contact_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  body_text TEXT NOT NULL,
  body_html TEXT NULL,
  created_by INT NOT NULL,
  updated_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_cn_contact (contact_id, created_at DESC),
  CONSTRAINT fk_cn_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  CONSTRAINT fk_cn_created FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_cn_updated FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS contact_meetings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  scheduled_at DATETIME NOT NULL,
  reminder_at DATETIME NULL,
  notes TEXT NULL,
  status ENUM('scheduled', 'completed', 'cancelled') NOT NULL DEFAULT 'scheduled',
  created_by INT NOT NULL,
  updated_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_cm_contact (contact_id, scheduled_at DESC),
  CONSTRAINT fk_cm_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  CONSTRAINT fk_cm_created FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_cm_updated FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS contact_import_jobs (
  id CHAR(36) PRIMARY KEY,
  owner_id INT NOT NULL,
  status ENUM('pending', 'running', 'completed', 'failed') NOT NULL DEFAULT 'pending',
  total_rows INT NOT NULL DEFAULT 0,
  processed_rows INT NOT NULL DEFAULT 0,
  imported_count INT NOT NULL DEFAULT 0,
  skipped_count INT NOT NULL DEFAULT 0,
  duplicate_count INT NOT NULL DEFAULT 0,
  invalid_email_count INT NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  INDEX idx_cij_owner (owner_id, created_at DESC),
  CONSTRAINT fk_cij_owner FOREIGN KEY (owner_id) REFERENCES users(id)
);