DROP TABLE IF EXISTS `user_profile`;
CREATE TABLE `user_profile` (
    `cognito_user_id` VARCHAR(36) NOT NULL,
    `first_name` VARCHAR(255) DEFAULT NULL,
    `last_name` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`cognito_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `individual_type`;
CREATE TABLE `individual_type` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_individual_type` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `company_type`;
CREATE TABLE `company_type` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_company_type` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) DEFAULT NULL,
    `email_alt` VARCHAR(255) DEFAULT NULL,
    `phone_number` VARCHAR(255) DEFAULT NULL,
    `phone_number_alt` VARCHAR(255) DEFAULT NULL,
    `practice_number` VARCHAR(255) DEFAULT NULL,
    `vat_registration_number` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `company_type_id` INT(10) unsigned DEFAULT NULL,
    `primary_contact_id` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `company_type_id` (`company_type_id`),
    KEY `primary_contact_id` (`primary_contact_id`),
    CONSTRAINT `company_ibfk_company_type` FOREIGN KEY (`company_type_id`) REFERENCES `company_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `individual`;
CREATE TABLE `individual` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `profile_number` VARCHAR(255) NOT NULL,
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    `initials` VARCHAR(255) DEFAULT NULL,
    `gender` VARCHAR(255) DEFAULT NULL,
    `title` VARCHAR(255) DEFAULT NULL,
    `dependant_number` VARCHAR(255) DEFAULT NULL,
    `date_of_birth` DATE NOT NULL,
    `is_animal` TINYINT(1) NOT NULL DEFAULT 0,
    `phone_number` VARCHAR(255) DEFAULT NULL,
    `phone_number_alt` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `company_id` INT(10) unsigned DEFAULT NULL,
    `individual_type_id` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_profile_number` (`profile_number`) USING BTREE,
    KEY `company_id` (`company_id`),
    KEY `individual_type_id` (`individual_type_id`),
    CONSTRAINT `individual_ibfk_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
    CONSTRAINT `individual_ibfk_individual_type` FOREIGN KEY (`individual_type_id`) REFERENCES `individual_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `building_apt_number` VARCHAR(255) NOT NULL,
    `address_1` VARCHAR(255) NOT NULL,
    `address_2` VARCHAR(255) NOT NULL,
    `province` VARCHAR(255) DEFAULT NULL,
    `postcode` VARCHAR(10) DEFAULT NULL,
    `country` VARCHAR(255) DEFAULT NULL,
    `individual_id` INT(10) unsigned DEFAULT NULL,
    `company_id` INT(10) unsigned DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `company_id` (`company_id`),
    KEY `individual_id` (`individual_id`),
    CONSTRAINT `address_ibfk_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
    CONSTRAINT `address_ibfk_individual` FOREIGN KEY (`individual_id`) REFERENCES `individual` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `order_status`;
CREATE TABLE `order_status` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `status` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_order_status` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `order_item_status`;
CREATE TABLE `order_item_status` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `status` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_order_item_status` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `batch_status`;
CREATE TABLE `batch_status` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `status` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_batch_status` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `delivery_type`;
CREATE TABLE `delivery_type` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_delivery_type` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `shipping_details`;
CREATE TABLE `shipping_details` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `waybill_number` VARCHAR(255) DEFAULT NULL,
    `address_id` INT(10) unsigned NOT NULL,
    `delivery_type_id` INT(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `address_id` (`address_id`),
    KEY `delivery_type_id` (`delivery_type_id`),
    CONSTRAINT `shipping_details_ibfk_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`),
    CONSTRAINT `shipping_details_ibfk_delivery_type` FOREIGN KEY (`delivery_type_id`) REFERENCES `delivery_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `barcode` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `order_status_id` INT(10) unsigned NOT NULL,
    `client_id` INT(10) unsigned DEFAULT NULL,
    `doctor_id` INT(10) unsigned DEFAULT NULL,
    `patient_id` INT(10) unsigned DEFAULT NULL,
    `shipping_details_id` INT(10) unsigned NOT NULL,
    `parent_order_id` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `order_status_id` (`order_status_id`),
    KEY `client_id` (`client_id`),
    KEY `doctor_id` (`doctor_id`),
    KEY `patient_id` (`patient_id`),
    KEY `shipping_details_id` (`shipping_details_id`),
    KEY `parent_order_id` (`parent_order_id`),
    CONSTRAINT `order_ibfk_order_status` FOREIGN KEY (`order_status_id`) REFERENCES `order_status` (`id`),
    CONSTRAINT `order_ibfk_client` FOREIGN KEY (`client_id`) REFERENCES `company` (`id`),
    CONSTRAINT `order_ibfk_doctor` FOREIGN KEY (`doctor_id`) REFERENCES `individual` (`id`),
    CONSTRAINT `order_ibfk_patient` FOREIGN KEY (`patient_id`) REFERENCES `individual` (`id`),
    CONSTRAINT `order_ibfk_shipping_details` FOREIGN KEY (`shipping_details_id`) REFERENCES `shipping_details` (`id`),
    CONSTRAINT `order_ibfk_parent_order` FOREIGN KEY (`parent_order_id`) REFERENCES `order` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `dosage_unit`;
CREATE TABLE `dosage_unit` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `unit` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `symbol` VARCHAR(5) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_dosage_unit` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `dosage_type`;
CREATE TABLE `dosage_type` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `dosage_unit_id` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_dosage_type` (`code`) USING BTREE,
    CONSTRAINT `dosage_type_ibfk_dosage_unit` FOREIGN KEY (`dosage_unit_id`) REFERENCES `dosage_unit` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medicine`;
CREATE TABLE `medicine` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `barcode_prefix` VARCHAR(255) NOT NULL,
    `active_ingredient` VARCHAR(255) NOT NULL,
    `active_ingredient_nappy_code` VARCHAR(255) NOT NULL,
    `schedule_status` INT(1) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_barcode_prefix_medicine` (`barcode_prefix`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medicine_dosage_type`;
CREATE TABLE `medicine_dosage_type` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `default_price` INT(10) NOT NULL,
    `additional_information` TEXT DEFAULT NULL,
    `usage_instructions` TEXT DEFAULT NULL,
    `storage_directions` TEXT DEFAULT NULL,
    `special_instructions` TEXT DEFAULT NULL,
    `dosage_strength` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `medicine_id` INT(10) unsigned NOT NULL,
    `dosage_type_id` INT(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `medicine_dosage_type_ibfk_medicine` FOREIGN KEY (`medicine_id`) REFERENCES `medicine` (`id`),
    CONSTRAINT `medicine_dosage_type_ibfk_dosage_type` FOREIGN KEY (`dosage_type_id`) REFERENCES `dosage_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `package_type`;
CREATE TABLE `package_type` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NOT NULL,
    `code` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_code_package_type` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `medicine_dosage_package_type`;
CREATE TABLE `medicine_dosage_package_type` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `contained_quantity` INT(10) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `medicine_dosage_type_id` INT(10) unsigned NOT NULL,
    `package_type_id` INT(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `medicine_dosage_package_type_ibfk_medicine_dosage_type` FOREIGN KEY (`medicine_dosage_type_id`) REFERENCES `medicine_dosage_type` (`id`),
    CONSTRAINT `medicine_dosage_package_type_ibfk_package_type` FOREIGN KEY (`package_type_id`) REFERENCES `package_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `batch`;
CREATE TABLE `batch` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `quantity_produced` INT(10) NOT NULL,
    `batch_number` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `batch_status_id` INT(10) unsigned NOT NULL,
    `medicine_dosage_type_id` INT(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_batch_number` (`batch_number`) USING BTREE,
    CONSTRAINT `batch_ibfk_batch_status` FOREIGN KEY (`batch_status_id`) REFERENCES `batch_status` (`id`),
    CONSTRAINT `batch_ibfk_medicine_dosage_type` FOREIGN KEY (`medicine_dosage_type_id`) REFERENCES `medicine_dosage_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `order_item`;
CREATE TABLE `order_item` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `total_quantity` INT(10) NOT NULL,
    `price_per_item` INT(10) NOT NULL,
    `order_item_status_id` INT(10) unsigned NOT NULL,
    `order_id` INT(10) unsigned DEFAULT NULL,
    `medicine_dosage_package_type_id` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `order_item_status_id` (`order_item_status_id`),
    KEY `order_id` (`order_id`),
    KEY `medicine_dosage_package_type_id` (`medicine_dosage_package_type_id`),
    CONSTRAINT `order_item_ibfk_order_item_status` FOREIGN KEY (`order_item_status_id`) REFERENCES `order_item_status` (`id`),
    CONSTRAINT `order_item_ibfk_order` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`),
    CONSTRAINT `order_item_ibfk_medicine_dosage_package_type` FOREIGN KEY (`medicine_dosage_package_type_id`) REFERENCES `medicine_dosage_package_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `batch_item`;
CREATE TABLE `batch_item` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `barcode` VARCHAR(255) DEFAULT NULL,
    `in_stock` TINYINT(1) NOT NULL DEFAULT '1',
    `order_item_id` INT(10) unsigned DEFAULT NULL,
    `batch_id` INT(10) unsigned DEFAULT NULL,
    `medicine_dosage_package_type_id` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_barcode` (`barcode`) USING BTREE,
    KEY `order_item_id` (`order_item_id`),
    KEY `batch_id` (`batch_id`),
    CONSTRAINT `batch_item_ibfk_order_item` FOREIGN KEY (`order_item_id`) REFERENCES `order_item` (`id`),
    CONSTRAINT `batch_item_ibfk_batch` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`id`),
    CONSTRAINT `batch_item_ibfk_medicine_dosage_package_type` FOREIGN KEY (`medicine_dosage_package_type_id`) REFERENCES `medicine_dosage_package_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
    `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
    `entity_id` INT(10) unsigned NOT NULL,
    `entity` VARCHAR(255) NOT NULL,
    `log_type` VARCHAR(255) NOT NULL,
    `old_value` TEXT DEFAULT NULL,
    `new_value` TEXT DEFAULT NULL,
    `user_id` VARCHAR(255) NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
