-- Zylon advanced e-commerce migration (run once on existing zylon_db)
USE zylon_db;

-- ---------------------------------------------------------------------------
-- users
-- ---------------------------------------------------------------------------
ALTER TABLE users
  ADD COLUMN email VARCHAR(190) NULL DEFAULT NULL AFTER username,
  ADD COLUMN phone VARCHAR(32) NULL DEFAULT NULL AFTER email,
  ADD COLUMN profile_image VARCHAR(512) NULL DEFAULT NULL AFTER phone,
  ADD COLUMN address TEXT NULL AFTER profile_image,
  ADD COLUMN wallet_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER address,
  ADD COLUMN suspended TINYINT(1) NOT NULL DEFAULT 0 AFTER wallet_balance;

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_phone ON users (phone);
CREATE INDEX idx_users_role ON users (role);

-- ---------------------------------------------------------------------------
-- products (nullable tracking first, then backfill, then NOT NULL + unique)
-- ---------------------------------------------------------------------------
ALTER TABLE products
  ADD COLUMN tracking_code VARCHAR(40) NULL DEFAULT NULL AFTER id,
  ADD COLUMN slug VARCHAR(255) NULL DEFAULT NULL AFTER title,
  ADD COLUMN video_path VARCHAR(512) NULL DEFAULT NULL AFTER image_url,
  ADD COLUMN brand VARCHAR(120) NULL DEFAULT NULL AFTER category,
  ADD COLUMN weight_kg DECIMAL(10,3) NULL DEFAULT NULL AFTER stock,
  ADD COLUMN dimensions VARCHAR(120) NULL DEFAULT NULL AFTER weight_kg,
  ADD COLUMN tags TEXT NULL AFTER dimensions,
  ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active' AFTER tags,
  ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER status;

UPDATE products SET tracking_code = CONCAT('PRD-LEG-', LPAD(id, 6, '0')) WHERE tracking_code IS NULL OR tracking_code = '';
UPDATE products SET slug = CONCAT('product-', id) WHERE slug IS NULL OR slug = '';

ALTER TABLE products
  MODIFY COLUMN tracking_code VARCHAR(40) NOT NULL;

CREATE UNIQUE INDEX uq_products_slug ON products (slug);
CREATE UNIQUE INDEX uq_products_tracking ON products (tracking_code);

-- ---------------------------------------------------------------------------
-- orders
-- ---------------------------------------------------------------------------
ALTER TABLE orders
  ADD COLUMN order_tracking_id VARCHAR(40) NULL DEFAULT NULL AFTER id;

CREATE UNIQUE INDEX uq_orders_tracking_public ON orders (order_tracking_id);

-- ---------------------------------------------------------------------------
-- order_items
-- ---------------------------------------------------------------------------
ALTER TABLE order_items
  ADD COLUMN product_tracking_code VARCHAR(40) NULL DEFAULT NULL AFTER product_id;

-- ---------------------------------------------------------------------------
-- bank_cards
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bank_cards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bank_name VARCHAR(120) NOT NULL,
  card_number_masked VARCHAR(32) NOT NULL,
  card_holder VARCHAR(120) NOT NULL,
  iban VARCHAR(40) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE INDEX idx_bank_cards_active ON bank_cards (is_active, sort_order);
