-- Migration: Create ticket_notes table
-- Description: Stores Autotask ticket notes synced via webhooks

CREATE TABLE IF NOT EXISTS ticket_notes (
  id BIGINT PRIMARY KEY,
  ticket_id BIGINT NOT NULL,
  title VARCHAR(500),
  description TEXT,
  note_type INTEGER, -- Autotask noteType picklist
  publish INTEGER, -- Visibility: 1=All, 2=Internal, etc.
  creator_resource_id BIGINT,
  creator_type INTEGER, -- 1=Resource, 2=Contact, etc.
  last_activity_date TIMESTAMP WITH TIME ZONE,
  create_date_time TIMESTAMP WITH TIME ZONE,
  is_deleted BOOLEAN DEFAULT false,
  synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_ticket_notes_ticket_id ON ticket_notes(ticket_id);
CREATE INDEX IF NOT EXISTS idx_ticket_notes_creator ON ticket_notes(creator_resource_id);
CREATE INDEX IF NOT EXISTS idx_ticket_notes_create_date ON ticket_notes(create_date_time DESC);
CREATE INDEX IF NOT EXISTS idx_ticket_notes_is_deleted ON ticket_notes(is_deleted);

-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_ticket_notes_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS ticket_notes_updated_at ON ticket_notes;
CREATE TRIGGER ticket_notes_updated_at
  BEFORE UPDATE ON ticket_notes
  FOR EACH ROW
  EXECUTE FUNCTION update_ticket_notes_updated_at();

COMMENT ON TABLE ticket_notes IS 'Autotask ticket notes synced via webhooks';

-- Ensure webhook_configs has rows for all webhook-supported entities
INSERT INTO webhook_configs (entity_type, event_types, is_active) VALUES
  ('TicketNotes', '["create", "update", "delete"]', true),
  ('ConfigurationItems', '["create", "update", "delete"]', true)
ON CONFLICT (entity_type) DO NOTHING;