/*
* Qui scrivo gli SQL che si dovranno fare, indicando anche dove sono già stati fatti
* Sulmona
* Pineto
* Corridonia
*/

/** 29/06 **/

CREATE  TABLE IF NOT EXISTS `point_contracts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `point_id` INT(11) NOT NULL ,
  `document_id` INT(11) NULL DEFAULT NULL ,
  `expiration` DATE NOT NULL ,
  `status` SMALLINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 = da pagare, 2 = pagato, 9 = scaduto' ,
  PRIMARY KEY (`id`) ,
  INDEX `point_id` (`point_id` ASC) ,
  CONSTRAINT `point_contracts_ibfk_1`
    FOREIGN KEY (`point_id` )
    REFERENCES `points` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

ALTER TABLE  `point_contracts` ADD  `years` SMALLINT( 4 ) NULL AFTER  `expiration` ,
ADD  `purchase_date` DATE NULL AFTER  `years` ,
ADD  `payment_type` VARCHAR( 30 ) NULL AFTER  `purchase_date`;

ALTER TABLE  `point_contracts` ADD  `confirm_date` DATE NULL AFTER  `purchase_date`;

ALTER TABLE  `point_contracts` CHANGE  `status`  `status` SMALLINT( 1 ) UNSIGNED NOT NULL DEFAULT  '0' COMMENT  '0 = disponibile, 1 = acquistato, 2 = pagato, 9 = scaduto';

ALTER TABLE  `point_contracts` ADD  `price` FLOAT( 8, 2 ) NULL AFTER  `purchase_date`;


/* ViewPointsCategories */
CREATE VIEW `view_points_categories`
AS
select `categories_points`.`category_id` AS `category_id`,
`categories`.`name` AS `category_name`,
`points`.`id` AS `point_id`,
`points`.`name` AS `point_name`,
`attachments`.`name` AS `name`,
`points`.`lat` AS `lat`,
`points`.`lng` AS `lng`,
`points`.`address` AS `address`,
`points`.`status` AS `status`,
`point_contracts`.`status` as `contract_status`,
`point_contracts`.`expiration` as `expiration`
from
    `points`
	left join `categories_points`
        on(`categories_points`.`point_id` = `points`.`id`)
	left join `categories`
        on(`categories_points`.`category_id` = `categories`.`id`)
	left join (`attachments_points`
            join `attachments`
                on(`attachments_points`.`attachment_id` = `attachments`.`id`))
        on(`attachments_points`.`point_id` = `points`.`id`
	        and
	        `attachments_points`.`is_predefined` = 1)
    left join `point_contracts`
        on(`point_contracts`.`point_id` = `points`.`id`);
        

/* ViewPointsUsers */
CREATE VIEW `view_points_users` AS
SELECT
points_users.user_id as user_id,
points.id as point_id,
points.name as point_name,
attachments.name,
points.lat as lat,
points.lng as lng,
points.address,
points.status as status,
point_contracts.status as contract_status,
point_contracts.expiration as expiration
FROM
points LEFT JOIN points_users
ON 
points_users.point_id = points.id
LEFT JOIN (attachments_points 
JOIN
attachments
ON attachments_points.attachment_id = attachments.id)
ON
attachments_points.point_id = points.id
AND
attachments_points.is_predefined = 1
LEFT JOIN
point_contracts ON(point_contracts.point_id = points.id);  



INSERT INTO  `permissions` (
`id` ,
`module` ,
`controller` ,
`action` ,
`row` ,
`flag_exclusive` ,
`level` ,
`created` ,
`modified`
)
VALUES (
NULL ,  'Frontend',  'Users',  'purchase',  'frontend:users:purchase',  '0',  '10', NOW( ) , NOW( )
);  

INSERT INTO  `permissions` (
`id` ,
`module` ,
`controller` ,
`action` ,
`row` ,
`flag_exclusive` ,
`level` ,
`created` ,
`modified`
)
VALUES (
NULL ,  'Frontend',  'Users',  'confirmPurchase',  'frontend:users:confirmpurchase',  '0',  '10', NOW( ) , NOW( )
);

/*** FATTI a PINETO, CORRIDONIA, SULMONA ***/
