USP_FAFEVENT_METADATACLEANUP

create procedure dbo.USP_FAFEVENT_METADATACLEANUP
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
begin
    declare @PUBLISHED bit, 
          @APPEALID uniqueidentifier,
          @CLIENTSITESID int,
          @DESIGNATIONID uniqueidentifier,
          @DESIGNATIONLEVELID uniqueidentifier


  if exists (select R.ID from dbo.EVENTEXTENSION EX join dbo.REGISTRANT R on EX.EVENTID = R.EVENTID where EX.EVENTID = @ID)
    raiserror('EVENTHASTRANSACTION', 13, 2)

  select 
    @APPEALID = EVENT.APPEALID, 
    @DESIGNATIONID=APPEALDESIGNATION.DESIGNATIONID,
    @DESIGNATIONLEVELID = DESIGNATION.DESIGNATIONLEVEL1ID,
    @CLIENTSITESID = EVENTEXTENSION.CLIENTSITESID, 
    @PUBLISHED = dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS(@ID) 
  from dbo.EVENT 
  left join dbo.EVENTEXTENSION on EVENT.ID = EVENTEXTENSION.EVENTID
  left join dbo.APPEALDESIGNATION on EVENT.APPEALID = APPEALDESIGNATION.APPEALID 
  left join dbo.DESIGNATION on APPEALDESIGNATION.DESIGNATIONID = DESIGNATION.ID 
  where EVENT.ID = @ID   

  if @PUBLISHED = 1
  begin
    raiserror('EVENTSITEISPUBLISHED',13,2);
  end

  if exists (select * from dbo.WIDGETEXTENSION    where EVENTWIDGETID IN (select ID from EVENTWIDGET where EVENTID=@ID))
      delete from dbo.WIDGETEXTENSION where EVENTWIDGETID IN (select ID from EVENTWIDGET where EVENTID=@ID)

  if exists (select * from dbo.EVENTWIDGET where EVENTID=@ID)
      delete from dbo.EVENTWIDGET where EVENTID=@ID

  if exists (select * from dbo.STORY where EVENTID=@ID)
    delete from dbo.STORY where EVENTID=@ID

  delete from
      dbo.APPEAL
  where 
      APPEAL.ID = @APPEALID

  exec dbo.USP_DESIGNATION_DELETE @ID=@DESIGNATIONID,@CHANGEAGENTID=@CHANGEAGENTID

  delete from 
    dbo.DESIGNATIONLEVEL
  where ID = @DESIGNATIONLEVELID

  update dbo.CMSSITESETTING
  set value = ''
  where CLIENTSITESID = @CLIENTSITESID and ENUMID in (10,11)

  update dbo.ClientSites 
  set
    Deleted = 1
  where ID = @CLIENTSITESID

  if (select COUNT(*) from sysobjects where parent_obj = OBJECT_ID(N'[dbo].[ClientSites]')) > 0
  begin
    declare @SiteGuid uniqueidentifier
    select @SiteGuid=Guid from dbo.ClientSites where ID = @CLIENTSITESID
    if @SiteGuid is not null
      exec dbo.spAuditThis @ClientUsersID=1,@AuditCodesID=8,@ObjectGuid=@SiteGuid,@ObjectTypesID=8
  end   


  delete from dbo.IDSETREGISTER where ID in (select ADHOCQUERYID from dbo.FAFEVENTADHOCQUERY where EVENTID = @ID)               
  delete from dbo.ADHOCQUERY where ID in (select ADHOCQUERYID from dbo.FAFEVENTADHOCQUERY where EVENTID = @ID)

end