-- Create table for Addigy organization to Autotask company mappings
CREATE TABLE IF NOT EXISTS addigy_org_mappings (
    id SERIAL PRIMARY KEY,
    addigy_org_id VARCHAR(255) NOT NULL UNIQUE,
    addigy_org_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_addigy_org_id ON addigy_org_mappings(addigy_org_id);
CREATE INDEX IF NOT EXISTS idx_addigy_autotask_company_id ON addigy_org_mappings(autotask_company_id);

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

CREATE TRIGGER addigy_org_mappings_updated_at
    BEFORE UPDATE ON addigy_org_mappings
    FOR EACH ROW
    EXECUTE FUNCTION update_addigy_org_mappings_updated_at();