-- FastCronPHP schema
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  plan VARCHAR(50) NOT NULL DEFAULT 'Premium',
  expires_at DATE NOT NULL,
  telegram_enabled TINYINT(1) NOT NULL DEFAULT 0,
  telegram_chat_id VARCHAR(64) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  domain VARCHAR(180) NOT NULL,
  base_url VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX(user_id),
  CONSTRAINT fk_domains_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dhru_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  domain_id INT NOT NULL,
  dhru_url VARCHAR(255) NULL,
  api_key VARCHAR(255) NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  INDEX(user_id),
  INDEX(domain_id),
  CONSTRAINT fk_dhru_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_dhru_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  domain_id INT NULL,
  job_group ENUM('manual','dhru_auto','price_update') NOT NULL,
  title VARCHAR(200) NOT NULL,
  url VARCHAR(600) NOT NULL,
  method VARCHAR(10) NOT NULL DEFAULT 'GET',
  headers_json TEXT NOT NULL,
  body_json MEDIUMTEXT NULL,
  interval_seconds INT NOT NULL,
  timeout_seconds INT NULL,
  retry_count INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  next_run_at DATETIME NOT NULL,
  last_scheduled_at DATETIME NULL,
  last_run_at DATETIME NULL,
  last_status_code INT NULL,
  last_ok TINYINT(1) NULL,
  running_until DATETIME NULL,
  created_at DATETIME NOT NULL,
  INDEX(user_id),
  INDEX(domain_id),
  INDEX(next_run_at),
  CONSTRAINT fk_jobs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_jobs_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS job_runs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL,
  user_id INT NOT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NOT NULL,
  duration_ms INT NOT NULL,
  status_code INT NOT NULL DEFAULT 0,
  ok TINYINT(1) NOT NULL DEFAULT 0,
  response_snippet TEXT NOT NULL,
  error_message TEXT NULL,
  INDEX(job_id),
  INDEX(user_id),
  INDEX(started_at),
  CONSTRAINT fk_runs_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_runs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;