CREATE TABLE IF NOT EXISTS gi_sources (
  id VARCHAR(80) PRIMARY KEY,
  type VARCHAR(40) NOT NULL,
  name VARCHAR(190) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  config_json JSON NOT NULL,
  last_sync_at DATETIME NULL,
  last_success_at DATETIME NULL,
  imported_count INT NOT NULL DEFAULT 0,
  error_count INT NOT NULL DEFAULT 0,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gi_activities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id VARCHAR(80) NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  fingerprint CHAR(64) NOT NULL,
  title VARCHAR(255) NOT NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NULL,
  venue_name VARCHAR(255) NULL,
  address VARCHAR(255) NULL,
  postal_code VARCHAR(20) NULL,
  city VARCHAR(120) NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'autre',
  price_type ENUM('free','paid','unknown') NOT NULL DEFAULT 'unknown',
  price_label VARCHAR(120) NULL,
  source_name VARCHAR(190) NOT NULL,
  official_url TEXT NOT NULL,
  image_url TEXT NULL,
  summary TEXT NULL,
  last_verified_at DATETIME NOT NULL,
  raw_json JSON NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uniq_source_external (source_id, external_id),
  KEY idx_fingerprint (fingerprint),
  KEY idx_starts_at (starts_at),
  KEY idx_geo (latitude, longitude),
  KEY idx_category (category),
  CONSTRAINT fk_gi_activity_source FOREIGN KEY (source_id) REFERENCES gi_sources(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gi_sync_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id VARCHAR(80) NULL,
  status ENUM('pending','running','done','failed') NOT NULL DEFAULT 'pending',
  reason VARCHAR(80) NOT NULL DEFAULT 'manual',
  search_json JSON NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  error TEXT NULL,
  created_at DATETIME NOT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  KEY idx_status_created (status, created_at),
  CONSTRAINT fk_gi_job_source FOREIGN KEY (source_id) REFERENCES gi_sources(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
