--
-- Empty cart_products table
--
DELETE FROM `cart_products`;

--
-- Create carts table
DROP TABLE IF EXISTS `carts`;
CREATE TABLE `carts` (
    `cart_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(11) unsigned NOT NULL,
    `transaction_id` int(11) unsigned NULL,
    `cart_timestamp_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `cart_timestamp_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `active` tinyint(1) unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY (`cart_id`),
    KEY `user_id` (`user_id`),
    KEY `transaction_id` (`transaction_id`),
    KEY `active` (`active`),
    CONSTRAINT `carts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `carts_ibfk_2` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`transaction_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Remove user_id from cart_products
--
ALTER TABLE `cart_products` DROP FOREIGN KEY `cart_products_ibfk_1`;
ALTER TABLE `cart_products` DROP INDEX `user_id`;
ALTER TABLE `cart_products` DROP `user_id`;


--
-- Add cart_id on cart_products
--
ALTER TABLE `cart_products` ADD `cart_id` int(11) unsigned NOT NULL AFTER `cart_product_id`;
ALTER TABLE `cart_products` ADD INDEX `cart_id` (`cart_id`);
ALTER TABLE `cart_products` ADD CONSTRAINT `cart_products_ibfk_8` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`cart_id`) ON DELETE CASCADE ON UPDATE CASCADE;
