CREATE TABLE IF NOT EXISTS businesses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  sector VARCHAR(120) NOT NULL,
  city VARCHAR(120) NOT NULL,
  region VARCHAR(120) NOT NULL,
  website VARCHAR(255) DEFAULT NULL,
  instagram VARCHAR(255) DEFAULT NULL,
  facebook VARCHAR(255) DEFAULT NULL,
  linkedin VARCHAR(255) DEFAULT NULL,
  google_url TEXT DEFAULT NULL,
  phone VARCHAR(80) DEFAULT NULL,
  email VARCHAR(160) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  logo_url VARCHAR(255) DEFAULT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Yeni aday',
  priority VARCHAR(80) NOT NULL DEFAULT 'İncele',
  opportunity_score TINYINT UNSIGNED NOT NULL DEFAULT 50,
  estimated_value DECIMAL(10,2) NOT NULL DEFAULT 0,
  summary TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS business_scores (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id INT UNSIGNED NOT NULL,
  channel VARCHAR(80) NOT NULL,
  label VARCHAR(120) NOT NULL,
  score DECIMAL(4,1) NOT NULL DEFAULT 0,
  max_score DECIMAL(4,1) NOT NULL DEFAULT 10,
  reason TEXT DEFAULT NULL,
  recommendation TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX business_scores_business_id_idx (business_id),
  CONSTRAINT business_scores_business_fk FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS business_findings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id INT UNSIGNED NOT NULL,
  type ENUM('weakness','strength','service','note') NOT NULL DEFAULT 'weakness',
  title VARCHAR(180) NOT NULL,
  body TEXT NOT NULL,
  impact VARCHAR(80) NOT NULL DEFAULT 'Orta',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX business_findings_business_id_idx (business_id),
  CONSTRAINT business_findings_business_fk FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunities (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id INT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Sıcak fırsat',
  score TINYINT UNSIGNED NOT NULL DEFAULT 50,
  value DECIMAL(10,2) NOT NULL DEFAULT 0,
  next_step VARCHAR(180) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX opportunities_business_id_idx (business_id),
  CONSTRAINT opportunities_business_fk FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS offers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id INT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Taslak',
  channel VARCHAR(80) NOT NULL DEFAULT 'E-posta',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  body MEDIUMTEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX offers_business_id_idx (business_id),
  CONSTRAINT offers_business_fk FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_drafts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id INT UNSIGNED NOT NULL,
  channel VARCHAR(80) NOT NULL,
  subject VARCHAR(180) DEFAULT NULL,
  body MEDIUMTEXT NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Taslak',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX message_drafts_business_id_idx (business_id),
  CONSTRAINT message_drafts_business_fk FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tasks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id INT UNSIGNED DEFAULT NULL,
  title VARCHAR(180) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Açık',
  priority VARCHAR(80) NOT NULL DEFAULT 'Orta',
  due_date DATE DEFAULT NULL,
  note TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX tasks_business_id_idx (business_id),
  CONSTRAINT tasks_business_fk FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_connections (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(80) NOT NULL UNIQUE,
  label VARCHAR(120) NOT NULL,
  status ENUM('missing','ready','active','error') NOT NULL DEFAULT 'missing',
  api_key TEXT DEFAULT NULL,
  extra_json JSON DEFAULT NULL,
  last_checked_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scan_schedules (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  weekday TINYINT UNSIGNED NOT NULL,
  day_label VARCHAR(40) NOT NULL,
  region VARCHAR(120) NOT NULL,
  query VARCHAR(180) NOT NULL,
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  last_run_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scan_runs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  schedule_id INT UNSIGNED DEFAULT NULL,
  region VARCHAR(120) NOT NULL,
  query VARCHAR(180) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Hazır',
  result_count INT UNSIGNED NOT NULL DEFAULT 0,
  summary TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX scan_runs_schedule_id_idx (schedule_id),
  CONSTRAINT scan_runs_schedule_fk FOREIGN KEY (schedule_id) REFERENCES scan_schedules(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL UNIQUE,
  trigger_type VARCHAR(100) NOT NULL,
  action_type VARCHAR(100) NOT NULL,
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  description TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(80) NOT NULL DEFAULT 'info',
  title VARCHAR(180) NOT NULL,
  body TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;