<?php

///////////////////////////////////////////////////////////////////////////////////////////////////
// DATABASE TABLES DEFINITION /////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////

// Table description - $DBTSupplierInvoices
$CFG['monthTablesStructure']['supplierInvoices'] = "
	CREATE TABLE IF NOT EXISTS `%s` (
		`id` BIGINT(15) UNSIGNED NOT NULL AUTO_INCREMENT,
		`deleted` TINYINT(1) NOT NULL DEFAULT '0',
		`idSupplier` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
		`idBankAccount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
		`statusOp` ENUM('notConfirmed', 'confirmed') NOT NULL DEFAULT 'notConfirmed',
		`statusFin` ENUM( 'n', 'p', 't', 'o' ) NOT NULL DEFAULT 'n',
		`invoiceDate` DATE NOT NULL,
		`invoiceSeries` VARCHAR(30) NULL DEFAULT NULL,
		`invoiceNumber` VARCHAR(30) NOT NULL DEFAULT '',
		`invoiceComments` TEXT NOT NULL,
		`formOfPayment` VARCHAR(20) NULL DEFAULT NULL,
		`dueDate` DATE NULL DEFAULT NULL,
		`fulfillmentDate` DATE NULL DEFAULT NULL,
		`estPayDate` DATE NULL DEFAULT NULL,
		`priceValue` DECIMAL( 16, 3 ) NOT NULL DEFAULT '0',
		`vatValue` DECIMAL( 16, 3 ) NOT NULL DEFAULT '0',
		`totalValue` DECIMAL( 16, 3 ) NOT NULL DEFAULT '0',
		`payedValue` DECIMAL( 21, 8 ) NOT NULL DEFAULT '0',
		`currency` CHAR(3) NOT NULL DEFAULT '',
		`remarks` TEXT NOT NULL,
		`processedRemarks` TEXT NOT NULL,
		`extraInfo` TEXT NULL,
		`idUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' ,
		`idOffice` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' ,
		`idAddUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' ,
		`addTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' ,
		`idUpdateUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' ,
		`lastUpdateTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' ,
		`updatesCount` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0',
		`blockable` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' ,
		`idBlockUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' ,
		`blockTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' ,
		PRIMARY KEY (`id`),
		KEY `invoiceDate` (`invoiceDate`),
		KEY `idUser` (`idUser`),
		KEY `idOffice` (`idOffice`),
		KEY `idSupplier` (`idSupplier`),
		KEY `invoiceNumber` (`invoiceNumber`)
	) ENGINE=INNODB DEFAULT CHARSET={$CFG['encodings']['dbCharacterSet']} COLLATE={$CFG['encodings']['dbCollation']} AUTO_INCREMENT=%s ROW_FORMAT=DYNAMIC
";


// Table description - $DBTSupplierInvoiceRows
$CFG['monthTablesStructure']['supplierInvoiceRows'] = "
	CREATE TABLE IF NOT EXISTS `%s` (
		`id` BIGINT(15) UNSIGNED NOT NULL AUTO_INCREMENT,
		`idInvoice` BIGINT(15) UNSIGNED NOT NULL DEFAULT '0',
		`serviceCode` CHAR(2) NOT NULL DEFAULT '',
		`description` VARCHAR(500) NULL DEFAULT NULL,
		`rowType` VARCHAR(15) NULL DEFAULT NULL,
		`externalReservationId` VARCHAR(30) NULL DEFAULT NULL,
		`externalReservationDate` DATE NULL DEFAULT NULL,
		`confirmationNumber` VARCHAR(50) NULL DEFAULT NULL,
		`idPassenger` INT(10) UNSIGNED NULL DEFAULT NULL,
		`checkIn` DATE NULL DEFAULT NULL,
		`checkOut` DATE NULL DEFAULT NULL,
		`contractNumber` VARCHAR(30) NULL DEFAULT NULL,
		`ticketNo` VARCHAR(30) NULL DEFAULT NULL,
		`idSupplierProduct` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
		`countryCode` CHAR(2) NULL DEFAULT NULL,
		`cityId` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
		`reservationCode` VARCHAR(50) NULL DEFAULT NULL,
		`reservationNumber` VARCHAR(100) NULL DEFAULT NULL,
		`reservationId` BIGINT(15) UNSIGNED NULL DEFAULT NULL,
		`idService` BIGINT( 15 ) UNSIGNED NULL DEFAULT NULL,
		`measureUnit` VARCHAR(30) NULL DEFAULT NULL,
		`quantity` DECIMAL( 8, 2 ) NOT NULL DEFAULT '0',
		`unitPriceValue` DECIMAL( 16, 3 ) NOT NULL DEFAULT '0',
		`priceValue` DECIMAL( 16, 3 ) NOT NULL DEFAULT '0',
		`vatValue` DECIMAL( 16, 3 ) NOT NULL DEFAULT '0',
		`vatPercent` DECIMAL( 5, 2 ) NOT NULL DEFAULT '0',
		`vatText` VARCHAR(100) NOT NULL DEFAULT '',
		`idBalance` BIGINT(15) UNSIGNED NOT NULL DEFAULT '0',
		PRIMARY KEY (`id`),
		KEY `idInvoice` (`idInvoice`),
		KEY `serviceCode` (`serviceCode`)
	) ENGINE=INNODB DEFAULT CHARSET={$CFG['encodings']['dbCharacterSet']} COLLATE={$CFG['encodings']['dbCollation']} AUTO_INCREMENT=%s ROW_FORMAT=DYNAMIC
";


// Table description - $DBTSupplierInvoicePayment
$CFG['monthTablesStructure']['supplierInvoicePayments'] = "
	CREATE TABLE IF NOT EXISTS `%s` (
		`id` BIGINT(15) UNSIGNED NOT NULL AUTO_INCREMENT,
		`deleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
		`idInvoice` BIGINT(15) UNSIGNED NOT NULL DEFAULT '0',
		`idPaymentSeries` MEDIUMINT( 8 ) UNSIGNED NOT NULL DEFAULT '0',
		`docType` VARCHAR(25) NOT NULL DEFAULT '',
		`docDate` DATE NOT NULL,
		`docNumber` VARCHAR(50) NOT NULL DEFAULT '',
		`value` DECIMAL( 16, 3 ) NOT NULL DEFAULT '0',
		`currency` CHAR(3) NOT NULL DEFAULT '',
		`invoicePayedValue` DECIMAL( 21, 8 ) NOT NULL DEFAULT '0',
		`idBankAccount` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
		`explanations` TEXT NULL DEFAULT NULL,
		`comments` TEXT NULL DEFAULT NULL,
		`idImportedDoc` INT(10) UNSIGNED NOT NULL DEFAULT '0',
		`remarks` TEXT NOT NULL,
		`processedRemarks` TEXT NOT NULL,
		`extraInfo` TEXT NOT NULL,
		`idUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
		`idOffice` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
		`idIssueUser` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0',
		`issueTime` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0',
		`idAddUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
		`addTime` INT(10) UNSIGNED NOT NULL DEFAULT '0',
		`idUpdateUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
		`lastUpdateTime` INT(10) UNSIGNED NOT NULL DEFAULT '0',
		`updatesCount` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
		`idBlockUser` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
		`blockTime` INT(10) UNSIGNED NOT NULL DEFAULT '0',
		PRIMARY KEY (`id`),
		KEY (`idInvoice`)
	) ENGINE=INNODB DEFAULT CHARSET={$CFG['encodings']['dbCharacterSet']} COLLATE={$CFG['encodings']['dbCollation']} AUTO_INCREMENT=%s ROW_FORMAT=DYNAMIC
";

