CREATE TABLE IF NOT EXISTS health_checks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider TEXT NOT NULL DEFAULT 'local',
  cf_healthcheck_id TEXT,
  cf_zone_id TEXT,
  name TEXT NOT NULL,
  protocol TEXT NOT NULL DEFAULT 'tcp',
  path TEXT,
  method TEXT,
  timeout INTEGER NOT NULL DEFAULT 5,
  interval_sec INTEGER NOT NULL DEFAULT 30,
  retries INTEGER NOT NULL DEFAULT 2,
  expected_status INTEGER,
  consecutive_fails INTEGER NOT NULL DEFAULT 2,
  consecutive_successes INTEGER NOT NULL DEFAULT 2,
  suspended INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS nodes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  service_id INTEGER NOT NULL REFERENCES services(id) ON DELETE CASCADE,
  address TEXT NOT NULL,
  protocol TEXT NOT NULL DEFAULT 'tcp',
  port INTEGER,
  enabled INTEGER NOT NULL DEFAULT 1,
  priority INTEGER NOT NULL DEFAULT 1,
  weight INTEGER NOT NULL DEFAULT 1,
  health_status TEXT NOT NULL DEFAULT 'unknown',
  health_check_id INTEGER REFERENCES health_checks(id) ON DELETE SET NULL,
  consecutive_failures INTEGER NOT NULL DEFAULT 0,
  consecutive_successes INTEGER NOT NULL DEFAULT 0,
  last_check_at TEXT,
  last_failure_reason TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(service_id, address)
);

CREATE TABLE IF NOT EXISTS binding_nodes (
  binding_id INTEGER NOT NULL REFERENCES service_bindings(id) ON DELETE CASCADE,
  node_id INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
  weight INTEGER NOT NULL DEFAULT 1,
  priority INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (binding_id, node_id)
);

CREATE INDEX IF NOT EXISTS idx_nodes_service ON nodes(service_id);
CREATE INDEX IF NOT EXISTS idx_binding_nodes_node ON binding_nodes(node_id);
CREATE INDEX IF NOT EXISTS idx_health_checks_cf ON health_checks(cf_healthcheck_id);

ALTER TABLE service_bindings ADD COLUMN routing_strategy TEXT NOT NULL DEFAULT 'round_robin';
ALTER TABLE service_bindings ADD COLUMN operation_version INTEGER NOT NULL DEFAULT 0;

ALTER TABLE ip_health_status ADD COLUMN consecutive_successes INTEGER NOT NULL DEFAULT 0;

INSERT INTO nodes (service_id, address, enabled, priority, weight, health_status)
SELECT service_id, ip, 1, 1, 1, 'unknown'
FROM service_ips
WHERE NOT EXISTS (
  SELECT 1 FROM nodes n WHERE n.service_id = service_ips.service_id AND n.address = service_ips.ip
);

INSERT INTO nodes (service_id, address, enabled, priority, weight, health_status)
SELECT DISTINCT sb.service_id, sbi.ip, 1, sbi.priority, sbi.weight, 'unknown'
FROM service_binding_ips sbi
JOIN service_bindings sb ON sb.id = sbi.binding_id
WHERE NOT EXISTS (
  SELECT 1 FROM nodes n WHERE n.service_id = sb.service_id AND n.address = sbi.ip
);

INSERT INTO binding_nodes (binding_id, node_id, weight, priority)
SELECT sbi.binding_id, n.id, sbi.weight, sbi.priority
FROM service_binding_ips sbi
JOIN service_bindings sb ON sb.id = sbi.binding_id
JOIN nodes n ON n.service_id = sb.service_id AND n.address = sbi.ip
WHERE NOT EXISTS (
  SELECT 1 FROM binding_nodes bn
  WHERE bn.binding_id = sbi.binding_id AND bn.node_id = n.id
);

UPDATE service_bindings SET routing_strategy = lb_mode WHERE routing_strategy = 'round_robin';