-- ============================================================
-- RunMyStore.ai — Сесия 15 миграция
-- Липсващи таблици + колони спрямо MASTER_TRACKER v3.8
-- Пусни с: mysql -u root -p runmystore < session15_migration.sql
-- ============================================================

-- ─── 1. tenant_ai_memory (FIFO, max 100, последните 20 в prompt) ───
CREATE TABLE IF NOT EXISTS `tenant_ai_memory` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int unsigned NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `content` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_created` (`tenant_id`, `created_at`),
  CONSTRAINT `tam_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tam_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 2. purchase_orders ───
CREATE TABLE IF NOT EXISTS `purchase_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int unsigned NOT NULL,
  `supplier_id` int unsigned DEFAULT NULL,
  `store_id` int unsigned DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `status` enum('draft','sent','partial','received','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `sent_at` timestamp NULL DEFAULT NULL,
  `expected_date` date DEFAULT NULL,
  `received_at` timestamp NULL DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant_status` (`tenant_id`, `status`),
  KEY `supplier_id` (`supplier_id`),
  KEY `store_id` (`store_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `po_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `po_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `po_ibfk_3` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE SET NULL,
  CONSTRAINT `po_ibfk_4` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 3. purchase_order_items ───
CREATE TABLE IF NOT EXISTS `purchase_order_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `purchase_order_id` int unsigned NOT NULL,
  `product_id` int unsigned NOT NULL,
  `qty_ordered` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `qty_received` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `cost_price` decimal(12,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`id`),
  KEY `purchase_order_id` (`purchase_order_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `poi_ibfk_1` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `poi_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 4. shifts (Смени — вход/изход пари) ───
CREATE TABLE IF NOT EXISTS `shifts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int unsigned NOT NULL,
  `store_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `opened_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `closed_at` timestamp NULL DEFAULT NULL,
  `opening_cash` decimal(12,2) NOT NULL DEFAULT '0.00',
  `closing_cash` decimal(12,2) DEFAULT NULL,
  `cash_in` decimal(12,2) NOT NULL DEFAULT '0.00',
  `cash_out` decimal(12,2) NOT NULL DEFAULT '0.00',
  `approved_by` int unsigned DEFAULT NULL,
  `note` text COLLATE utf8mb4_unicode_ci,
  `status` enum('open','closed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  PRIMARY KEY (`id`),
  KEY `idx_tenant_store` (`tenant_id`, `store_id`),
  KEY `user_id` (`user_id`),
  KEY `approved_by` (`approved_by`),
  CONSTRAINT `shifts_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shifts_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shifts_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shifts_ibfk_4` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 5. companies (tenant → company → store) ───
CREATE TABLE IF NOT EXISTS `companies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `eik` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `vat_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mol` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT 'BG',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  CONSTRAINT `companies_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 6. bank_accounts (per company) ───
CREATE TABLE IF NOT EXISTS `bank_accounts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `bank_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `iban` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bic` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'EUR',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `ba_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 7. stores → company_id FK ───
ALTER TABLE `stores`
  ADD COLUMN `company_id` int unsigned DEFAULT NULL AFTER `tenant_id`,
  ADD KEY `company_id` (`company_id`),
  ADD CONSTRAINT `stores_company_fk` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL;

-- ─── 8. Липсващи колони в users ───
ALTER TABLE `users`
  ADD COLUMN `default_store_id` int unsigned DEFAULT NULL AFTER `store_id`,
  ADD COLUMN `fcm_token` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `max_discount_pct`;

-- ─── 9. Липсващи колони в stores ───
ALTER TABLE `stores`
  ADD COLUMN `discount_notify_pct` decimal(5,2) NOT NULL DEFAULT '15.00' AFTER `mol`;

-- ─── 10. Липсващи колони в products ───
ALTER TABLE `products`
  ADD COLUMN `discount_pct` decimal(5,2) NOT NULL DEFAULT '0.00' AFTER `retail_price`,
  ADD COLUMN `discount_ends_at` date DEFAULT NULL AFTER `discount_pct`;

-- ─── 11. notifications.type — добави липсващи enum стойности ───
ALTER TABLE `notifications`
  MODIFY COLUMN `type` enum('low_stock','out_stock','new_order','invoice_due','transfer','delivery','sale_spike','debt','system') COLLATE utf8mb4_unicode_ci NOT NULL;

-- ─── 12. stock_movements.type — синхронизирай с тракера ───
-- Запазваме текущите + добавяме липсващите
ALTER TABLE `stock_movements`
  MODIFY COLUMN `type` enum('in','out','sale','delivery','transfer_in','transfer_out','return','scrap','waste','inventory_adjust','adjustment') COLLATE utf8mb4_unicode_ci NOT NULL;

-- ─── 13. Премахни bundle_items (Bundles = Фаза 2, без таблица засега) ───
DROP TABLE IF EXISTS `bundle_items`;

-- ─── 14. Премахни is_bundle от products (Фаза 2) ───
ALTER TABLE `products` DROP COLUMN `is_bundle`;

-- ============================================================
-- ГОТОВО. Провери с: SHOW TABLES; DESC tenant_ai_memory;
-- ============================================================
