-- Baltique OS Core 0.7.1 — PayCore Payments & Patient Store.
-- Installs commercial order/payment layer for Baltique OS.
-- It does NOT replace OpenCare, KSeF, fiscal printers, accounting, or formal medical records.


CREATE TABLE IF NOT EXISTS paycore_catalog_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku VARCHAR(80) NOT NULL,
  name VARCHAR(190) NOT NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'service',
  description TEXT NULL,
  default_price_gross_cents INT UNSIGNED NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'PLN',
  active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_paycore_catalog_sku (sku),
  KEY idx_paycore_catalog_active (active, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS paycore_order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  catalog_item_id BIGINT UNSIGNED NULL,
  item_type VARCHAR(60) NOT NULL DEFAULT 'other',
  name VARCHAR(190) NOT NULL,
  quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  unit_gross_cents INT UNSIGNED NOT NULL DEFAULT 0,
  total_gross_cents INT UNSIGNED NOT NULL DEFAULT 0,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_paycore_items_order (order_id),
  KEY idx_paycore_items_catalog (catalog_item_id),
  CONSTRAINT fk_paycore_items_order FOREIGN KEY (order_id) REFERENCES paycore_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_paycore_items_catalog FOREIGN KEY (catalog_item_id) REFERENCES paycore_catalog_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS paycore_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  gateway VARCHAR(40) NOT NULL DEFAULT 'manual',
  status VARCHAR(60) NOT NULL DEFAULT 'created',
  amount_gross_cents INT UNSIGNED NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'PLN',
  external_id VARCHAR(190) NULL,
  redirect_url TEXT NULL,
  payload_json JSON NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_paycore_transactions_order (order_id, created_at),
  KEY idx_paycore_transactions_gateway (gateway, status),
  CONSTRAINT fk_paycore_transactions_order FOREIGN KEY (order_id) REFERENCES paycore_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS paycore_webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gateway VARCHAR(40) NOT NULL,
  event_id VARCHAR(190) NULL,
  order_id BIGINT UNSIGNED NULL,
  verified TINYINT(1) NOT NULL DEFAULT 0,
  status VARCHAR(60) NOT NULL DEFAULT 'received',
  payload_json JSON NULL,
  error_text TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_paycore_webhook_gateway (gateway, created_at),
  KEY idx_paycore_webhook_order (order_id),
  KEY idx_paycore_webhook_event (event_id),
  CONSTRAINT fk_paycore_webhook_order FOREIGN KEY (order_id) REFERENCES paycore_orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO paycore_catalog_items (sku, name, category, description, default_price_gross_cents, currency, active, metadata_json, created_at, updated_at) VALUES
('CONSULTATION_FIRST', 'Konsultacja pierwszorazowa', 'consultation', 'Płatność za konsultację pierwszorazową.', 60000, 'PLN', 1, JSON_OBJECT('core','0.7.1'), NOW(), NOW()),
('CONSULTATION_3D', 'Konsultacja 3D', 'consultation', 'Konsultacja z elementem skanu/planowania 3D.', 100000, 'PLN', 1, JSON_OBJECT('core','0.7.1'), NOW(), NOW()),
('SURGERY_DEPOSIT_10', 'Zadatek operacyjny 10%', 'surgery', 'Zadatek tworzony ręcznie lub automatycznie po wyborze terminu operacji.', 0, 'PLN', 1, JSON_OBJECT('percentage',10), NOW(), NOW()),
('BREAST_IMPLANTS_PACKAGE', 'Implanty piersi — pakiet operacyjny', 'implant', 'Pozycja sprzedażowa/rezerwacyjna dla implantów piersi.', 0, 'PLN', 1, JSON_OBJECT('requires_manual_price', true), NOW(), NOW()),
('PHYSIO_POSTOP', 'Fizjoterapia pooperacyjna', 'service', 'Sesja fizjoterapii pooperacyjnej.', 27000, 'PLN', 1, JSON_OBJECT('recommended_after', JSON_ARRAY('abdominoplastyka','liposukcja','piersi')), NOW(), NOW()),
('SCAR_CARE', 'Opieka nad blizną / Scar Care', 'service', 'Pakiet zaleceń i produktów do pielęgnacji blizny.', 30000, 'PLN', 1, JSON_OBJECT('recommended_after', JSON_ARRAY('redukcja piersi','abdominoplastyka','blefaroplastyka')), NOW(), NOW()),
('POSTOP_BRA', 'Biustonosz pooperacyjny', 'product', 'Biustonosz pooperacyjny dopasowany do zabiegu piersi.', 30000, 'PLN', 1, JSON_OBJECT('size_required', true), NOW(), NOW()),
('COMPRESSION_GARMENT', 'Odzież / pas uciskowy', 'product', 'Odzież uciskowa po liposukcji lub abdominoplastyce.', 35000, 'PLN', 1, JSON_OBJECT('size_required', true), NOW(), NOW()),
('SKINCARE_POSTOP', 'Kosmetyki pooperacyjne', 'product', 'Kosmetyki wspierające pielęgnację skóry po zabiegu.', 18000, 'PLN', 1, JSON_OBJECT('optional', true), NOW(), NOW()),
('CARE_PACKAGE_STANDARD', 'Pakiet opieki pooperacyjnej Standard', 'package', 'Pakiet usług i kontaktów kontrolnych po zabiegu.', 149000, 'PLN', 1, JSON_OBJECT('tier','standard'), NOW(), NOW())
ON DUPLICATE KEY UPDATE
  name=VALUES(name), category=VALUES(category), description=VALUES(description), default_price_gross_cents=VALUES(default_price_gross_cents), active=VALUES(active), updated_at=NOW();

INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES
('core_version','0.7.1',NOW(),NOW()),
('paycore_enabled','1',NOW(),NOW()),
('paycore_safe_mode','1',NOW(),NOW()),
('paycore_default_gateway','bank_transfer',NOW(),NOW()),
('paycore_bank_enabled','1',NOW(),NOW()),
('paycore_bank_recipient','Baltique',NOW(),NOW()),
('paycore_bank_account','— uzupełnij w ustawieniach PayCore —',NOW(),NOW()),
('paycore_bank_title_prefix','Baltique OS',NOW(),NOW()),
('paycore_bank_note','W tytule przelewu podaj numer płatności. Po wykonaniu przelewu możesz zgłosić płatność przez link.',NOW(),NOW()),
('paycore_stripe_enabled','0',NOW(),NOW()),
('paycore_p24_enabled','0',NOW(),NOW()),
('paycore_p24_sandbox','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

