-- Migration: products + event type mapping (MySQL 8.0+)
-- Date: 2026-02-17

-- Ensure event types used by invitation flow exist.
INSERT IGNORE INTO event_types (code, label) VALUES
('comunion', 'Primera Comunion'),
('graduacion', 'Graduacion'),
('cumpleanos', 'Cumpleanos');

-- Product catalog with explicit invitation type.
CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  description TEXT NULL,
  price_cents INT UNSIGNED NOT NULL DEFAULT 0,
  template_file VARCHAR(255) NULL,
  event_type_code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_products_event_type_code FOREIGN KEY (event_type_code) REFERENCES event_types(code),
  INDEX idx_products_event_type_code (event_type_code),
  INDEX idx_products_active (is_active)
) ENGINE=InnoDB;

-- Backward-compatible columns if products already existed.
ALTER TABLE products
  ADD COLUMN IF NOT EXISTS image_url VARCHAR(500) NULL AFTER template_file,
  ADD COLUMN IF NOT EXISTS event_type_code VARCHAR(50) NULL AFTER image_url,
  ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER event_type_code,
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER is_active,
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

SET @idx_products_event_type_code_exists := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'products'
    AND index_name = 'idx_products_event_type_code'
);
SET @sql := IF(
  @idx_products_event_type_code_exists = 0,
  'CREATE INDEX idx_products_event_type_code ON products(event_type_code)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Optional link from event to selected product.
ALTER TABLE events
  ADD COLUMN IF NOT EXISTS source_product_id BIGINT UNSIGNED NULL AFTER event_type_id;

ALTER TABLE guests
  ADD COLUMN IF NOT EXISTS invite_sent_at DATETIME NULL AFTER rsvp_status;

SET @idx_events_source_product_exists := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'events'
    AND index_name = 'idx_events_source_product'
);
SET @sql := IF(
  @idx_events_source_product_exists = 0,
  'CREATE INDEX idx_events_source_product ON events(source_product_id)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @fk_events_source_product_exists := (
  SELECT COUNT(*)
  FROM information_schema.table_constraints
  WHERE table_schema = DATABASE()
    AND table_name = 'events'
    AND constraint_type = 'FOREIGN KEY'
    AND constraint_name = 'fk_events_source_product'
);
SET @sql := IF(
  @fk_events_source_product_exists = 0,
  'ALTER TABLE events ADD CONSTRAINT fk_events_source_product FOREIGN KEY (source_product_id) REFERENCES products(id)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Backfill event_type_code from slug/title when possible.
UPDATE products
SET event_type_code = CASE
  WHEN LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%quince%' OR LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%xv%' THEN 'quince'
  WHEN LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%boda%' OR LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%wedding%' THEN 'boda'
  WHEN LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%bautizo%' THEN 'bautizo'
  WHEN LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%comunion%' THEN 'comunion'
  WHEN LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%gradua%' THEN 'graduacion'
  WHEN LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%cumple%' THEN 'cumpleanos'
  WHEN LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%corpora%' OR LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%empresa%' OR LOWER(CONCAT(IFNULL(slug,''), ' ', IFNULL(title,''))) LIKE '%business%' THEN 'corporativo'
  ELSE event_type_code
END
WHERE event_type_code IS NULL OR event_type_code = '';

UPDATE products
SET image_url = '/assets/imagen1.png'
WHERE slug = 'boda-floral' AND (image_url IS NULL OR image_url = '');
