-- =====================================================================
--  GuestBook — Full Schema (for fresh installs only)
-- =====================================================================
--  Use this file ONLY if creating a brand-new database.
--  If you're keeping the existing bigsness_guestbook2026 data, run the
--  migration file (2026_05_09_consolidate_to_guestbookit.sql) instead.
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`            INT          NOT NULL AUTO_INCREMENT,
    `name`          VARCHAR(128) NOT NULL,
    `email`         VARCHAR(255) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `is_admin`      TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_login_at` TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- password_resets
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_resets` (
    `id`         INT       NOT NULL AUTO_INCREMENT,
    `user_id`    INT       NOT NULL,
    `token_hash` CHAR(64)  NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `used_at`    TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_pr_token_hash` (`token_hash`),
    KEY `idx_pr_user`       (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- event_types  (seeded by the migration file)
-- ---------------------------------------------------------------------
-- See 2026_05_09_consolidate_to_guestbookit.sql for the full table
-- definition + seed data. Both files use IF NOT EXISTS so it is safe
-- to run them in either order.

-- ---------------------------------------------------------------------
-- events
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `events` (
    `id`              INT          NOT NULL AUTO_INCREMENT,
    `event_type_id`   INT          NULL DEFAULT NULL,
    `owner_id`        INT          NOT NULL,
    `partner_id`      INT          NULL DEFAULT NULL,
    `slug`            VARCHAR(120) NOT NULL,
    `title`           VARCHAR(255) NOT NULL,
    `host_name`       VARCHAR(128) NOT NULL,
    `event_date`      DATE         NULL DEFAULT NULL,
    `custom_welcome`  TEXT         NULL DEFAULT NULL,
    `cover_photo_url` VARCHAR(512) NULL DEFAULT NULL,
    `is_paid`         TINYINT(1)   NOT NULL DEFAULT 0,
    `status`          ENUM('draft','active','archived','expired') NOT NULL DEFAULT 'draft',
    `paid_at`         TIMESTAMP    NULL DEFAULT NULL,
    `expires_at`      TIMESTAMP    NULL DEFAULT NULL,
    `stripe_checkout_session_id` VARCHAR(255) NULL DEFAULT NULL,
    `stripe_payment_intent_id`   VARCHAR(255) NULL DEFAULT NULL,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_events_slug` (`slug`),
    KEY `idx_events_owner`         (`owner_id`),
    KEY `idx_events_partner`       (`partner_id`),
    KEY `idx_events_event_type_id` (`event_type_id`),
    KEY `idx_events_status_exp`    (`status`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- messages — what guests submit
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `messages` (
    `id`           INT           NOT NULL AUTO_INCREMENT,
    `event_id`     INT           NOT NULL,
    `guest_name`   VARCHAR(128)  NULL DEFAULT NULL,
    `guest_email`  VARCHAR(255)  NULL DEFAULT NULL,
    `type`         ENUM('audio','video','photo','text') NOT NULL,
    `file_url`     VARCHAR(512)  NULL DEFAULT NULL,
    `file_path`    VARCHAR(512)  NULL DEFAULT NULL,
    `mime_type`    VARCHAR(64)   NULL DEFAULT NULL,
    `file_size`    INT           NULL DEFAULT NULL,
    `duration_sec` INT           NULL DEFAULT NULL,
    `text_content` TEXT          NULL DEFAULT NULL,
    `caption`      VARCHAR(500)  NULL DEFAULT NULL,
    `is_approved`  TINYINT(1)    NOT NULL DEFAULT 1,
    `is_hidden`    TINYINT(1)    NOT NULL DEFAULT 0,
    `ip_address`   VARCHAR(45)   NULL DEFAULT NULL,
    `user_agent`   VARCHAR(255)  NULL DEFAULT NULL,
    `created_at`   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_messages_event`    (`event_id`),
    KEY `idx_messages_event_at` (`event_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- storage_extensions — record of paid storage extensions
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `storage_extensions` (
    `id`               INT          NOT NULL AUTO_INCREMENT,
    `event_id`         INT          NOT NULL,
    `user_id`          INT          NOT NULL,
    `stripe_session_id`        VARCHAR(255) NOT NULL,
    `stripe_payment_intent_id` VARCHAR(255) NULL DEFAULT NULL,
    `amount_cents`     INT          NOT NULL,
    `extension_days`   INT          NOT NULL DEFAULT 365,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_storage_ext_session` (`stripe_session_id`),
    KEY `idx_storage_ext_event` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- partners — wedding planners, funeral homes, etc.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `partners` (
    `id`               INT           NOT NULL AUTO_INCREMENT,
    `slug`             VARCHAR(64)   NOT NULL,
    `business_name`    VARCHAR(255)  NOT NULL,
    `contact_name`     VARCHAR(128)  NULL DEFAULT NULL,
    `contact_email`    VARCHAR(255)  NULL DEFAULT NULL,
    `phone`            VARCHAR(40)   NULL DEFAULT NULL,
    `logo_url`         VARCHAR(512)  NULL DEFAULT NULL,
    `tagline`          VARCHAR(255)  NULL DEFAULT NULL,
    `welcome_message`  TEXT          NULL DEFAULT NULL,
    `is_active`        TINYINT(1)    NOT NULL DEFAULT 1,
    `revenue_share_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `created_at`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_partners_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- email_log — record of outgoing emails (for debugging + reminders)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `email_log` (
    `id`         INT          NOT NULL AUTO_INCREMENT,
    `event_id`   INT          NULL DEFAULT NULL,
    `to_email`   VARCHAR(255) NOT NULL,
    `subject`    VARCHAR(255) NOT NULL,
    `kind`       VARCHAR(64)  NOT NULL,
    `status`     ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
    `error`      TEXT         NULL DEFAULT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `sent_at`    TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_email_log_event` (`event_id`),
    KEY `idx_email_log_kind`  (`kind`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;