-- Create table for RMM sites (all sites from Datto RMM API)
CREATE TABLE IF NOT EXISTS rmm_sites (
    id SERIAL PRIMARY KEY,
    site_uid VARCHAR(255) NOT NULL UNIQUE,
    site_name VARCHAR(255) NOT NULL,
    device_count INTEGER DEFAULT 0,
    last_sync_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_rmm_sites_uid ON rmm_sites(site_uid);

-- Add updated_at trigger
CREATE OR REPLACE FUNCTION update_rmm_sites_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS rmm_sites_updated_at ON rmm_sites;
CREATE TRIGGER rmm_sites_updated_at
    BEFORE UPDATE ON rmm_sites
    FOR EACH ROW
    EXECUTE FUNCTION update_rmm_sites_updated_at();