-- Baltique OS Core 0.7.2 — PayCore Entity Routing
-- Adds multiple payment recipients/entities and routing rules.
-- Idempotent table creation. Column additions are handled by upgrade_0_7_2.php.

CREATE TABLE IF NOT EXISTS paycore_entities (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_key VARCHAR(80) NOT NULL,
  display_name VARCHAR(190) NOT NULL,
  legal_name VARCHAR(190) NULL,
  entity_type VARCHAR(60) NOT NULL DEFAULT 'company',
  bank_recipient VARCHAR(190) NULL,
  bank_account VARCHAR(64) NULL,
  bank_title_prefix VARCHAR(120) NULL,
  default_currency CHAR(3) NOT NULL DEFAULT 'PLN',
  active TINYINT(1) NOT NULL DEFAULT 1,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_paycore_entities_key (entity_key),
  KEY idx_paycore_entities_active (active, is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS paycore_entity_routes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_key VARCHAR(100) NOT NULL,
  route_label VARCHAR(190) NOT NULL,
  order_type VARCHAR(60) NULL,
  item_type VARCHAR(80) NULL,
  catalog_category VARCHAR(80) NULL,
  catalog_sku VARCHAR(100) NULL,
  payment_entity_id BIGINT UNSIGNED NOT NULL,
  priority INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_paycore_entity_routes_key (route_key),
  KEY idx_paycore_entity_routes_active (active, priority),
  KEY idx_paycore_entity_routes_entity (payment_entity_id),
  CONSTRAINT fk_paycore_entity_routes_entity FOREIGN KEY (payment_entity_id) REFERENCES paycore_entities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO paycore_entities (entity_key, display_name, legal_name, entity_type, bank_recipient, bank_account, bank_title_prefix, active, is_default, notes, created_at, updated_at) VALUES
('pawel_szychta', 'Konto Prof. Paweł Szychta', 'Konto Prof. Paweł Szychta', 'main_company', 'Konto Prof. Paweł Szychta', '', 'Baltique Prof. Szychta', 1, 1, 'Konsultacje, zadatki, operacje, medycyna estetyczna, fizjoterapia, kontrole dodatkowe, usługi specjalistów.', NOW(), NOW()),
('magdalena_szychta', 'Konto Magdalena Szychta', 'Konto Magdalena Szychta', 'product_company', 'Konto Magdalena Szychta', '', 'Baltique Magdalena Szychta', 1, 0, 'Kosmetyki, implanty, biustonosze pooperacyjne, pasy uciskowe, scar care, zestawy pozabiegowe.', NOW(), NOW())
ON DUPLICATE KEY UPDATE
  display_name=VALUES(display_name),
  legal_name=VALUES(legal_name),
  entity_type=VALUES(entity_type),
  bank_recipient=COALESCE(NULLIF(paycore_entities.bank_recipient,''), VALUES(bank_recipient)),
  bank_title_prefix=COALESCE(NULLIF(paycore_entities.bank_title_prefix,''), VALUES(bank_title_prefix)),
  active=VALUES(active),
  notes=VALUES(notes),
  updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'order_consultation', 'Konsultacje → Konto Prof. Paweł Szychta', 'consultation', NULL, NULL, NULL, id, 100, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='pawel_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), route_label=VALUES(route_label), priority=VALUES(priority), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'order_surgery_deposit', 'Zadatki operacyjne → Konto Prof. Paweł Szychta', 'surgery_deposit', NULL, NULL, NULL, id, 100, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='pawel_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), route_label=VALUES(route_label), priority=VALUES(priority), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'order_surgery_balance', 'Dopłaty za operacje → Konto Prof. Paweł Szychta', 'surgery_balance', NULL, NULL, NULL, id, 100, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='pawel_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), route_label=VALUES(route_label), priority=VALUES(priority), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'category_consultation', 'Kategoria consultation → Konto Prof. Paweł Szychta', NULL, NULL, 'consultation', NULL, id, 90, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='pawel_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'category_surgery', 'Kategoria surgery → Konto Prof. Paweł Szychta', NULL, NULL, 'surgery', NULL, id, 90, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='pawel_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'category_service', 'Usługi / specjaliści → Konto Prof. Paweł Szychta', NULL, NULL, 'service', NULL, id, 110, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='pawel_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'category_implant', 'Implanty → Konto Magdalena Szychta', NULL, NULL, 'implant', NULL, id, 80, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'category_product', 'Produkty → Konto Magdalena Szychta', NULL, NULL, 'product', NULL, id, 80, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'sku_scar_care', 'Scar care / żele / plastry → Konto Magdalena Szychta', NULL, NULL, NULL, 'SCAR_CARE', id, 10, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'sku_breast_implants', 'Implanty piersi → Konto Magdalena Szychta', NULL, NULL, NULL, 'BREAST_IMPLANTS_PACKAGE', id, 10, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'sku_postop_bra', 'Biustonosze pooperacyjne → Konto Magdalena Szychta', NULL, NULL, NULL, 'POSTOP_BRA', id, 10, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'sku_compression_garment', 'Pasy/odzież uciskowa → Konto Magdalena Szychta', NULL, NULL, NULL, 'COMPRESSION_GARMENT', id, 10, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'sku_skincare_postop', 'Kosmetyki → Konto Magdalena Szychta', NULL, NULL, NULL, 'SKINCARE_POSTOP', id, 10, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_catalog_items (sku, name, category, description, default_price_gross_cents, currency, active, metadata_json, created_at, updated_at) VALUES
('POSTOP_KIT', 'Zestaw pozabiegowy', 'product', 'Zestaw produktów pozabiegowych — routing do konta Magdalena Szychta.', 0, 'PLN', 1, JSON_OBJECT('requires_manual_price', true, 'core','0.7.2'), NOW(), NOW())
ON DUPLICATE KEY UPDATE name=VALUES(name), category=VALUES(category), description=VALUES(description), active=VALUES(active), updated_at=NOW();

INSERT INTO paycore_entity_routes (route_key, route_label, order_type, item_type, catalog_category, catalog_sku, payment_entity_id, priority, active, created_at, updated_at)
SELECT 'sku_postop_kit', 'Zestawy pozabiegowe → Konto Magdalena Szychta', NULL, NULL, NULL, 'POSTOP_KIT', id, 10, 1, NOW(), NOW() FROM paycore_entities WHERE entity_key='magdalena_szychta'
ON DUPLICATE KEY UPDATE payment_entity_id=VALUES(payment_entity_id), active=VALUES(active), updated_at=NOW();

INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES
('core_version','0.7.2',NOW(),NOW()),
('paycore_entity_routing_enabled','1',NOW(),NOW()),
('paycore_split_mixed_cart','1',NOW(),NOW()),
('paycore_show_entity_to_patient','1',NOW(),NOW()),
('paycore_entity_routing_note','Koszyk mieszany jest automatycznie dzielony według odbiorcy płatności.',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();
