-- Migration: Create RMM Site Mappings Table
-- Purpose: Support mapping multiple RMM sites to a single PSA company
-- Date: 2024-11-05

-- Create the RMM site mappings table
CREATE TABLE IF NOT EXISTS rmm_site_mappings (
  id SERIAL PRIMARY KEY,
  company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  rmm_site_uid VARCHAR(255) NOT NULL,
  rmm_site_name VARCHAR(255) NOT NULL,
  is_primary BOOLEAN DEFAULT false,
  device_count INTEGER DEFAULT 0, -- Cache device count for performance
  notes TEXT,
  last_sync_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(255),
  UNIQUE(company_id, rmm_site_uid)
);

-- Create indexes for better query performance
CREATE INDEX idx_rmm_site_mappings_company ON rmm_site_mappings(company_id);
CREATE INDEX idx_rmm_site_mappings_site_uid ON rmm_site_mappings(rmm_site_uid);
CREATE INDEX idx_rmm_site_mappings_is_primary ON rmm_site_mappings(is_primary) WHERE is_primary = true;

-- Add comments for documentation
COMMENT ON TABLE rmm_site_mappings IS 'Maps RMM sites to PSA companies, supporting multiple sites per company';
COMMENT ON COLUMN rmm_site_mappings.company_id IS 'Foreign key to companies table';
COMMENT ON COLUMN rmm_site_mappings.rmm_site_uid IS 'Unique identifier from RMM system';
COMMENT ON COLUMN rmm_site_mappings.rmm_site_name IS 'Display name of the RMM site';
COMMENT ON COLUMN rmm_site_mappings.is_primary IS 'Indicates if this is the primary site for the company';
COMMENT ON COLUMN rmm_site_mappings.device_count IS 'Cached count of devices in this site';
COMMENT ON COLUMN rmm_site_mappings.notes IS 'Optional notes about the site mapping';
COMMENT ON COLUMN rmm_site_mappings.last_sync_at IS 'Timestamp of last successful device sync';
COMMENT ON COLUMN rmm_site_mappings.created_by IS 'User who created the mapping';

-- Create trigger to update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_rmm_site_mappings_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_rmm_site_mappings_updated_at
BEFORE UPDATE ON rmm_site_mappings
FOR EACH ROW
EXECUTE FUNCTION update_rmm_site_mappings_updated_at();

-- Create a view for easier querying with company names
CREATE OR REPLACE VIEW rmm_site_mappings_view AS
SELECT 
  rsm.*,
  c.company_name,
  c.company_number,
  c.is_active AS company_is_active
FROM rmm_site_mappings rsm
JOIN companies c ON c.id = rsm.company_id;

-- Grant permissions (adjust as needed for your setup)
GRANT SELECT, INSERT, UPDATE, DELETE ON rmm_site_mappings TO PUBLIC;
GRANT SELECT ON rmm_site_mappings_view TO PUBLIC;
GRANT USAGE, SELECT ON SEQUENCE rmm_site_mappings_id_seq TO PUBLIC;