-- Create table for Auvik tenant to Autotask company mappings
CREATE TABLE IF NOT EXISTS auvik_tenant_mappings (
    id SERIAL PRIMARY KEY,
    auvik_tenant_id VARCHAR(255) NOT NULL UNIQUE,
    auvik_tenant_name VARCHAR(255) NOT NULL,
    autotask_company_id INTEGER NOT NULL,
    autotask_company_name VARCHAR(255) NOT NULL,
    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_tenant_id ON auvik_tenant_mappings(auvik_tenant_id);
CREATE INDEX IF NOT EXISTS idx_autotask_company_id ON auvik_tenant_mappings(autotask_company_id);

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

CREATE TRIGGER auvik_tenant_mappings_updated_at
    BEFORE UPDATE ON auvik_tenant_mappings
    FOR EACH ROW
    EXECUTE FUNCTION update_auvik_tenant_mappings_updated_at();