/* Delete all news items older than XX days
 It should be done first*/

print 'Delete all news items older than XX days...'

begin transaction

DECLARE @id_to_clean table(id int NOT NULL);                                        

insert into @id_to_clean
select ph.id
from attrib_vals
        left outer join ph_to_attrvals     on attrib_vals.id = ph_to_attrvals.attrval_id
        left outer join ph         on ph.id = ph_to_attrvals.ph_id
    where 
        attrib_vals.deleted=0 and attrib_vals.type = 5
        and attrib_vals.attr_id=1 and attrib_vals.ForNewsBrowser=1
        and ph.deleted=0 and ph.AddDate<(dateadd( day, -14, CURRENT_TIMESTAMP))

ALTER TABLE [PH_TO_ATTRVALS] DISABLE TRIGGER OnPhToAttrVals
ALTER TABLE [ph] DISABLE TRIGGER ModifyPH

/* Clear all attribute links for deleted items (#3730)*/
DELETE FROM [PH_TO_ATTRVALS] WHERE Ph_ID IN (select * from @id_to_clean)
update ph set deleted=1, modifydate=CURRENT_TIMESTAMP where id in(select * from @id_to_clean)

ALTER TABLE [PH_TO_ATTRVALS] ENABLE TRIGGER OnPhToAttrVals
ALTER TABLE [ph] ENABLE TRIGGER ModifyPH

commit transaction

GO


/*Delete lost attrib links*/
print 'Delete lost attrib links...'

begin transaction

ALTER TABLE ph_to_attrvals DISABLE TRIGGER OnPhToAttrVals

delete from ph_to_attrvals where id in 
 (select  distinct ph_to_attrvals.id from ph_to_attrvals, attrib_vals 
  where 
   attrib_vals.id = ph_to_attrvals.attrval_id and 
   attrib_vals.deleted = 1)

delete from ph_to_attrvals where id in 
 (select  distinct ph_to_attrvals.id from ph_to_attrvals, ph 
  where 
   ph.id = ph_to_attrvals.ph_id and 
   ph.deleted = 1)

ALTER TABLE ph_to_attrvals ENABLE TRIGGER OnPhToAttrVals

commit transaction
GO


/*Clear recycle bin*/
print 'DELETE FROM PH...'
begin transaction

ALTER TABLE ph_to_attrvals DISABLE TRIGGER OnPhToAttrVals

DELETE FROM PH where deleted=1 AND modifydate<(dateadd( day, -7, CURRENT_TIMESTAMP)) AND 0 = (select count(*) from mp_phonograms where ph_id=PH.id)

ALTER TABLE ph_to_attrvals ENABLE TRIGGER OnPhToAttrVals

commit transaction
GO

/*Clear recycle bin*/
print 'DELETE FROM ATTRIB_VALS...'
DELETE FROM ATTRIB_VALS where deleted=1 AND modifydate<(dateadd( day, -1, CURRENT_TIMESTAMP))
GO

/*Clear recycle bin*/
print 'DELETE FROM CAT_JNGL_BTNS...'
DELETE FROM CAT_JNGL_BTNS where deleted=1 AND modifydate<(dateadd( day, -1, CURRENT_TIMESTAMP))
GO

/*Clear Old Scheule elems*/
print 'DELETE FROM PLIST_ELEMS...'
DELETE FROM PLIST_ELEMS where not exists(select id from PLIST_BLOCKS where id = PLIST_ELEMS.BlockID)
GO

/*Clear Old Scheule elems*/
print 'DELETE FROM PLIST_ELEMS...'
DELETE FROM PLIST_ELEMS 
where BlockID in(select ID FROM PLIST_BLOCKS where [date] < (dateadd( day, -3, CURRENT_TIMESTAMP))) 
GO

/*Clear Old Scheule Blocks*/
print 'DELETE FROM PLIST_BLOCKS...'
DELETE FROM PLIST_BLOCKS where [date] < (dateadd( day, -3, CURRENT_TIMESTAMP))
GO

/*Clear Old Changes*/
print 'DELETE FROM MODIFY_DATES...'
DELETE FROM MODIFY_DATES where modifydate< (dateadd( day, -1, CURRENT_TIMESTAMP))
GO

/*Clear sch_files*/
print 'DELETE FROM SCH_FILES...'
DELETE FROM SCH_FILES where SchDate < (dateadd( day, -3, CURRENT_TIMESTAMP))
GO

/*Delete old DDB blocks info*/
print 'Delete old DDB blocks info...'
DELETE FROM P_BLOCKS where BlkDate < (dateadd( day, -3, CURRENT_TIMESTAMP)) 
GO

/*Delete playback history older than 100 days*/
print 'Delete playback history older than 100 days...'
DELETE FROM PH_PLAY_HISTORY where PlayTime<(dateadd( day, -100, CURRENT_TIMESTAMP)) 
GO

/*Delete plan history older than 100 days*/
print 'Delete plan history older than 100 days...'
DELETE FROM PH_PLAY_PLAN where PlayTime<(dateadd( day, -100, CURRENT_TIMESTAMP)) 
GO

/* Delete old MAG2 shedule orders*/
print 'Delete old mag schedule orders'
DELETE  FROM [dbo].[MAG2_WORK] where WorkDate< dateadd( day, -14, GETDATE())
GO



/*Delete PH_HISTORY rows older than 30 days*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PH_HISTORY]') AND type in (N'U'))
BEGIN
	PRINT N'Delete PH_HISTORY rows older than 30 days'
	DECLARE @MinDate datetime
	SELECT @MinDate=dateadd(d,-30, GETDATE())
	
	DELETE	
	FROM [dbo].[PH_HISTORY] 
	WHERE ModifyDate < @MinDate	
END

GO



/*Truncating transaction log file for current database*/
print 'Truncating transaction log file for current database...'

declare @ver int
declare @ver_str varchar(128)
set @ver_str = convert(varchar(128),SERVERPROPERTY('ProductVersion'))
set @ver = cast( left(@ver_str, charindex('.', @ver_str)-1) as int )

declare @db_name varchar(128), 
	@dbcc_query varchar(256)

SELECT @db_name = DB_NAME()

if (@ver < 10)
BEGIN
	set @dbcc_query='BACKUP LOG ['+@db_name+'] WITH TRUNCATE_ONLY'
	EXEC (@dbcc_query)
END

set @dbcc_query='DBCC SHRINKDATABASE (['+@db_name+'], 0)'
EXEC (@dbcc_query)
GO