exports.up = function(knex) {
    return knex.schema.raw(
            "if not exists (SELECT * FROM dbo.sysobjects where id = object_id("+
            "N'dbo.[ACOBYPAG_VENCIMIENTOS]') and OBJECTPROPERTY(id, N'IsTable') = 1) begin "+
            "CREATE TABLE ACOBYPAG_VENCIMIENTOS(" +
                "[CYV] [char](1) NOT NULL," +
                "[COD] [int] NOT NULL," +
                "[FEP] [smalldatetime] NOT NULL," +
                "[TIP] [char](1) NOT NULL," +
                "[TCO] [char](2) NOT NULL," +
                "[SUC] [int] NOT NULL," +
                "[NCO] [bigint] NOT NULL," +
                "[NCU] [bigint] NOT NULL," +
                "[IPA] [money] NOT NULL," +
                "[SAL] [money] NOT NULL," +
                "[TCA] [money] NOT NULL," +
                "[ZONA] [int] NOT NULL," +
                "[FPA] [char](1) NOT NULL," +
                "[REC] [bigint] NOT NULL," +
                "[REP] [bigint] NOT NULL," +
                "[FER] [smalldatetime] NULL," +
                "[REM] [bigint] NOT NULL," +
                "[FRE] [smalldatetime] NULL," +
                "[PRO] [char](1) NOT NULL," +
                "[FEV] [datetime] NULL," +
                "[ANU] [char](1) NOT NULL," +
                "[CCU] [int] NOT NULL," +
                "[UCU] [int] NOT NULL," +
                "[PLA] [int] NOT NULL," +
                "[LUG] [int] NOT NULL," +
                "[PFA] [char](1) NOT NULL," +
                "[RES] [int] NOT NULL," +
                "[LEG] [varchar](15) NOT NULL," +
                "[CTA] [int] NOT NULL," +
                "[FAR] [bit] NOT NULL," +
                "[HOS] [varchar](1) NOT NULL," +
                "[E_HD] [varchar](8) NOT NULL," +
                "[C_HD] [varchar](1) NOT NULL," +
                "[DIC_SEL] [int] NOT NULL," +
                "[SALDO_CALC] [money] NOT NULL," +
                "[IDLP] [varchar](1) NOT NULL," +
                "[ES_CANJE] [bit] NOT NULL," +
                "[CAMBIO_APLICADO] [varchar](50) NOT NULL," +
                "[TIPO_CANJE] [bit] NOT NULL," +
                "[ORD_COM] [int] NOT NULL," +
                "[NATHB] [varchar](1) NOT NULL," +
             "CONSTRAINT [PK_ACOBYPAG_VENCIMIENTOS] PRIMARY KEY CLUSTERED" +
            "(" +
                "[CYV] ASC," +
                "[COD] ASC," +
                "[FEP] ASC," +
                "[TIP] ASC," +
                "[TCO] ASC," +
                "[SUC] ASC," +
                "[NCO] ASC," +
                "[NCU] ASC" +
            ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]" +
            ") ON [PRIMARY]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_IPA] DEFAULT ((0)) FOR [IPA]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_SAL] DEFAULT ((0)) FOR [SAL]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_TCA] DEFAULT ((1)) FOR [TCA]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_ZONA] DEFAULT (' ') FOR [ZONA]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_FPA] DEFAULT ((1)) FOR [FPA]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_REC] DEFAULT ((0)) FOR [REC]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_REP] DEFAULT ((0)) FOR [REP]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_REM] DEFAULT ((0)) FOR [REM]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_PRO] DEFAULT ('N') FOR [PRO]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_ANU] DEFAULT (' ') FOR [ANU]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_CCU] DEFAULT ((0)) FOR [CCU]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_UCU] DEFAULT ((0)) FOR [UCU]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_PLA] DEFAULT ((0)) FOR [PLA]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_LUG] DEFAULT ((0)) FOR [LUG]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_PFA] DEFAULT ('N') FOR [PFA]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_RES] DEFAULT ((0)) FOR [RES]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_LEG] DEFAULT ('') FOR [LEG]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_CTA] DEFAULT ((0)) FOR [CTA]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF_ACOBYPAG_VENCIMIENTOS_FAR] DEFAULT ((0)) FOR [FAR]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF__ACOBYPAG_VENCIMIENTOS__HOS__383BDA07] DEFAULT ('') FOR [HOS]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF__ACOBYPAG_VENCIMIENTOS__E_HD__50D27DA7] DEFAULT ('') FOR [E_HD]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD CONSTRAINT [DF__ACOBYPAG_VENCIMIENTOS__C_HD__51C6A1E0] DEFAULT ('') FOR [C_HD]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [DIC_SEL]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [SALDO_CALC]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ('') FOR [IDLP]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [ES_CANJE]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ('') FOR [CAMBIO_APLICADO]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [TIPO_CANJE]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ((0)) FOR [ORD_COM]" +
            "ALTER TABLE ACOBYPAG_VENCIMIENTOS ADD DEFAULT ('') FOR [NATHB]" +
            " end"
        ).raw(
            "if OBJECT_ID('[dbo].[VI_ACOBYPAG_AMAEFACT]') is not null "+ 
            "begin " +
            "drop view VI_ACOBYPAG_AMAEFACT "+
            "end " +
            "execute(' " +
            "CREATE VIEW [VI_ACOBYPAG_AMAEFACT]" +
            "AS " +
            "SELECT A.*, A.FEP as FECHA_COMPROBANTE, C.ID_MONEDA AS MONEDA,C.COTIZACION AS COTIZACION," +
            "B.COD AS CODIGO_CLIENTE,B.NOM AS NOMBRE_CLIENTE,B.DOM AS DOMICILIO_CLIENTE,B.LOC AS LOCALIDAD,B.PCI AS PROVINCIA,B.CPO AS CODIGO_POSTAL," +
            "C.NET AS NETO," +
            "C.NEE AS NETO_EXENTO," +
            "C.IRI + C.IRS AS IVA," +
            "C.IMI + C.IMI2 + C.IMI3 AS IMPUESTOS_INTERNOS," +
            "C.PER AS PERCEPCION_IIBB," +
            "C.PER_IVA AS PERCECPION_IVA," +
            "C.TOT AS TOTAL " +
            "FROM ACOBYPAG A " +
            "FULL OUTER JOIN AMAEFACT C ON A.CYV = C.CYV  AND A.TIP=C.TIP AND A.TCO=C.TCO AND A.SUC=C.SUC AND A.NCO=C.NCO " +
            "INNER JOIN CLIENTES B ON A.COD=B.COD"+
            " ')"
        ).raw(
            "if OBJECT_ID('[dbo].[VI_ACOBYPAG_VENCIMIENTOS]') is not null "+ 
            "begin " +
            "drop view VI_ACOBYPAG_VENCIMIENTOS "+
            "end " +
            "execute(' " +
            "CREATE VIEW [VI_ACOBYPAG_VENCIMIENTOS]" +
            "AS " +
            "SELECT A.*,B.FEP AS FECHA_COMPROBANTE,C.ID_MONEDA AS MONEDA,C.COTIZACION AS COTIZACION," +
            "C.NET AS NETO," +
            "C.NEE AS NETO_EXENTO," +
            "C.IRI + C.IRS AS IVA," +
            "C.IMI + C.IMI2 + C.IMI3 AS IMPUESTOS_INTERNOS," +
            "C.PER AS PERCEPCION_IIBB," +
            "C.PER_IVA AS PERCECPION_IVA," +
            "C.TOT AS TOTAL " +
            "FROM ACOBYPAG_VENCIMIENTOS A " +
            "INNER JOIN ACOBYPAG B ON A.CYV = B.CYV  AND A.TIP=B.TIP AND A.TCO=B.TCO AND A.SUC=B.SUC AND A.NCO=B.NCO " +
            "INNER JOIN AMAEFACT C ON A.CYV = C.CYV  AND A.TIP=C.TIP AND A.TCO=C.TCO AND A.SUC=C.SUC AND A.NCO=C.NCO" +
            " ')"
        );
};

exports.down = function(knex) {
    return knex.schema
        .dropTable('ACOBYPAG_VENCIMIENTOS')
        .raw("DROP VIEW [VI_ACOBYPAG_AMAEFACT]")
        .raw("DROP VIEW [VI_ACOBYPAG_VENCIMIENTOS]");
};