-- Memorial product model (public/private pages, media, guestbook, contacts, notices)

CREATE TABLE IF NOT EXISTS memorials (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  slug VARCHAR(120) NOT NULL,
  visibility ENUM('public','private') NOT NULL DEFAULT 'public',

  title VARCHAR(255) NOT NULL,
  deceased_name VARCHAR(255) NOT NULL,
  birth_date DATE NULL,
  death_date DATE NULL,
  city_birth VARCHAR(255) NULL,
  city_death VARCHAR(255) NULL,
  resting_place VARCHAR(255) NULL,
  obituary_text TEXT NULL,
  hero_image_url TEXT NULL,
  cover_image_url TEXT NULL,

  allow_guestbook TINYINT(1) NOT NULL DEFAULT 1,
  allow_photos TINYINT(1) NOT NULL DEFAULT 1,
  allow_videos TINYINT(1) NOT NULL DEFAULT 1,
  allow_qr TINYINT(1) NOT NULL DEFAULT 1,

  storage_limit_bytes BIGINT NOT NULL DEFAULT 1073741824,
  storage_used_bytes BIGINT NOT NULL DEFAULT 0,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,

  UNIQUE KEY uq_memorials_slug (slug),
  KEY idx_memorials_user (user_id, updated_at),
  KEY idx_memorials_visibility (visibility, deleted_at),
  CONSTRAINT fk_memorials_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS memorial_media (
  id CHAR(36) PRIMARY KEY,
  memorial_id CHAR(36) NOT NULL,
  type ENUM('image','video') NOT NULL,
  s3_bucket VARCHAR(191) NOT NULL,
  s3_key VARCHAR(1024) NOT NULL,
  content_type VARCHAR(255) NOT NULL,
  size_bytes BIGINT NOT NULL,
  original_filename VARCHAR(255) NULL,
  caption VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,

  KEY idx_memorial_media_memorial (memorial_id, type, created_at),
  KEY idx_memorial_media_deleted (deleted_at),
  CONSTRAINT fk_memorial_media_memorial FOREIGN KEY (memorial_id) REFERENCES memorials(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS memorial_guestbook_entries (
  id CHAR(36) PRIMARY KEY,
  memorial_id CHAR(36) NOT NULL,
  author_name VARCHAR(255) NOT NULL,
  author_email VARCHAR(320) NULL,
  message TEXT NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at DATETIME NULL,
  deleted_at DATETIME NULL,

  KEY idx_memorial_guestbook_memorial (memorial_id, status, created_at),
  CONSTRAINT fk_memorial_guestbook_memorial FOREIGN KEY (memorial_id) REFERENCES memorials(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS memorial_contacts (
  id CHAR(36) PRIMARY KEY,
  memorial_id CHAR(36) NOT NULL,
  channel ENUM('email','sms') NOT NULL,
  name VARCHAR(255) NULL,
  email VARCHAR(320) NULL,
  phone VARCHAR(64) NULL,
  opt_in TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,

  KEY idx_memorial_contacts_memorial (memorial_id, channel, deleted_at),
  CONSTRAINT fk_memorial_contacts_memorial FOREIGN KEY (memorial_id) REFERENCES memorials(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS memorial_notices (
  id CHAR(36) PRIMARY KEY,
  memorial_id CHAR(36) NOT NULL,
  channel ENUM('email','sms') NOT NULL,
  recipients_count INT NOT NULL DEFAULT 0,
  status ENUM('sent','not_configured','failed') NOT NULL,
  estimated_cost_cents INT NOT NULL DEFAULT 0,
  details_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY idx_memorial_notices_memorial (memorial_id, created_at),
  CONSTRAINT fk_memorial_notices_memorial FOREIGN KEY (memorial_id) REFERENCES memorials(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
