
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- payment_subscription
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `payment_subscription`;


CREATE TABLE `payment_subscription`
(
	`subid` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255)  NOT NULL,
	`status` INTEGER(11) default 0 NOT NULL,
	`timeframe_choice` INTEGER(11) default 0 NOT NULL,
	`timeframe_value` INTEGER(11) default 30 NOT NULL,
	`portal` TINYINT(1) default 0 NOT NULL,
	`chid` INTEGER(11),
	`lsid` INTEGER(11),
	`ends_automatically` TINYINT(1) default 1 NOT NULL,
	`price` DOUBLE(12,2) default 0 NOT NULL,
	`created_at` DATETIME  NOT NULL,
	`updated_at` DATETIME  NOT NULL,
	PRIMARY KEY (`subid`),
	KEY `ps_i_chid`(`chid`),
	KEY `ps_i_lsid`(`lsid`),
	CONSTRAINT `ps_fk_chid`
		FOREIGN KEY (`chid`)
		REFERENCES `channel` (`chid`),
	CONSTRAINT `ps_fk_lsid`
		FOREIGN KEY (`lsid`)
		REFERENCES `livestream` (`lsid`)
)Engine=InnoDB;

#-----------------------------------------------------------------------------
#-- payment_ordered_subscription
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `payment_ordered_subscription`;


CREATE TABLE `payment_ordered_subscription`
(
	`osid` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`subid` INTEGER(11)  NOT NULL,
	`uid` INTEGER(11)  NOT NULL,
	`portal` TINYINT(1) default 0 NOT NULL,
	`chid` INTEGER(11),
	`lsid` INTEGER(11),
	`ends_automatically` TINYINT(1) default 1 NOT NULL,
	`status` INTEGER(11) default 0 NOT NULL,
	`provider_name` VARCHAR(255)  NOT NULL,
	`provider_transaction_id` VARCHAR(255),
	`created_at` DATETIME  NOT NULL,
	`updated_at` DATETIME  NOT NULL,
	`expired_at` DATETIME  NOT NULL,
	PRIMARY KEY (`osid`),
	KEY `pos_i_subid`(`subid`),
	KEY `pos_i_uid`(`uid`),
	KEY `pos_i_chid`(`chid`),
	KEY `pos_i_lsid`(`lsid`),
	CONSTRAINT `pos_fk_subid`
		FOREIGN KEY (`subid`)
		REFERENCES `payment_subscription` (`subid`),
	CONSTRAINT `pos_fk_uid`
		FOREIGN KEY (`uid`)
		REFERENCES `user` (`uid`),
	CONSTRAINT `pos_fk_chid`
		FOREIGN KEY (`chid`)
		REFERENCES `channel` (`chid`),
	CONSTRAINT `pos_fk_lsid`
		FOREIGN KEY (`lsid`)
		REFERENCES `livestream` (`lsid`)
)Engine=InnoDB;

#-----------------------------------------------------------------------------
#-- payment_producer
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `payment_producer`;


CREATE TABLE `payment_producer`
(
	`proid` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`uid` INTEGER(11)  NOT NULL,
	`share_credits` DOUBLE(12,2) default 0 NOT NULL,
	`share_subscription` VARCHAR(255) default '' NOT NULL,
	`share_individual_sale` VARCHAR(255) default '' NOT NULL,
	`status` INTEGER(11) default 0 NOT NULL,
	`created_at` DATETIME  NOT NULL,
	`updated_at` DATETIME  NOT NULL,
	PRIMARY KEY (`proid`),
	KEY `pp_i_uid`(`uid`),
	CONSTRAINT `pp_fk_uid`
		FOREIGN KEY (`uid`)
		REFERENCES `user` (`uid`)
)Engine=InnoDB;

#-----------------------------------------------------------------------------
#-- payment_accounting
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `payment_accounting`;


CREATE TABLE `payment_accounting`
(
	`accid` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`osid` INTEGER(11),
	`mid` INTEGER(11),
	`chid` INTEGER(11),
	`lsid` INTEGER(11),
	`pay_mode` TINYINT(1) default 0 NOT NULL,
	`pay_option` INTEGER(11) default 0 NOT NULL,
	`uid` INTEGER(11)  NOT NULL,
	`producer_uid` INTEGER(11) default 0 NOT NULL,
	`credits_paid` DOUBLE(12,2) default 0 NOT NULL,
	`created_at` DATETIME  NOT NULL,
	`updated_at` DATETIME  NOT NULL,
	`expired_at` DATETIME  NOT NULL,
	PRIMARY KEY (`accid`),
	KEY `pa_i_uid`(`uid`),
	KEY `pa_i_mid`(`mid`),
	KEY `pa_i_chid`(`chid`),
	KEY `pa_i_osid`(`osid`),
	KEY `pa_i_lsid`(`lsid`),
	CONSTRAINT `pa_fk_uid`
		FOREIGN KEY (`uid`)
		REFERENCES `user` (`uid`),
	CONSTRAINT `pa_fk_mid`
		FOREIGN KEY (`mid`)
		REFERENCES `media` (`mid`),
	CONSTRAINT `pa_fk_chid`
		FOREIGN KEY (`chid`)
		REFERENCES `channel` (`chid`),
	CONSTRAINT `pa_fk_osid`
		FOREIGN KEY (`osid`)
		REFERENCES `payment_ordered_subscription` (`osid`),
	CONSTRAINT `pa_fk_lsid`
		FOREIGN KEY (`lsid`)
		REFERENCES `livestream` (`lsid`)
)Engine=InnoDB;

#-----------------------------------------------------------------------------
#-- payment_transaction_accounting
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `payment_transaction_accounting`;


CREATE TABLE `payment_transaction_accounting`
(
	`taid` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`accid` INTEGER(11),
	`uid` INTEGER(11)  NOT NULL,
	`username` VARCHAR(255)  NOT NULL,
	`inbound` TINYINT(1) default 1 NOT NULL,
	`amount` DOUBLE(12,2) default 0 NOT NULL,
	`comment` VARCHAR(255)  NOT NULL,
	`payment_link` VARCHAR(255),
	`created_at` DATETIME  NOT NULL,
	PRIMARY KEY (`taid`),
	KEY `pta_i_uid`(`uid`),
	KEY `pta_i_accid`(`accid`),
	CONSTRAINT `pta_fk_uid`
		FOREIGN KEY (`uid`)
		REFERENCES `user` (`uid`),
	CONSTRAINT `pta_fk_accid`
		FOREIGN KEY (`accid`)
		REFERENCES `payment_accounting` (`accid`)
)Engine=InnoDB;

#-----------------------------------------------------------------------------
#-- payment_user
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `payment_user`;


CREATE TABLE `payment_user`
(
	`pusid` INTEGER(11)  NOT NULL AUTO_INCREMENT,
	`uid` INTEGER(11)  NOT NULL,
	`credits` DOUBLE(12,2) default 0 NOT NULL,
	`created_at` DATETIME  NOT NULL,
	`updated_at` DATETIME  NOT NULL,
	PRIMARY KEY (`pusid`),
	KEY `pu_i_uid`(`uid`),
	CONSTRAINT `pu_fk_uid`
		FOREIGN KEY (`uid`)
		REFERENCES `user` (`uid`)
)Engine=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
