-- =========================================================
-- SCHEMA: Tabella routers per gestione router MikroTik
-- =========================================================
-- Memorizza configurazione router per comunicazione API REST
-- =========================================================

-- Drop tabella se esiste (solo per ambiente di sviluppo)
DROP TABLE IF EXISTS routers CASCADE;

-- Crea tabella routers
CREATE TABLE routers (
    id VARCHAR PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    ip_address TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    api_port INTEGER NOT NULL DEFAULT 443,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    last_check TIMESTAMP,
    status TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indici per performance
CREATE INDEX IF NOT EXISTS routers_ip_idx ON routers(ip_address);
CREATE INDEX IF NOT EXISTS routers_enabled_idx ON routers(enabled);

-- Commento tabella
COMMENT ON TABLE routers IS 'Configurazione router MikroTik per comunicazione API REST';

-- Inserisci router di esempio (FIBRA)
INSERT INTO routers (name, ip_address, username, password, api_port, enabled)
VALUES ('Router FIBRA', '192.178.204.1', 'admin', 'change_me', 443, true)
ON CONFLICT (ip_address) DO NOTHING;

-- Verifica
SELECT 'Tabella routers creata con successo!' AS status;