-- MySQL full schema for Invitacion app (single DB mode)
-- Recommended MySQL 8.0+

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_types (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(50) NOT NULL UNIQUE,
  label VARCHAR(80) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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,
  image_url VARCHAR(500) 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,
  INDEX idx_products_event_type_code (event_type_code),
  INDEX idx_products_active (is_active),
  CONSTRAINT fk_products_event_type_code FOREIGN KEY (event_type_code) REFERENCES event_types(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  stripe_session_id VARCHAR(255) NULL,
  amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_orders_user_id (user_id),
  INDEX idx_orders_status (status),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_events (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  event_date DATE NULL,
  venue VARCHAR(255) NULL,
  notes TEXT NULL,
  event_type_code VARCHAR(50) NULL,
  main_person_name VARCHAR(180) NULL,
  secondary_person_name VARCHAR(180) NULL,
  padrinos_names TEXT NULL,
  salon_name VARCHAR(255) NULL,
  event_time TIME NULL,
  ceremony_place VARCHAR(255) NULL,
  ceremony_time TIME NULL,
  event_address VARCHAR(255) NULL,
  maps_url VARCHAR(500) NULL,
  dress_code VARCHAR(120) NULL,
  rsvp_deadline DATE NULL,
  contact_whatsapp VARCHAR(60) NULL,
  main_message TEXT NULL,
  source_product_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_events_user (user_id),
  INDEX idx_user_events_type (event_type_code),
  CONSTRAINT fk_user_events_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_events_product FOREIGN KEY (source_product_id) REFERENCES products(id) ON DELETE SET NULL,
  CONSTRAINT fk_user_events_type_code FOREIGN KEY (event_type_code) REFERENCES event_types(code) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_guests (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  event_id BIGINT UNSIGNED NULL,
  name VARCHAR(180) NOT NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  adults TINYINT UNSIGNED NOT NULL DEFAULT 1,
  kids TINYINT UNSIGNED NOT NULL DEFAULT 0,
  pass_type VARCHAR(40) NULL,
  pass_count TINYINT UNSIGNED NOT NULL DEFAULT 1,
  rsvp_status VARCHAR(20) NOT NULL DEFAULT 'pending',
  invite_sent_at DATETIME NULL,
  table_name VARCHAR(120) NULL,
  dietary_notes VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_guests_user (user_id),
  INDEX idx_user_guests_event (event_id),
  CONSTRAINT fk_user_guests_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_guests_event FOREIGN KEY (event_id) REFERENCES user_events(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_field_templates (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  event_type_id INT UNSIGNED NOT NULL,
  field_key VARCHAR(80) NOT NULL,
  field_label VARCHAR(120) NOT NULL,
  input_type VARCHAR(20) NOT NULL DEFAULT 'text',
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  group_name VARCHAR(80) NULL,
  placeholder VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  options_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_event_field_templates_type_key (event_type_id, field_key),
  CONSTRAINT fk_event_field_templates_type FOREIGN KEY (event_type_id) REFERENCES event_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_event_details (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  event_id BIGINT UNSIGNED NOT NULL,
  field_key VARCHAR(80) NOT NULL,
  field_value TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_event_details_event_key (event_id, field_key),
  CONSTRAINT fk_user_event_details_event FOREIGN KEY (event_id) REFERENCES user_events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_event_timeline (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  event_id BIGINT UNSIGNED NOT NULL,
  item_time TIME NULL,
  item_title VARCHAR(180) NOT NULL,
  item_note TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_event_timeline_event (event_id),
  CONSTRAINT fk_user_event_timeline_event FOREIGN KEY (event_id) REFERENCES user_events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO event_types (code, label) VALUES
('boda', 'Boda'),
('quince', 'XV Anos'),
('bautizo', 'Bautizo'),
('comunion', 'Primera Comunion'),
('graduacion', 'Graduacion'),
('cumpleanos', 'Cumpleanos'),
('corporativo', 'Corporativo');

INSERT IGNORE INTO products (title, slug, description, price_cents, template_file, image_url, event_type_code) VALUES
('Ejemplo: Boda floral', 'boda-floral', 'Invitacion clasica floral', 1500, 'designs/boda-floral.pen', '/assets/imagen1.png', 'boda');
