-- MySQL Workbench Synchronization
-- Generated: 2019-06-18 21:04
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: Tuxer

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER TABLE `kommu_dev`.`community`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`store`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`member`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`product`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`banner`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`invoice`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`product_price_log`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`session`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ;

ALTER TABLE `kommu_dev`.`otp`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`notification`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`member_device`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`news`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`media`
CHANGE COLUMN `content_type` `content_type` ENUM('image/jpeg', 'image/png') NULL DEFAULT NULL ;

ALTER TABLE `kommu_dev`.`bank_account`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`sales_order`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
CHANGE COLUMN `total_price` `total_amount` DOUBLE UNSIGNED NOT NULL DEFAULT 0 ,
CHANGE COLUMN `payment_status` `payment_status` ENUM('waiting', 'verification', 'verification_failed', 'verified', 'payout', 'refund') NOT NULL DEFAULT 'waiting' COMMENT 'created: order dibuat/checkout\nverification: buyer meminta verifikasi\nverified: pembayaran berhasil diverifikasi\nprocessing: order diproses\nsending: dikirim\nsent: terkirim\nreceived: barang diterima (auto no resi)\nconfirmed: barang diterima\npayout: pembayaran dikirim ke penjual\n\nrefund: pembayaran dikembalikan ke pembeli\nexpired: kadaluarsa' ;

ALTER TABLE `kommu_dev`.`sales_order_item`
ADD COLUMN `total_item_price` DOUBLE UNSIGNED NOT NULL DEFAULT 0 AFTER `item_quantity`,
ADD COLUMN `item_weight` INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `total_item_price`,
ADD COLUMN `total_item_weight` INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `item_weight`,
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
CHANGE COLUMN `price` `item_price` DOUBLE UNSIGNED NOT NULL DEFAULT 0 ,
CHANGE COLUMN `quantity` `item_quantity` INT(10) UNSIGNED NOT NULL DEFAULT 1 ;

ALTER TABLE `kommu_dev`.`sales_order_log`
CHANGE COLUMN `status` `status` ENUM('created', 'checkout', 'verification', 'verification_failed', 'verified', 'processing', 'sending', 'sent', 'received', 'confirmed', 'payout', 'refund', 'expired') NOT NULL DEFAULT 'created' COMMENT 'created: order dibuat/checkout\nverification: buyer meminta verifikasi\nverified: pembayaran berhasil diverifikasi\nprocessing: order diproses\nsending: dikirim\nsent: terkirim\nreceived: barang diterima (auto no resi)\nconfirmed: barang diterima\npayout: pembayaran dikirim ke penjual\n\nrefund: pembayaran dikembalikan ke pembeli\nexpired: kadaluarsa' ;

ALTER TABLE `kommu_dev`.`sales_order_store`
ADD COLUMN `total_weight` INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `shipping_status`,
ADD COLUMN `total_cost` DOUBLE UNSIGNED NOT NULL DEFAULT 0 AFTER `total_price`;

ALTER TABLE `kommu_dev`.`product_stock`
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `kommu_dev`.`city`
ADD CONSTRAINT `fk_city_province1`
  FOREIGN KEY (`province_id`)
  REFERENCES `kommu_dev`.`province` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`member`
ADD CONSTRAINT `fk_member_role1`
  FOREIGN KEY (`role_id`)
  REFERENCES `kommu_dev`.`role` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`product`
ADD CONSTRAINT `fk_product_product_category1`
  FOREIGN KEY (`product_category_id`)
  REFERENCES `kommu_dev`.`product_category` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`product_price_log`
ADD CONSTRAINT `fk_product_price_log_product1`
  FOREIGN KEY (`product_id`)
  REFERENCES `kommu_dev`.`product` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`member_address`
ADD CONSTRAINT `fk_member_address_city1`
  FOREIGN KEY (`city_id`)
  REFERENCES `kommu_dev`.`city` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_member_address_subdistrict1`
  FOREIGN KEY (`subdistrict_id`)
  REFERENCES `kommu_dev`.`subdistrict` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`role_permission`
ADD CONSTRAINT `fk_role_permission_role1`
  FOREIGN KEY (`role_id`)
  REFERENCES `kommu_dev`.`role` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_role_permission_permission1`
  FOREIGN KEY (`permission_id`)
  REFERENCES `kommu_dev`.`permission` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`store_member`
ADD CONSTRAINT `fk_store_member_role1`
  FOREIGN KEY (`role_id`)
  REFERENCES `kommu_dev`.`role` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_store_member_member1`
  FOREIGN KEY (`member_id`)
  REFERENCES `kommu_dev`.`member` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`notification`
ADD CONSTRAINT `fk_notification_member_device1`
  FOREIGN KEY (`device_id`)
  REFERENCES `kommu_dev`.`member_device` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`news`
ADD CONSTRAINT `fk_news_community1`
  FOREIGN KEY (`community_id`)
  REFERENCES `kommu_dev`.`community` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_news_member1`
  FOREIGN KEY (`author_id`)
  REFERENCES `kommu_dev`.`member` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`bank_account`
ADD CONSTRAINT `fk_bank_account_community1`
  FOREIGN KEY (`community_id`)
  REFERENCES `kommu_dev`.`community` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`subdistrict`
ADD CONSTRAINT `fk_subdistrict_city1`
  FOREIGN KEY (`city_id`)
  REFERENCES `kommu_dev`.`city` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`sales_order`
ADD CONSTRAINT `fk_order_member2`
  FOREIGN KEY (`buyer_id`)
  REFERENCES `kommu_dev`.`member` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_member1`
  FOREIGN KEY (`payment_verification_by`)
  REFERENCES `kommu_dev`.`member` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_member2`
  FOREIGN KEY (`buyer_id`)
  REFERENCES `kommu_dev`.`member` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_member3`
  FOREIGN KEY (`payment_verification_by`)
  REFERENCES `kommu_dev`.`member` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`sales_order_item`
ADD CONSTRAINT `fk_sales_order_item_product1`
  FOREIGN KEY (`product_id`)
  REFERENCES `kommu_dev`.`product` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_item_sales_order1`
  FOREIGN KEY (`sales_order_id`)
  REFERENCES `kommu_dev`.`sales_order` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_item_product2`
  FOREIGN KEY (`product_id`)
  REFERENCES `kommu_dev`.`product` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`sales_order_log`
ADD CONSTRAINT `fk_sales_order_log_sales_order1`
  FOREIGN KEY (`sales_order_id`)
  REFERENCES `kommu_dev`.`sales_order` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `kommu_dev`.`sales_order_store`
ADD CONSTRAINT `fk_sales_order_store_sales_order1`
  FOREIGN KEY (`sales_order_id`)
  REFERENCES `kommu_dev`.`sales_order` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_store_store1`
  FOREIGN KEY (`store_id`)
  REFERENCES `kommu_dev`.`store` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_store_invoice1`
  FOREIGN KEY (`invoice_id`)
  REFERENCES `kommu_dev`.`invoice` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sales_order_store_member_address1`
  FOREIGN KEY (`member_address_id`)
  REFERENCES `kommu_dev`.`member_address` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


USE `kommu_dev`;
DROP procedure IF EXISTS `kommu_dev`.`drop_fk`;

DELIMITER $$
USE `kommu_dev`$$
CREATE PROCEDURE `drop_fk`(IN tableName VARCHAR(255))
BEGIN
	DECLARE done INT DEFAULT FALSE;
    DECLARE dropCommand VARCHAR(255);
    DECLARE dropCur CURSOR FOR
        SELECT CONCAT('ALTER TABLE ', DATABASE(), '.', tableName,' DROP FOREIGN KEY ', `constraint_name`, ';')
        FROM `information_schema`.`table_constraints`
        WHERE `constraint_type` = 'FOREIGN KEY'
        AND `table_name` = tableName
        AND `table_schema` = DATABASE();

    DEClARE CONTINUE HANDLER FOR NOT FOUND SET DONE = true;

    OPEN dropCur;

    readLoop: LOOP
        FETCH dropCur INTO dropCommand;
        IF done THEN
            LEAVE readLoop;
        END IF;

        SET @sdropCommand = dropCommand;

        PREPARE dropClientUpdateKeyStmt FROM @sdropCommand;

        EXECUTE dropClientUpdateKeyStmt;

        DEALLOCATE PREPARE dropClientUpdateKeyStmt;
    END LOOP;

    CLOSE dropCur;
END$$

DELIMITER ;

USE `kommu_dev`;
DROP procedure IF EXISTS `kommu_dev`.`optimize_db`;

DELIMITER $$
USE `kommu_dev`$$
CREATE PROCEDURE `optimize_db`()
BEGIN
	DECLARE curTableFinish INT DEFAULT 0;
    DECLARE tableName VARCHAR(255);

	DECLARE curTable CURSOR FOR
		SELECT `table_name` FROM `information_schema`.`tables` WHERE `table_schema` = DATABASE();

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET curTableFinish = 1;

    OPEN curTable;

    readLoop: LOOP
		FETCH curTable INTO tableName;
        IF curTableFinish = 1 THEN
			LEAVE readLoop;
        END IF;

        CALL drop_fk(tableName);
    END LOOP readLoop;
    CLOSE curTable;
END$$

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;