ALTER TABLE phppos_customers ADD temp_id varchar(50) AFTER person_id;
ALTER TABLE phppos_customers ADD CONSTRAINT phppos_customers_temp_id UNIQUE (temp_id);
ALTER TABLE phppos_customers MODIFY person_id int(10) null;
ALTER TABLE phppos_sales MODIFY employee_id int(10) null;

DROP TABLE IF EXISTS `phppos_sales_charges`;
CREATE TABLE `phppos_sales_charges` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_id` int(11) DEFAULT NULL,
  `code` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `charge_cost` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  PRIMARY KEY (`id`),
  KEY `item_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE phppos_sales_items ADD comment varchar(255) AFTER commission;
ALTER TABLE phppos_sales_payments ADD payment_code varchar(25) AFTER payment_type;

CREATE TABLE `phppos_sales_deliveries` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `store_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `deliver_date` date NOT NULL,
  `deliver_time` time NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0.0000000000',
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `item_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `phppos_sales_riders` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `delivery_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `started_at` datetime NOT NULL,
  `ended_at` datetime DEFAULT NULL,
  `is_delivered` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) DEFAULT '0',
  `delete_user` int(11) DEFAULT NULL,
  `delete_reason` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `phppos_access_tokens` (
  `id` varchar(100) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `revoked` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `phppos_customers_address`;
CREATE TABLE `phppos_customers_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `name` varchar(115) NOT NULL,
  `street_1` varchar(250) DEFAULT NULL,
  `street_2` varchar(250) DEFAULT NULL,
  `city` varchar(150) DEFAULT NULL,
  `zip` varchar(30) DEFAULT NULL,
  `lat` varchar(200) DEFAULT NULL,
  `lng` varchar(200) DEFAULT NULL,
  `default` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for phppos_sales_details
-- ----------------------------
DROP TABLE IF EXISTS `phppos_sales_details`;
CREATE TABLE `phppos_sales_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `first_name` varchar(150) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `street_1` varchar(255) DEFAULT NULL,
  `street_2` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `lat` varchar(200) DEFAULT NULL,
  `lng` varchar(200) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `phppos_sales_logs`;
CREATE TABLE `phppos_sales_logs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `store_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0.0000000000',
  `action` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `from` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `to` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

DROP TABLE IF EXISTS `phppos_sales_notifications`;
CREATE TABLE `phppos_sales_notifications` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `person_id` int(10) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `log_id` int(11) NOT NULL,
  `is_seen` tinyint(1) NOT NULL DEFAULT '0',
  `seen_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

ALTER TABLE phppos_sales_payments MODIFY payment_code varchar(255) null;

ALTER TABLE phppos_sales ADD order_no varchar(255) null AFTER sale_id;

DROP TABLE IF EXISTS `phppos_reservations`;
CREATE TABLE `phppos_reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NULL,
  `customer_id` int(11) NOT NULL,
  `employee_id` int(11) NULL,
  `brand_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `first_name` varchar(150) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `guest_no` int(11) NOT NULL,
  `reserve_date` date NOT NULL,
  `reserve_time` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

ALTER TABLE phppos_employees ADD loyalty_card_num varchar(55) null AFTER password;
ALTER TABLE phppos_employees ADD loyalty_card_email varchar(55) null AFTER loyalty_card_num;

ALTER TABLE phppos_people ADD work_address varchar(255) null AFTER address_2; -- 01152021 update
ALTER TABLE phppos_locations ADD location_hub_id int(11) null AFTER brand_id;

DROP TABLE IF EXISTS `phppos_locations_hub`;
CREATE TABLE `phppos_locations_hub` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `phppos_customers_favorites`;
CREATE TABLE `phppos_customers_favorites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `brand_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

ALTER TABLE phppos_people ADD profile_pic MEDIUMBLOB null;

DROP TABLE IF EXISTS `phppos_promos`;
CREATE TABLE `phppos_promos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(55) DEFAULT NULL,
  `mechanics` longtext,
  `promo_value` int(10) DEFAULT NULL,
  `image_id` int(55) DEFAULT NULL,
  `date_effective` date DEFAULT NULL,
  `date_added` datetime DEFAULT NULL,
  `date_update` datetime DEFAULT NULL,
  `inactive` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `phppos_brands_category`;
CREATE TABLE `phppos_brands_category` (
  `brand_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `sequence` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`brand_id`,`category_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

ALTER TABLE phppos_people ADD birthdate DATE null AFTER last_name;

ALTER TABLE phppos_sales
ADD COLUMN sale_type  tinyint NULL AFTER total_amount,--1 if single brand 2 if multi brand
ADD COLUMN km_away  double NULL AFTER sale_type,
ADD COLUMN tip_amount  double NULL AFTER km_away;
ALTER TABLE phppos_customers_address ADD address_type VARCHAR(15) null AFTER name;
ALTER TABLE phppos_sales ADD promo_id int(55) DEFAULT NULL;
ALTER TABLE phppos_sales ADD promo_amount double(55);
ALTER TABLE phppos_items ADD COLUMN url varchar(255) NULL AFTER deleted;
ALTER TABLE phppos_items ADD COLUMN allow_negative_inventory tinyint(1) NULL AFTER url;

ALTER TABLE phppos_sales ADD ref_no varchar(255) null AFTER promo_amount;

ALTER TABLE phppos_sales_details ADD loyalty_card_num varchar(100) null AFTER email;
ALTER TABLE phppos_sales_details ADD loyalty_card_email varchar(100) null AFTER loyalty_card_num;
ALTER TABLE phppos_sales_details ADD loyalty_card_tier varchar(100) null AFTER loyalty_card_email;
ALTER TABLE phppos_employees ADD loyalty_card_tier varchar(100) null AFTER loyalty_card_email;
ALTER TABLE phppos_user_logs ADD platform varchar(100) null AFTER logs;
ALTER TABLE phppos_user_logs ADD specific_platform varchar(100) null AFTER platform;

ALTER TABLE phppos_sales_details ADD billing_address varchar(255) null ;
ALTER TABLE phppos_sales_details ADD billing_city varchar(255) null ;
ALTER TABLE phppos_sales_details ADD billing_country varchar(255) null ;

ALTER TABLE phppos_sales_items ADD COLUMN is_promo_item tinyint(1) NULL;

ALTER TABLE phppos_customers_address ADD COLUMN street1 varchar(255) NULL AFTER street_2;
ALTER TABLE phppos_customers_address ADD COLUMN street2 varchar(255) NULL AFTER street1;
ALTER TABLE phppos_customers_address ADD COLUMN city_solo varchar(150) NULL AFTER city;
ALTER TABLE phppos_customers_address ADD COLUMN sublocality varchar(255) NULL AFTER city_solo;
ALTER TABLE phppos_customers_address ADD COLUMN administrative_area varchar(255) NULL AFTER sublocality;
ALTER TABLE phppos_employees ADD COLUMN is_verified TINYINT(1) NULL AFTER person_id;

ALTER TABLE phppos_user_logs ADD platform varchar(100) null AFTER logs;
ALTER TABLE phppos_user_logs ADD location varchar(100) null AFTER platform;
ALTER TABLE phppos_user_logs ADD country varchar(100) null AFTER location;
ALTER TABLE phppos_user_logs ADD method varchar(100) null AFTER country;

ALTER TABLE phppos_user_logs ADD brands varchar(100) null AFTER method;
ALTER TABLE phppos_user_logs ADD shops varchar(100) null AFTER brands;


ALTER TABLE phppos_user_logs ADD carts_created TINYINT(1) null AFTER shops;
ALTER TABLE phppos_user_logs ADD checkouts TINYINT(1) null AFTER carts_created;
ALTER TABLE phppos_user_logs ADD referrer varchar(100) null AFTER checkouts;

ALTER TABLE phppos_customers_address ADD COLUMN actual_address varchar(255) NULL AFTER name;
ALTER TABLE phppos_sales_details ADD actual_address varchar(255) null AFTER billing_address ;

ALTER TABLE phppos_locations ADD paymongo_secret varchar(150) null;
ALTER TABLE phppos_locations ADD paymongo_key varchar(150) null;

ALTER TABLE phppos_locations ADD paymaya_secret varchar(150) null;
ALTER TABLE phppos_locations ADD paymaya_key varchar(150) null;