-- Test schema for optica_smoketest (auto-generated from optica_local)
-- DO NOT EDIT: regenerate with tests/scripts/gen_schema.php

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `asides`;
CREATE TABLE `asides` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL,
  `patient` int unsigned DEFAULT NULL,
  `financing` decimal(10,2) unsigned NOT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `asides_patient_foreign` (`patient`),
  KEY `asides_created_by_foreign` (`created_by`),
  KEY `asides_sale_foreign` (`sale`),
  CONSTRAINT `asides_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `asides_patient_foreign` FOREIGN KEY (`patient`) REFERENCES `patients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `asides_sale_foreign` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `auth_groups_users`;
CREATE TABLE `auth_groups_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `group` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `auth_groups_users_user_id_foreign` (`user_id`),
  CONSTRAINT `auth_groups_users_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `auth_identities`;
CREATE TABLE `auth_identities` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `secret` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `secret2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `expires` datetime DEFAULT NULL,
  `extra` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `force_reset` tinyint(1) NOT NULL DEFAULT '0',
  `last_used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `type_secret` (`type`,`secret`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `auth_identities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `auth_logins`;
CREATE TABLE `auth_logins` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `date` datetime NOT NULL,
  `success` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_type_identifier` (`id_type`,`identifier`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `auth_permissions_users`;
CREATE TABLE `auth_permissions_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `permission` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `auth_permissions_users_user_id_foreign` (`user_id`),
  CONSTRAINT `auth_permissions_users_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `auth_remember_tokens`;
CREATE TABLE `auth_remember_tokens` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `selector` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `hashedValidator` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int unsigned NOT NULL,
  `expires` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `selector` (`selector`),
  KEY `auth_remember_tokens_user_id_foreign` (`user_id`),
  CONSTRAINT `auth_remember_tokens_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `auth_token_logins`;
CREATE TABLE `auth_token_logins` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `date` datetime NOT NULL,
  `success` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_type_identifier` (`id_type`,`identifier`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `bank_accounts`;
CREATE TABLE `bank_accounts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `bank_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `alias` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `account_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `clabe` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `owner_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `store` int unsigned DEFAULT NULL,
  `currency` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bank_accounts_store_foreign` (`store`),
  CONSTRAINT `bank_accounts_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `bank_commissions`;
CREATE TABLE `bank_commissions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `bank` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `payment_method` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `percentage` decimal(4,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `brands`;
CREATE TABLE `brands` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `cash_out_payments`;
CREATE TABLE `cash_out_payments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `cash_out` int unsigned NOT NULL,
  `payment` int unsigned NOT NULL,
  `amount` decimal(11,2) NOT NULL COMMENT 'Monto del pago al momento del corte',
  `payment_type` enum('cash','card','transfer') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Método de pago al momento del corte',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cash_out_payments_payment` (`payment`),
  UNIQUE KEY `uq_cash_out_payments_cash_out_payment` (`cash_out`,`payment`),
  CONSTRAINT `cash_out_payments_cash_out_foreign` FOREIGN KEY (`cash_out`) REFERENCES `cash_outs` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `cash_out_payments_payment_foreign` FOREIGN KEY (`payment`) REFERENCES `payments` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `cash_out_incomes`;
CREATE TABLE `cash_out_incomes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `cash_out` int unsigned NOT NULL,
  `income` int NOT NULL,
  `amount` decimal(11,2) NOT NULL COMMENT 'Monto del ingreso al momento del corte',
  `payment_type` enum('cash','card','transfer') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Método del ingreso al momento del corte',
  `concept` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Concepto del ingreso al momento del corte',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cash_out_incomes_income` (`income`),
  UNIQUE KEY `uq_cash_out_incomes_cash_out_income` (`cash_out`,`income`),
  CONSTRAINT `cash_out_incomes_cash_out_foreign` FOREIGN KEY (`cash_out`) REFERENCES `cash_outs` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `cash_out_incomes_income_foreign` FOREIGN KEY (`income`) REFERENCES `incomes` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `cash_outs`;
CREATE TABLE `cash_outs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user` int unsigned NOT NULL COMMENT 'Usuario que cierra el corte',
  `store` int unsigned NOT NULL COMMENT 'Tienda del corte',
  `date` date NOT NULL COMMENT 'Fecha del corte',
  `sales_count` int unsigned NOT NULL DEFAULT '0' COMMENT 'Cantidad de ventas creadas durante el corte',
  `total_sales` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total de ventas del día',
  `total_cash` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total en efectivo',
  `total_card` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total en tarjeta',
  `total_transfer` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total en transferencias',
  `income_count` int unsigned NOT NULL DEFAULT '0' COMMENT 'Cantidad de ingresos independientes incluidos',
  `total_income` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT 'Total de ingresos independientes',
  `income_cash` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT 'Ingresos independientes en efectivo',
  `income_card` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT 'Ingresos independientes con tarjeta',
  `income_transfer` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT 'Ingresos independientes por transferencia',
  `expected_cash` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT 'Efectivo pendiente esperado al momento de cerrar',
  `cash_in_drawer` decimal(11,2) DEFAULT NULL COMMENT 'Efectivo físico declarado por el usuario',
  `difference` decimal(11,2) DEFAULT NULL COMMENT 'Efectivo declarado menos efectivo esperado',
  `cash_deposited` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Efectivo depositado',
  `bank_account` int unsigned DEFAULT NULL COMMENT 'Cuenta bancaria donde se depositó',
  `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Notas del corte',
  `status` enum('open','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'open' COMMENT 'Estado del corte: open | closed',
  `closed_at` datetime DEFAULT NULL COMMENT 'Fecha de cierre del corte',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cash_outs_user_store_date` (`user`,`store`,`date`),
  KEY `idx_cash_outs_store_date_status` (`store`,`date`,`status`),
  KEY `cash_outs_user_foreign` (`user`),
  KEY `cash_outs_store_foreign` (`store`),
  KEY `cash_outs_bank_account_foreign` (`bank_account`),
  CONSTRAINT `cash_outs_bank_account_foreign` FOREIGN KEY (`bank_account`) REFERENCES `bank_accounts` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `cash_outs_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `cash_outs_user_foreign` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `colors`;
CREATE TABLE `colors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `commission_rules`;
CREATE TABLE `commission_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `product_line` int unsigned NOT NULL,
  `payment_type` enum('cash','card','transfer') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `percentage` decimal(5,2) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `commission_rules_product_line_foreign` (`product_line`),
  CONSTRAINT `commission_rules_product_line_foreign` FOREIGN KEY (`product_line`) REFERENCES `lines` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `commissions`;
CREATE TABLE `commissions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `payment` int unsigned NOT NULL,
  `seller_user` int unsigned NOT NULL,
  `product_line` int unsigned DEFAULT NULL,
  `base_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `commission_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status` enum('pending','paid','excluded') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
  `excluded_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `commission_month` date NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `commissions_payment_foreign` (`payment`),
  KEY `commissions_seller_user_foreign` (`seller_user`),
  KEY `commissions_product_line_foreign` (`product_line`),
  CONSTRAINT `commissions_payment_foreign` FOREIGN KEY (`payment`) REFERENCES `payments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `commissions_product_line_foreign` FOREIGN KEY (`product_line`) REFERENCES `lines` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `commissions_seller_user_foreign` FOREIGN KEY (`seller_user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `companies`;
CREATE TABLE `companies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `type` enum('lab','contract','supplier') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `contact` int unsigned DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `companies_contact_foreign` (`contact`),
  CONSTRAINT `companies_contact_foreign` FOREIGN KEY (`contact`) REFERENCES `persons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `consultation_contact_lenses`;
CREATE TABLE `consultation_contact_lenses` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `consultation` int unsigned DEFAULT NULL,
  `patient` int unsigned DEFAULT NULL,
  `right_keratometry_a` decimal(6,3) unsigned DEFAULT NULL,
  `left_keratometry_a` decimal(6,3) unsigned DEFAULT NULL,
  `right_keratometry_b` decimal(6,3) unsigned DEFAULT NULL,
  `left_keratometry_b` decimal(6,3) unsigned DEFAULT NULL,
  `eyelid_opening` int unsigned DEFAULT NULL,
  `cornea_diameter` decimal(4,2) unsigned DEFAULT NULL,
  `pupil_diameter` decimal(4,2) unsigned DEFAULT NULL,
  `tear_breakup_time` int unsigned DEFAULT NULL,
  `right_base` decimal(4,2) unsigned DEFAULT NULL,
  `left_base` decimal(4,2) unsigned DEFAULT NULL,
  `right_diameter` int unsigned DEFAULT NULL,
  `left_diameter` int unsigned DEFAULT NULL,
  `final_right_sphere` decimal(4,2) DEFAULT NULL,
  `final_left_sphere` decimal(4,2) DEFAULT NULL,
  `final_right_cylinder` decimal(4,2) DEFAULT NULL,
  `final_left_cylinder` decimal(4,2) DEFAULT NULL,
  `final_right_axis` int unsigned DEFAULT NULL,
  `final_left_axis` int unsigned DEFAULT NULL,
  `right_keratometry_axis` int unsigned DEFAULT NULL,
  `left_keratometry_axis` int unsigned DEFAULT NULL,
  `over_right_sphere` decimal(4,2) DEFAULT NULL,
  `over_left_sphere` decimal(4,2) DEFAULT NULL,
  `over_right_cylinder` decimal(4,2) DEFAULT NULL,
  `over_left_cylinder` decimal(4,2) DEFAULT NULL,
  `right_thickness` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `left_thickness` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `right_cpp` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `left_cpp` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_right_acuity_before` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_right_acuity_after` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_left_acuity_before` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `contact_left_acuity_after` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `suggested` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `consultation_contact_lenses_consultation_foreign` (`consultation`),
  CONSTRAINT `consultation_contact_lenses_consultation_foreign` FOREIGN KEY (`consultation`) REFERENCES `consultations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `consultation_general_background`;
CREATE TABLE `consultation_general_background` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `consultation` int unsigned DEFAULT NULL,
  `healthy` tinyint(1) NOT NULL DEFAULT '1',
  `diabetes` tinyint(1) DEFAULT NULL,
  `last_glucose_date` date DEFAULT NULL,
  `glucose_date_number` int DEFAULT NULL,
  `glucose_date_unit` enum('days','weeks','months','years') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `glucose_level` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `hypertensive` tinyint(1) DEFAULT NULL,
  `last_blood_pressure_date` date DEFAULT NULL,
  `blood_date_number` int DEFAULT NULL,
  `blood_date_unit` enum('days','weeks','months','years') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `blood_pressure_level` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `observations` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`),
  KEY `consultation_general_background_consultation_foreign` (`consultation`),
  CONSTRAINT `consultation_general_background_consultation_foreign` FOREIGN KEY (`consultation`) REFERENCES `consultations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `consultation_visual_background`;
CREATE TABLE `consultation_visual_background` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `consultation` int unsigned DEFAULT NULL,
  `glasses` tinyint(1) DEFAULT NULL,
  `last_check_date` date DEFAULT NULL,
  `fatigue` tinyint(1) DEFAULT NULL,
  `burning` tinyint(1) DEFAULT NULL,
  `itching` tinyint(1) DEFAULT NULL,
  `photophobia` tinyint(1) DEFAULT NULL,
  `redness` tinyint(1) DEFAULT NULL,
  `blurry` set('close','far','double') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `headache` set('frontal','parietal','temporal','occipital') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `secretion` enum('white','transparent','yellow','green') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `other_conditions` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`),
  KEY `consultation_visual_background_consultation_foreign` (`consultation`),
  CONSTRAINT `consultation_visual_background_consultation_foreign` FOREIGN KEY (`consultation`) REFERENCES `consultations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `consultation_visual_evaluation`;
CREATE TABLE `consultation_visual_evaluation` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `consultation` int unsigned DEFAULT NULL,
  `oftalmoscopy` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `others` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `right_wafer_height` int DEFAULT NULL,
  `left_wafer_height` int DEFAULT NULL,
  `anomalies_image` blob,
  `anterior_segment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `cover_test` enum('ortoforia','exoforia','exotropia','endorforia','endotropia','hipertopria','hipotropia','cicloforia') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `posterior_segment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `chromatic_vision` enum('normal','red','green','blue') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ease_accommodation` int unsigned DEFAULT NULL,
  `brock_string` enum('x','exo','endo') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `stereotest` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `worth_bridge` enum('fusion','right','left') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `convergence_break` int unsigned DEFAULT NULL,
  `convergence_recover` int unsigned DEFAULT NULL,
  `amsler_grid` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `left_acuity_before` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `right_acuity_before` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `left_acuity_after` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `right_acuity_after` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `left_capacity` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `right_capacity` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `acuity_ocular` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `interpupilar_distance` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`),
  KEY `consultation_visual_evaluation_consultation_foreign` (`consultation`),
  CONSTRAINT `consultation_visual_evaluation_consultation_foreign` FOREIGN KEY (`consultation`) REFERENCES `consultations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `consultations`;
CREATE TABLE `consultations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `patient` int unsigned DEFAULT NULL,
  `sale` int unsigned DEFAULT NULL,
  `attended_by` int unsigned DEFAULT NULL,
  `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `consultations_patient_foreign` (`patient`),
  KEY `consultations_attended_by_foreign` (`attended_by`),
  KEY `consultations_sale_index` (`sale` DESC),
  KEY `consultations_sale_fk` (`sale`),
  CONSTRAINT `consultations_attended_by_foreign` FOREIGN KEY (`attended_by`) REFERENCES `doctors` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `consultations_patient_foreign` FOREIGN KEY (`patient`) REFERENCES `patients` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `consultations_sale_fk` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `contact_brands`;
CREATE TABLE `contact_brands` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `contact_colors`;
CREATE TABLE `contact_colors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `contact_designs`;
CREATE TABLE `contact_designs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `contact_wears`;
CREATE TABLE `contact_wears` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `item` int unsigned DEFAULT NULL,
  `design` int unsigned DEFAULT NULL,
  `wear` int unsigned DEFAULT NULL,
  `units_per_package` int unsigned DEFAULT '1',
  `color` int unsigned DEFAULT NULL,
  `replace_unit` int unsigned DEFAULT NULL,
  `replace_time` enum('days','weeks','months','years') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contacts_item_foreign` (`item`),
  KEY `contacts_design_foreign` (`design`),
  KEY `contacts_wear_foreign` (`wear`),
  KEY `contacts_color_foreign` (`color`),
  CONSTRAINT `contacts_color_foreign` FOREIGN KEY (`color`) REFERENCES `contact_colors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `contacts_design_foreign` FOREIGN KEY (`design`) REFERENCES `contact_designs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `contacts_item_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `contacts_wear_foreign` FOREIGN KEY (`wear`) REFERENCES `contact_wears` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `contracts`;
CREATE TABLE `contracts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contracts_company_foreign` (`company`),
  CONSTRAINT `contracts_company_foreign` FOREIGN KEY (`company`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `credits`;
CREATE TABLE `credits` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL,
  `patient` int unsigned DEFAULT NULL,
  `financing` decimal(10,2) NOT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `credits_sale_foreign` (`sale`),
  KEY `credits_patient_foreign` (`patient`),
  KEY `credits_created_by_foreign` (`created_by`),
  CONSTRAINT `credits_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `credits_patient_foreign` FOREIGN KEY (`patient`) REFERENCES `patients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `credits_sale_foreign` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `discounts`;
CREATE TABLE `discounts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL,
  `concept` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `percentage` int NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `discounts_sale_foreign` (`sale`),
  CONSTRAINT `discounts_sale_foreign` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `doctors`;
CREATE TABLE `doctors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `person` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `doctors_person_foreign` (`person`),
  CONSTRAINT `doctors_person_foreign` FOREIGN KEY (`person`) REFERENCES `persons` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `person` int unsigned DEFAULT NULL,
  `user` int unsigned DEFAULT NULL,
  `store` int unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employees_person_foreign` (`person`),
  KEY `employees_user_foreign` (`user`),
  KEY `employees_store_foreign` (`store`),
  CONSTRAINT `employees_person_foreign` FOREIGN KEY (`person`) REFERENCES `persons` (`id`),
  CONSTRAINT `employees_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`),
  CONSTRAINT `employees_user_foreign` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `concept` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `amount` decimal(7,2) NOT NULL,
  `responsible` int unsigned DEFAULT NULL,
  `category` int NOT NULL,
  `created_by` int NOT NULL,
  `comments` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `applied` datetime DEFAULT NULL,
  `reconciled` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `expenses_category_foreign` (`category`),
  KEY `expenses_responsible_foreign` (`responsible`),
  CONSTRAINT `expenses_category_foreign` FOREIGN KEY (`category`) REFERENCES `expenses_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `expenses_responsible_foreign` FOREIGN KEY (`responsible`) REFERENCES `users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `expenses_category`;
CREATE TABLE `expenses_category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `description` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `frame_colors`;
CREATE TABLE `frame_colors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `frame_materials`;
CREATE TABLE `frame_materials` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `frame_photos`;
CREATE TABLE `frame_photos` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `frame` int unsigned DEFAULT NULL,
  `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `frame_photos_frame_foreign` (`frame`),
  KEY `frame_photos_created_by_foreign` (`created_by`),
  CONSTRAINT `frame_photos_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `frame_photos_frame_foreign` FOREIGN KEY (`frame`) REFERENCES `frames` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `frames`;
CREATE TABLE `frames` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `item` int unsigned DEFAULT NULL,
  `model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `solar` tinyint(1) DEFAULT '0',
  `color_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `color` int unsigned DEFAULT NULL,
  `size` int DEFAULT NULL,
  `proportions` json DEFAULT NULL,
  `style` enum('full','slotted','3 pieces') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `gender` enum('f','m','u') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'm',
  `frame_material` int unsigned DEFAULT NULL,
  `rod_material` int unsigned DEFAULT NULL,
  `frame_color` int unsigned DEFAULT NULL,
  `rod_color` int unsigned DEFAULT NULL,
  `shape` enum('square','rectangle','geometric','round') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `published` tinyint DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `frames_item_foreign` (`item`),
  KEY `frames_frame_color_foreign` (`frame_color`),
  KEY `frames_frame_material_foreign` (`frame_material`),
  KEY `frames_rod_material_foreign` (`rod_material`),
  KEY `frames_rod_color_foreign` (`rod_color`),
  CONSTRAINT `frames_frame_color_foreign` FOREIGN KEY (`frame_color`) REFERENCES `frame_colors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `frames_frame_material_foreign` FOREIGN KEY (`frame_material`) REFERENCES `frame_materials` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `frames_item_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `frames_rod_color_foreign` FOREIGN KEY (`rod_color`) REFERENCES `frame_colors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `frames_rod_material_foreign` FOREIGN KEY (`rod_material`) REFERENCES `frame_materials` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `incomes`;
CREATE TABLE `incomes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `payment` int unsigned DEFAULT NULL COMMENT 'Referencia histórica; no usar para ingresos nuevos',
  `store` int unsigned DEFAULT NULL COMMENT 'Sucursal donde se recibió el ingreso',
  `payment_type` enum('cash','card','transfer') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Método con el que se recibió el ingreso',
  `account` int unsigned DEFAULT NULL COMMENT 'Cuenta bancaria receptora',
  `terminal` int unsigned DEFAULT NULL COMMENT 'Terminal usada para ingreso con tarjeta',
  `status` enum('pending','paid','deposited') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Estado de recepción o depósito del ingreso',
  `received_at` datetime DEFAULT NULL COMMENT 'Fecha de recepción o depósito confirmado',
  `amount` decimal(10,2) NOT NULL,
  `concept` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `comments` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `applied` datetime DEFAULT NULL,
  `responsible` int unsigned DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_incomes_cash_out_lookup` (`store`,`created_by`,`applied`,`payment_type`,`status`),
  KEY `incomes_payment_legacy_foreign` (`payment`),
  KEY `incomes_account_foreign` (`account`),
  KEY `incomes_terminal_foreign` (`terminal`),
  KEY `incomes_responsible_foreign` (`responsible`),
  KEY `incomes_created_by_foreign` (`created_by`),
  CONSTRAINT `incomes_payment_legacy_foreign` FOREIGN KEY (`payment`) REFERENCES `payments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `incomes_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `incomes_account_foreign` FOREIGN KEY (`account`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `incomes_terminal_foreign` FOREIGN KEY (`terminal`) REFERENCES `payment_cards` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `incomes_responsible_foreign` FOREIGN KEY (`responsible`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `incomes_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `inventories`;
CREATE TABLE `inventories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `item` int unsigned DEFAULT NULL,
  `cost` decimal(10,2) DEFAULT NULL,
  `supplier` int unsigned DEFAULT NULL,
  `store` int unsigned DEFAULT NULL,
  `stock` int NOT NULL,
  `purchase` int unsigned DEFAULT NULL,
  `bill` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `enter_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inventories_item_foreign` (`item`),
  KEY `inventories_supplier_foreign` (`supplier`),
  KEY `inventories_store_foreign` (`store`),
  KEY `inventories_purchase_foreign` (`purchase`),
  CONSTRAINT `inventories_item_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `inventories_purchase_foreign` FOREIGN KEY (`purchase`) REFERENCES `purchases` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `inventories_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `inventories_supplier_foreign` FOREIGN KEY (`supplier`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `invoice_payments`;
CREATE TABLE `invoice_payments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `invoice` int unsigned NOT NULL,
  `payment` int unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_payments_invoice_foreign` (`invoice`),
  KEY `invoice_payments_payment_foreign` (`payment`),
  CONSTRAINT `invoice_payments_invoice_foreign` FOREIGN KEY (`invoice`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `invoice_payments_payment_foreign` FOREIGN KEY (`payment`) REFERENCES `payments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL COMMENT 'Venta facturada',
  `person_tax` int unsigned DEFAULT NULL COMMENT 'Datos fiscales elegidos para timbrar',
  `series` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `folio` int DEFAULT NULL,
  `subtotal` decimal(12,2) DEFAULT NULL,
  `discount` decimal(12,2) DEFAULT '0.00',
  `tax_rate` decimal(5,2) DEFAULT NULL COMMENT 'porcentaje, ej. 16.00',
  `tax_amount` decimal(12,2) DEFAULT NULL,
  `total` decimal(12,2) DEFAULT NULL,
  `currency` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'MXN',
  `status` enum('draft','issued','canceled') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
  `uuid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'UUID del timbrado',
  `xml_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pdf_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `issued_at` datetime DEFAULT NULL,
  `canceled_at` datetime DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `series_folio` (`series`,`folio`),
  KEY `invoices_sale_foreign` (`sale`),
  KEY `invoices_person_tax_foreign` (`person_tax`),
  KEY `invoices_created_by_foreign` (`created_by`),
  CONSTRAINT `invoices_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `invoices_person_tax_foreign` FOREIGN KEY (`person_tax`) REFERENCES `person_taxes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `invoices_sale_foreign` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `item_prices`;
CREATE TABLE `item_prices` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `item` int unsigned NOT NULL,
  `type` enum('regular','promotion','discount','outlet') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'regular',
  `amount` decimal(10,2) NOT NULL,
  `shipping_cost` decimal(10,2) DEFAULT NULL,
  `starts_at` datetime DEFAULT NULL,
  `ends_at` datetime DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `item_prices_created_by_foreign` (`created_by`),
  KEY `idx_item_prices_type` (`type`),
  KEY `idx_item_prices_date_range` (`starts_at`,`ends_at`),
  KEY `idx_item_prices_item_dates` (`item`,`starts_at`,`ends_at`),
  KEY `idx_item_prices_deleted_at` (`deleted_at`),
  CONSTRAINT `item_prices_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `item_prices_item_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `cost` decimal(10,2) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT '0.00',
  `supplier` int unsigned DEFAULT NULL,
  `line` int unsigned DEFAULT NULL,
  `brand` int unsigned DEFAULT NULL,
  `size` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `proportions` json DEFAULT NULL,
  `stockable` tinyint(1) DEFAULT '1',
  `stock_min` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `items_line_foreign` (`line`),
  KEY `items_brand_foreign` (`brand`),
  CONSTRAINT `items_brand_foreign` FOREIGN KEY (`brand`) REFERENCES `brands` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `items_line_foreign` FOREIGN KEY (`line`) REFERENCES `lines` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `labs`;
CREATE TABLE `labs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company` int unsigned DEFAULT NULL,
  `is_foreign` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `labs_company_foreign` (`company`),
  CONSTRAINT `labs_company_foreign` FOREIGN KEY (`company`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lens_colors`;
CREATE TABLE `lens_colors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_by` int unsigned NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lens_correction_types`;
CREATE TABLE `lens_correction_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lens_designs`;
CREATE TABLE `lens_designs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_by` int unsigned NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lens_materials`;
CREATE TABLE `lens_materials` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_by` int unsigned NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lens_treatments`;
CREATE TABLE `lens_treatments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_by` int unsigned NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lens_treatments_created_by_foreign` (`created_by`),
  CONSTRAINT `lens_treatments_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lens_types`;
CREATE TABLE `lens_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_by` int unsigned NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lenses`;
CREATE TABLE `lenses` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `item` int unsigned DEFAULT NULL,
  `design` int unsigned DEFAULT NULL,
  `category_sphere` tinyint DEFAULT NULL,
  `range_sphere` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `category_cylinder` tinyint DEFAULT NULL,
  `range_cylinder` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `max_graduation` tinyint DEFAULT NULL,
  `over_processed` enum('T','P') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `arrival_days` tinyint NOT NULL,
  `frame_type` tinyint DEFAULT NULL,
  `lab` int unsigned DEFAULT NULL,
  `optical_correction` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `material` int unsigned DEFAULT NULL,
  `type` int unsigned DEFAULT NULL,
  `color` int unsigned DEFAULT NULL,
  `correction_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `treatment` int unsigned DEFAULT NULL,
  `comments` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lenses_material_foreign` (`material`),
  KEY `lenses_type_foreign` (`type`),
  KEY `lenses_color_foreign` (`color`),
  KEY `lenses_treatment_foreign` (`treatment`),
  KEY `lenses_design_foreign` (`design`),
  KEY `lenses_item_index` (`item`),
  CONSTRAINT `lenses_color_foreign` FOREIGN KEY (`color`) REFERENCES `lens_colors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `lenses_design_foreign` FOREIGN KEY (`design`) REFERENCES `lens_designs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `lenses_item__fk` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `lenses_material_foreign` FOREIGN KEY (`material`) REFERENCES `lens_materials` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `lenses_treatment_foreign` FOREIGN KEY (`treatment`) REFERENCES `lens_treatments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `lenses_type_foreign` FOREIGN KEY (`type`) REFERENCES `lens_types` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `lines`;
CREATE TABLE `lines` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `migrations`;
CREATE TABLE `migrations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `group` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `namespace` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `time` int NOT NULL,
  `batch` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `occupations`;
CREATE TABLE `occupations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `order_certificates`;
CREATE TABLE `order_certificates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int unsigned NOT NULL,
  `certificate_type` enum('micas_presentation','poly','blue','crizal_prevencia','crizal_sapphire','essilor','xtrack') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `included` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `order_certificates_order_id_foreign` (`order_id`),
  CONSTRAINT `order_certificates_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `order_patients`;
CREATE TABLE `order_patients` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `order` int unsigned DEFAULT NULL,
  `patient` int unsigned DEFAULT NULL,
  `sale` int unsigned DEFAULT NULL,
  `sale_item` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_patients_order_foreign` (`order`),
  KEY `order_patients_patient_foreign` (`patient`),
  KEY `order_patients_sale_foreign` (`sale`),
  KEY `order_patients_sale_item_foreign` (`sale_item`),
  CONSTRAINT `order_patients_order_foreign` FOREIGN KEY (`order`) REFERENCES `orders` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `order_patients_patient_foreign` FOREIGN KEY (`patient`) REFERENCES `patients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `order_patients_sale_foreign` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `order_patients_sale_item_foreign` FOREIGN KEY (`sale_item`) REFERENCES `sale_items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `order_shipments`;
CREATE TABLE `order_shipments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int unsigned NOT NULL,
  `shipment_id` int unsigned NOT NULL,
  `shipment_type` enum('outbound','return') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'outbound',
  PRIMARY KEY (`id`),
  KEY `order_shipments_order_id_foreign` (`order_id`),
  KEY `order_shipments_shipment_id_foreign` (`shipment_id`),
  CONSTRAINT `order_shipments_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `order_shipments_shipment_id_foreign` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL,
  `lab` int unsigned DEFAULT NULL,
  `patient` int unsigned DEFAULT NULL,
  `internal_folio` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `essilor_folio` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `job_folio` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('pending','processing','to_delivery','delivered','completed','warranty') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `laboratory_status` enum('pending_shipment','in_transit','in_process','received','defective','returned') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending_shipment',
  `lab_sent` datetime DEFAULT NULL,
  `lab_received` datetime DEFAULT NULL,
  `work_received_at` datetime DEFAULT NULL,
  `delivered` datetime DEFAULT NULL,
  `panoramic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pantoscopic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `vertex_distance` json DEFAULT NULL,
  `initials` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `nve` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `frame_measures` json DEFAULT NULL,
  `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `laboratory_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `orders_sale_foreign` (`sale`),
  KEY `orders_lab_foreign` (`lab`),
  CONSTRAINT `orders_lab_foreign` FOREIGN KEY (`lab`) REFERENCES `labs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `orders_sale_foreign` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `patients`;
CREATE TABLE `patients` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `person` int unsigned DEFAULT NULL,
  `store` int unsigned DEFAULT NULL,
  `user` int unsigned DEFAULT NULL,
  `company` int unsigned DEFAULT NULL,
  `card_id` int unsigned DEFAULT NULL,
  `original_id` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patients_person_foreign` (`person`),
  KEY `patients_user_foreign` (`user`),
  KEY `patients_store__fk` (`store`),
  CONSTRAINT `patients_person_foreign` FOREIGN KEY (`person`) REFERENCES `persons` (`id`),
  CONSTRAINT `patients_store__fk` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `patients_user_foreign` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `payment_cards`;
CREATE TABLE `payment_cards` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `bank_account` int unsigned DEFAULT NULL,
  `modality` int DEFAULT NULL,
  `alias` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `commission` decimal(4,2) NOT NULL,
  `store` int unsigned DEFAULT NULL,
  `bank_payment_type` int DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_cards_bank_account_foreign` (`bank_account`),
  CONSTRAINT `payment_cards_bank_account_foreign` FOREIGN KEY (`bank_account`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned NOT NULL,
  `store` int unsigned DEFAULT NULL COMMENT 'Sucursal donde se recibió el pago',
  `owner` int unsigned DEFAULT NULL,
  `terminal` int unsigned DEFAULT NULL,
  `bank_account` int unsigned DEFAULT NULL,
  `status` enum('pending','paid','deposited') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
  `received_at` datetime DEFAULT NULL,
  `payment_type` enum('cash','transfer','card') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'cash',
  `account` int unsigned DEFAULT NULL,
  `amount` decimal(11,2) NOT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payments_sale_foreign` (`sale`),
  KEY `payments_account__fk` (`account`),
  KEY `idx_payments_cash_out_lookup` (`store`,`created_by`,`created_at`,`payment_type`,`status`),
  CONSTRAINT `payments_account__fk` FOREIGN KEY (`account`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `payments_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `person_phones`;
CREATE TABLE `person_phones` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `person` int unsigned DEFAULT NULL,
  `number` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `type` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `person_phones_person_foreign` (`person`),
  CONSTRAINT `person_phones_person_foreign` FOREIGN KEY (`person`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `person_taxes`;
CREATE TABLE `person_taxes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `person` int unsigned DEFAULT NULL,
  `rfc` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `curp` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `tax_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `tax_address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `postal_code` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `tax_regime` enum('general','federal','municipal','local','special') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `person_taxes_person_foreign` (`person`),
  CONSTRAINT `person_taxes_person_foreign` FOREIGN KEY (`person`) REFERENCES `persons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `persons`;
CREATE TABLE `persons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `last_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `main_phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `street` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `postal_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `occupation` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `persons_occupation_foreign` (`occupation`),
  CONSTRAINT `persons_occupation_foreign` FOREIGN KEY (`occupation`) REFERENCES `occupations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `prescription_details`;
CREATE TABLE `prescription_details` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `prescription` int unsigned NOT NULL,
  `eye` enum('right','left') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `source` enum('autorrefractometer','total','final','subjective') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sphere` decimal(5,2) DEFAULT NULL,
  `cylinder` decimal(5,2) DEFAULT NULL,
  `axis` int DEFAULT NULL,
  `addition` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `prescription_details_prescription_foreign` (`prescription`),
  CONSTRAINT `prescription_details_prescription_foreign` FOREIGN KEY (`prescription`) REFERENCES `prescriptions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `prescriptions`;
CREATE TABLE `prescriptions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `patient` int unsigned NOT NULL,
  `consultation` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `prescriptions_patient_foreign` (`patient`),
  KEY `prescriptions_consultation_foreign` (`consultation`),
  CONSTRAINT `prescriptions_consultation_foreign` FOREIGN KEY (`consultation`) REFERENCES `consultations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `prescriptions_patient_foreign` FOREIGN KEY (`patient`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `promotion_items`;
CREATE TABLE `promotion_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `promotion_id` int unsigned NOT NULL,
  `item` int unsigned NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_promotion_items_unique` (`promotion_id`,`item`),
  KEY `promotion_items_item_id_foreign` (`item`),
  KEY `idx_promotion_items_deleted_at` (`deleted_at`),
  CONSTRAINT `promotion_items_item_id_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `promotion_items_promotion_id_foreign` FOREIGN KEY (`promotion_id`) REFERENCES `promotions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `promotions`;
CREATE TABLE `promotions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `type` enum('percent','fixed','bogo','bundle','tier_amount','tier_qty','upgrade','cashback','points','shipping','financing','warranty') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `scope` enum('item','order','mixed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'mixed',
  `priority` int NOT NULL DEFAULT '100',
  `combinable` tinyint(1) NOT NULL DEFAULT '0',
  `channel` enum('store','online','both') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'both',
  `audience` json DEFAULT NULL,
  `constraints_json` json DEFAULT NULL,
  `rule_json` json NOT NULL,
  `starts_at` datetime NOT NULL,
  `ends_at` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `active_starts_at_ends_at` (`active`,`starts_at`,`ends_at`),
  KEY `priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `supplier` int unsigned DEFAULT NULL,
  `store` int unsigned DEFAULT NULL,
  `bill` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `purchased_at` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `purchases_supplier_foreign` (`supplier`),
  KEY `purchases_store_foreign` (`store`),
  KEY `purchases_created_by_foreign` (`created_by`),
  CONSTRAINT `purchases_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `purchases_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `purchases_supplier_foreign` FOREIGN KEY (`supplier`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `range_intervals`;
CREATE TABLE `range_intervals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `range` int unsigned NOT NULL,
  `min_value` decimal(5,2) NOT NULL,
  `max_value` decimal(5,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `range_intervals_range_foreign` (`range`),
  CONSTRAINT `range_intervals_range_foreign` FOREIGN KEY (`range`) REFERENCES `ranges` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `ranges`;
CREATE TABLE `ranges` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime DEFAULT (now()),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `sale_item_promotions`;
CREATE TABLE `sale_item_promotions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL,
  `sale_item` int unsigned DEFAULT NULL,
  `promotion` int unsigned DEFAULT NULL,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `sale_item_promotions_promotion_foreign` (`promotion`),
  CONSTRAINT `sale_item_promotions_promotion_foreign` FOREIGN KEY (`promotion`) REFERENCES `promotions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `sale_items`;
CREATE TABLE `sale_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL,
  `patient` int unsigned DEFAULT NULL,
  `prescription` int unsigned DEFAULT NULL,
  `store` int unsigned DEFAULT NULL,
  `inventory` int unsigned DEFAULT NULL,
  `item` int unsigned DEFAULT NULL,
  `lens_side` enum('left','right','pair') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pair',
  `is_partial` tinyint(1) DEFAULT '0',
  `qty` int NOT NULL,
  `sale_price` decimal(10,2) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `final_price` decimal(10,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `base_unit_price` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `sale_items_sale_foreign` (`sale`),
  KEY `sale_items_item_foreign` (`item`),
  KEY `sale_items_inventory_foreign` (`inventory`),
  KEY `sale_items_patient` (`patient`),
  KEY `sale_items__prescription_fk` (`prescription`),
  CONSTRAINT `sale_items__prescription_fk` FOREIGN KEY (`prescription`) REFERENCES `prescriptions` (`id`),
  CONSTRAINT `sale_items_inventory_foreign` FOREIGN KEY (`inventory`) REFERENCES `inventories` (`id`),
  CONSTRAINT `sale_items_item_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`),
  CONSTRAINT `sale_items_patient_foreign` FOREIGN KEY (`patient`) REFERENCES `patients` (`id`),
  CONSTRAINT `sale_items_sale_foreign` FOREIGN KEY (`sale`) REFERENCES `sales` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `sale_promotions`;
CREATE TABLE `sale_promotions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sale` int unsigned DEFAULT NULL,
  `promotion` int unsigned DEFAULT NULL,
  `promo_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `sale_promotions_promotion_foreign` (`promotion`),
  CONSTRAINT `sale_promotions_promotion_foreign` FOREIGN KEY (`promotion`) REFERENCES `promotions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `lookup_code` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `store` int unsigned DEFAULT NULL,
  `patient` int unsigned DEFAULT NULL,
  `items` int NOT NULL,
  `type` enum('cash','aside','credit') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `payment_type` enum('cash','card','transfer','credit') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'cash',
  `aut` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `delivery` enum('store','pending','delivered') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
  `amount` decimal(10,2) NOT NULL,
  `comments` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `document` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `cancelled_by` int unsigned DEFAULT NULL,
  `cancellation_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sales_lookup_code` (`lookup_code`),
  KEY `sales_store_foreign` (`store`),
  KEY `sales_patient_foreign` (`patient`),
  KEY `sales_created_by_foreign` (`created_by`),
  CONSTRAINT `sales_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `sales_patient_foreign` FOREIGN KEY (`patient`) REFERENCES `patients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `sales_store_foreign` FOREIGN KEY (`store`) REFERENCES `stores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `type` varchar(31) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'string',
  `context` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `shipments`;
CREATE TABLE `shipments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `lab` int unsigned DEFAULT NULL,
  `tracking_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `carrier` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('pending','sent','in_transit','delivered') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
  `sent_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `solar_frames`;
CREATE TABLE `solar_frames` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `item` int unsigned DEFAULT NULL,
  `frame` int unsigned DEFAULT NULL,
  `material` int unsigned DEFAULT NULL,
  `property` int unsigned DEFAULT NULL,
  `treatment` int unsigned DEFAULT NULL,
  `treatment_color` int unsigned DEFAULT NULL,
  `lens_fade` int unsigned DEFAULT NULL,
  `lens_color` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `solar_frames_item_foreign` (`item`),
  KEY `solar_frames_frame_foreign` (`frame`),
  KEY `solar_frames_material_foreign` (`material`),
  KEY `solar_frames_property_foreign` (`property`),
  KEY `solar_frames_treatment_foreign` (`treatment`),
  KEY `solar_frames_treatment_color_foreign` (`treatment_color`),
  KEY `solar_frames_lens_fade_foreign` (`lens_fade`),
  KEY `solar_frames_lens_color_foreign` (`lens_color`),
  CONSTRAINT `solar_frames_frame_foreign` FOREIGN KEY (`frame`) REFERENCES `frames` (`id`),
  CONSTRAINT `solar_frames_item_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`),
  CONSTRAINT `solar_frames_lens_color_foreign` FOREIGN KEY (`lens_color`) REFERENCES `solar_lens_colors` (`id`),
  CONSTRAINT `solar_frames_lens_fade_foreign` FOREIGN KEY (`lens_fade`) REFERENCES `solar_lens_fade` (`id`),
  CONSTRAINT `solar_frames_material_foreign` FOREIGN KEY (`material`) REFERENCES `solar_lens_materials` (`id`),
  CONSTRAINT `solar_frames_property_foreign` FOREIGN KEY (`property`) REFERENCES `solar_lens_properties` (`id`),
  CONSTRAINT `solar_frames_treatment_color_foreign` FOREIGN KEY (`treatment_color`) REFERENCES `solar_lens_treatment_colors` (`id`),
  CONSTRAINT `solar_frames_treatment_foreign` FOREIGN KEY (`treatment`) REFERENCES `solar_lens_treatments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `solar_lens_colors`;
CREATE TABLE `solar_lens_colors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `solar_lens_fade`;
CREATE TABLE `solar_lens_fade` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `solar_lens_materials`;
CREATE TABLE `solar_lens_materials` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `solar_lens_properties`;
CREATE TABLE `solar_lens_properties` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `solar_lens_treatment_colors`;
CREATE TABLE `solar_lens_treatment_colors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `solar_lens_treatments`;
CREATE TABLE `solar_lens_treatments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `stores`;
CREATE TABLE `stores` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `address` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suppliers_company_foreign` (`company`),
  CONSTRAINT `suppliers_company_foreign` FOREIGN KEY (`company`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `transfers`;
CREATE TABLE `transfers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `inventory` int unsigned DEFAULT NULL,
  `item` int unsigned DEFAULT NULL,
  `qty` int NOT NULL,
  `store_dispatch` int unsigned DEFAULT NULL,
  `store_receive` int unsigned DEFAULT NULL,
  `created_by` int unsigned DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `transfers_inventory_foreign` (`inventory`),
  KEY `transfers_item_foreign` (`item`),
  KEY `transfers_store_dispatch_foreign` (`store_dispatch`),
  KEY `transfers_store_receive_foreign` (`store_receive`),
  KEY `transfers_created_by_foreign` (`created_by`),
  CONSTRAINT `transfers_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `transfers_inventory_foreign` FOREIGN KEY (`inventory`) REFERENCES `inventories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `transfers_item_foreign` FOREIGN KEY (`item`) REFERENCES `items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `transfers_store_dispatch_foreign` FOREIGN KEY (`store_dispatch`) REFERENCES `stores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `transfers_store_receive_foreign` FOREIGN KEY (`store_receive`) REFERENCES `stores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status_message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `last_active` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;