DELETE FROM OZELSIGORTACARI WHERE CHARINDEX('_',OZELSENTFIRMA)>0
GO

UPDATE   dbo.[OZELSIGORTACARI] SET AKTIF=0 WHERE AKTIF=NULL
GO

ALTER TABLE dbo.[OZELSIGORTACARI] ALTER COLUMN AKTIF BIT NOT NULL
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'SUBEID' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD SUBEID INT NULL
GO
UPDATE OZELSIGORTACARI SET SUBEID=ISNULL((SELECT TOP 1 SUBEID FROM (SELECT SUBEID,COUNT(HDOSYAID) AS SAYI FROM HDOSYA WHERE HDTARIH>DATEADD(HOUR,-3,GETDATE()) GROUP BY SUBEID) AS TBL ORDER BY SAYI DESC),1)
GO

ALTER TABLE dbo.[OZELSIGORTACARI] ALTER COLUMN SUBEID INT NOT NULL
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'PAKETLEME' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD PAKETLEME BIT NULL
GO

UPDATE OZELSIGORTACARI SET PAKETLEME=0
GO

ALTER TABLE dbo.[OZELSIGORTACARI] ALTER COLUMN PAKETLEME BIT NOT NULL
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'SERVISADRESI' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD SERVISADRESI NVARCHAR(300) NULL
GO
UPDATE OSCRI SET OSCRI.SERVISADRESI = FRM.SERVISADRESI FROM OZELSIGORTACARI OSCRI JOIN OZELSIGORTAENTFIRMA FRM ON FRM.OZELSENTFIRMA =OSCRI.OZELSENTFIRMA
GO

ALTER TABLE dbo.[OZELSIGORTACARI] ALTER COLUMN FIRMAKODU NVARCHAR(100) NULL
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'FIRMAANAHTARI' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD FIRMAANAHTARI NVARCHAR(100) NULL
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'TESTHESAP' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD TESTHESAP BIT NULL
GO
UPDATE OZELSIGORTACARI SET TESTHESAP=0
GO
ALTER TABLE dbo.[OZELSIGORTACARI] ALTER COLUMN TESTHESAP BIT NOT NULL
GO
IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'TOKEN' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD TOKEN NVARCHAR(1000) NULL
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'TOKENEXPIREMIN' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD TOKENEXPIREMIN INT NULL
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'TOKENEXPIREDATE' AND SYSOBJECTS.name = 'OZELSIGORTACARI')
ALTER TABLE dbo.[OZELSIGORTACARI] ADD TOKENEXPIREDATE DATETIME NULL
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'PK' AND name = 'PK_OZELSIGORTACARI')
BEGIN
    ALTER TABLE OZELSIGORTACARI
    DROP CONSTRAINT PK_OZELSIGORTACARI;
END

ALTER TABLE dbo.OZELSIGORTACARI ADD CONSTRAINT PK_OZELSIGORTACARI PRIMARY KEY CLUSTERED ([AKTIF] ASC, [PAKETLEME] ASC, [TESTHESAP] ASC, [OZELSENTFIRMA] ASC, [CARIID] ASC)
GO