PRAGMA foreign_keys = OFF;

CREATE TABLE service_ips (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service_id INTEGER NOT NULL REFERENCES services(id) ON DELETE CASCADE,
    ip TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(service_id, ip)
);

CREATE INDEX idx_service_ips_service_id ON service_ips(service_id);

CREATE TABLE service_binding_records (
    binding_id INTEGER NOT NULL REFERENCES service_bindings(id) ON DELETE CASCADE,
    dns_record_id INTEGER NOT NULL REFERENCES dns_records(id) ON DELETE CASCADE,
    PRIMARY KEY (binding_id, dns_record_id)
);

INSERT INTO service_ips (service_id, ip, created_at)
SELECT DISTINCT sb.service_id, dr.content, datetime('now')
FROM service_bindings sb
JOIN dns_records dr ON dr.id = sb.dns_record_id
WHERE dr.record_type = 'A'
  AND dr.content IS NOT NULL
  AND TRIM(dr.content) != ''
ON CONFLICT(service_id, ip) DO NOTHING;

INSERT INTO service_binding_records (binding_id, dns_record_id)
SELECT sb.id, sb.dns_record_id
FROM service_bindings sb
WHERE sb.dns_record_id IS NOT NULL
ON CONFLICT(binding_id, dns_record_id) DO NOTHING;

INSERT INTO services (name, slug) VALUES
    ('VPN Panel', 'vpn-panel'),
    ('VPN Node', 'vpn-node'),
    ('Home Assistant', 'home-assistant')
ON CONFLICT(slug) DO NOTHING;

PRAGMA foreign_keys = ON;