CREATE TABLE IF NOT EXISTS company_subscriptions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  plan_code VARCHAR(60) NOT NULL DEFAULT 'trial',
  status VARCHAR(40) NOT NULL DEFAULT 'trialing',
  seats INT UNSIGNED NOT NULL DEFAULT 3,
  monthly_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'CHF',
  trial_ends_at DATETIME NULL,
  current_period_end DATETIME NULL,
  stripe_customer_id VARCHAR(190),
  stripe_subscription_id VARCHAR(190),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_company_subscriptions_company (company_id),
  INDEX idx_company_subscriptions_status (status),
  CONSTRAINT fk_company_subscriptions_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS demo_reset_jobs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  interval_hours INT UNSIGNED NOT NULL DEFAULT 24,
  last_reset_at DATETIME NULL,
  next_reset_at DATETIME NULL,
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_demo_reset_company (company_id),
  CONSTRAINT fk_demo_reset_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS field_reports (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  intervention_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  arrival_status VARCHAR(80),
  before_note TEXT,
  after_note TEXT,
  materials_used TEXT,
  client_signature_name VARCHAR(190),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_field_reports_company (company_id),
  INDEX idx_field_reports_intervention (intervention_id),
  CONSTRAINT fk_field_reports_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_field_reports_intervention FOREIGN KEY (intervention_id) REFERENCES interventions(id) ON DELETE CASCADE,
  CONSTRAINT fk_field_reports_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;