-- Create table for Auvik tenants (all tenants from Auvik API)
CREATE TABLE IF NOT EXISTS auvik_tenants (
    id SERIAL PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL UNIQUE,
    tenant_name VARCHAR(255) NOT NULL,
    domain_prefix VARCHAR(255),
    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_auvik_tenants_tenant_id ON auvik_tenants(tenant_id);

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

DROP TRIGGER IF EXISTS auvik_tenants_updated_at ON auvik_tenants;
CREATE TRIGGER auvik_tenants_updated_at
    BEFORE UPDATE ON auvik_tenants
    FOR EACH ROW
    EXECUTE FUNCTION update_auvik_tenants_updated_at();