-- ================================================================
-- SHIFTDECK Update SQL — v1.23
-- Bestellungs-Modul Komplettfix
-- ================================================================
USE shiftdeck;

-- ── 1. orders-Tabelle neu erstellen falls nicht vorhanden ────────
CREATE TABLE IF NOT EXISTS `orders` (
  `id`                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_number`       VARCHAR(50)   NOT NULL DEFAULT '',
  `product_name`       VARCHAR(255)  DEFAULT '',
  `amount`             DECIMAL(10,2) DEFAULT 0.00,
  `user_count`         INT UNSIGNED  DEFAULT 1,
  `payment_type`       VARCHAR(50)   DEFAULT 'bank',
  `status`             VARCHAR(50)   DEFAULT 'pending',
  `customer_name`      VARCHAR(255)  DEFAULT '',
  `customer_email`     VARCHAR(255)  DEFAULT '',
  `customer_company`   VARCHAR(255)  DEFAULT '',
  `customer_phone`     VARCHAR(50)   DEFAULT '',
  `customer_address`   VARCHAR(500)  DEFAULT '',
  `billing_cycle`      VARCHAR(20)   DEFAULT 'monthly',
  `billing_day`        TINYINT UNSIGNED DEFAULT 1,
  `addons`             TEXT          DEFAULT NULL,
  `license_key`        VARCHAR(200)  DEFAULT NULL,
  `paypal_order_id`    VARCHAR(100)  DEFAULT NULL,
  `paypal_payer_email` VARCHAR(255)  DEFAULT NULL,
  `notes`              TEXT          DEFAULT NULL,
  `created_at`         DATETIME      DEFAULT CURRENT_TIMESTAMP,
  `updated_at`         DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_order_number` (`order_number`),
  KEY `idx_email`  (`customer_email`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. Fehlende Spalten nachrüsten ───────────────────────────────
ALTER TABLE `orders`
  ADD COLUMN IF NOT EXISTS `customer_name`      VARCHAR(255)  DEFAULT '' AFTER `status`,
  ADD COLUMN IF NOT EXISTS `customer_email`     VARCHAR(255)  DEFAULT '' AFTER `customer_name`,
  ADD COLUMN IF NOT EXISTS `customer_company`   VARCHAR(255)  DEFAULT '' AFTER `customer_email`,
  ADD COLUMN IF NOT EXISTS `customer_phone`     VARCHAR(50)   DEFAULT '' AFTER `customer_company`,
  ADD COLUMN IF NOT EXISTS `customer_address`   VARCHAR(500)  DEFAULT '' AFTER `customer_phone`,
  ADD COLUMN IF NOT EXISTS `billing_cycle`      VARCHAR(20)   DEFAULT 'monthly' AFTER `customer_address`,
  ADD COLUMN IF NOT EXISTS `billing_day`        TINYINT UNSIGNED DEFAULT 1 AFTER `billing_cycle`,
  ADD COLUMN IF NOT EXISTS `addons`             TEXT          DEFAULT NULL AFTER `billing_day`,
  ADD COLUMN IF NOT EXISTS `license_key`        VARCHAR(200)  DEFAULT NULL AFTER `addons`,
  ADD COLUMN IF NOT EXISTS `paypal_order_id`    VARCHAR(100)  DEFAULT NULL AFTER `license_key`,
  ADD COLUMN IF NOT EXISTS `paypal_payer_email` VARCHAR(255)  DEFAULT NULL AFTER `paypal_order_id`,
  ADD COLUMN IF NOT EXISTS `notes`              TEXT          DEFAULT NULL AFTER `paypal_payer_email`,
  ADD COLUMN IF NOT EXISTS `updated_at`         DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- ── 3. Index sicherstellen ────────────────────────────────────────
ALTER TABLE `orders`
  ADD INDEX IF NOT EXISTS `idx_email`  (`customer_email`),
  ADD INDEX IF NOT EXISTS `idx_status` (`status`);

-- ── 4. Sonstige Tabellen ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `customer_sessions` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `customer_id` INT UNSIGNED NOT NULL,
  `token`       VARCHAR(128) NOT NULL,
  `expires_at`  DATETIME NOT NULL,
  `ip_address`  VARCHAR(45) DEFAULT NULL,
  `user_agent`  VARCHAR(500) DEFAULT NULL,
  `created_at`  DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `website_tickets`
  ADD COLUMN IF NOT EXISTS `source`      VARCHAR(50)  DEFAULT 'website',
  ADD COLUMN IF NOT EXISTS `license_key` VARCHAR(200) DEFAULT NULL;

INSERT IGNORE INTO `website_settings` (`setting_key`, `setting_value`, `setting_type`) VALUES
  ('paypalClientId',  'sandbox_REPLACE_WITH_REAL_ID', 'string'),
  ('paypalMode',      'sandbox',                       'string'),
  ('paypalCurrency',  'EUR',                           'string'),
  ('paypalSecret',    '',                              'string'),
  ('companyEmail',    'info@shiftdeck.de',             'string');

-- ── 5. Prüf-Query ─────────────────────────────────────────────────
SELECT order_number, product_name, customer_name, customer_email,
       amount, status, created_at
FROM `orders`
ORDER BY created_at DESC
LIMIT 20;

SELECT COUNT(*) AS orders_total FROM `orders`;
SELECT '✅ SHIFTDECK v1.23 Update erfolgreich' AS status;

-- SHIFTDECK v1.28: sd_licenses Tabelle
CREATE TABLE IF NOT EXISTS `sd_licenses` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `license_key`   VARCHAR(200) NOT NULL UNIQUE,
  `customer_name` VARCHAR(255) NOT NULL DEFAULT '',
  `customer_email`VARCHAR(255) NOT NULL DEFAULT '',
  `product`       VARCHAR(255) NOT NULL DEFAULT 'SHIFTDECK',
  `user_count`    INT UNSIGNED NOT NULL DEFAULT 1,
  `status`        VARCHAR(30)  NOT NULL DEFAULT 'active',
  `billing_type`  VARCHAR(30)  NOT NULL DEFAULT 'monthly',
  `amount`        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `notes`         TEXT,
  `start_date`    DATE DEFAULT NULL,
  `next_due_date` DATE DEFAULT NULL,
  `valid_until`   DATE DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_email`  (`customer_email`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- v2.14: order_id Spalte zu sd_licenses hinzufügen
ALTER TABLE `sd_licenses` ADD COLUMN IF NOT EXISTS `order_id` VARCHAR(50) DEFAULT NULL;
ALTER TABLE `sd_licenses` ADD COLUMN IF NOT EXISTS `notes` TEXT;
ALTER TABLE `sd_licenses` ADD COLUMN IF NOT EXISTS `start_date` DATE DEFAULT NULL;
ALTER TABLE `sd_licenses` ADD COLUMN IF NOT EXISTS `next_due_date` DATE DEFAULT NULL;
ALTER TABLE `sd_licenses` ADD COLUMN IF NOT EXISTS `valid_until` DATE DEFAULT NULL;
