-- INVEETAIRE DATABASE SCHEMA
-- Generated from Physical Database Design (PDD v1.0)
-- Engine: InnoDB | Charset: utf8mb4

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Surrogate primary key',
    `name` VARCHAR(50) NOT NULL COMMENT 'Display name: Super Admin, Couple, Event Crew',
    `slug` VARCHAR(50) NOT NULL COMMENT 'Machine identifier: super_admin, couple, crew',
    `description` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Human-readable scope description',
    `is_system` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Always 1 — seeded roles cannot be deleted',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Seeded at deployment',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `event_types`;
CREATE TABLE `event_types` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Surrogate primary key',
    `name` VARCHAR(50) NOT NULL COMMENT 'Display name: Wedding, Engagement',
    `slug` VARCHAR(50) NOT NULL COMMENT 'Machine identifier: wedding, engagement',
    `description` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Human-readable scope description',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Whether new workspaces can use this type',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Seeded at deployment',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `commercial_plans`;
CREATE TABLE `commercial_plans` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Surrogate primary key',
    `name` VARCHAR(50) NOT NULL COMMENT 'Display name: Starter, Professional, Enterprise',
    `slug` VARCHAR(50) NOT NULL COMMENT 'Machine identifier: starter, professional, enterprise',
    `default_retention_days` SMALLINT UNSIGNED NOT NULL DEFAULT 180 COMMENT 'Days active post-wedding before archive',
    `default_archive_days` SMALLINT UNSIGNED NOT NULL DEFAULT 365 COMMENT 'Days archived before purge',
    `max_crew_accounts` TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT 'Max Event Crew accounts per workspace',
    `max_guests` SMALLINT UNSIGNED NOT NULL DEFAULT 1000 COMMENT 'Max guests per workspace',
    `max_gallery_images` TINYINT UNSIGNED NOT NULL DEFAULT 20 COMMENT 'Max gallery images per invitation',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Whether this plan is offered to new workspaces',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deprecated_at` DATETIME NULL DEFAULT NULL COMMENT 'NULL if still active',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `themes`;
CREATE TABLE `themes` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL COMMENT 'Display name',
    `slug` VARCHAR(100) NOT NULL COMMENT 'Maps to theme package directory name in /themes/',
    `description` TEXT NULL DEFAULT NULL COMMENT 'Human-readable theme description',
    `thumbnail_path` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Preview image path',
    `theme_status` VARCHAR(20) NOT NULL DEFAULT 'released' COMMENT 'released | deprecated | sunset',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deprecated_at` DATETIME NULL DEFAULT NULL COMMENT 'NULL if active',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `theme_versions`;
CREATE TABLE `theme_versions` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `theme_id` SMALLINT UNSIGNED NOT NULL COMMENT '→ themes.id',
    `version_number` VARCHAR(20) NOT NULL COMMENT 'Semantic version: 1.0, 1.1, 2.0. Immutable after release.',
    `is_breaking_change` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = structural change requiring config migration',
    `changelog` TEXT NULL DEFAULT NULL COMMENT 'What changed in this version',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = cannot be selected by new workspaces',
    `released_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `sunset_at` DATETIME NULL DEFAULT NULL COMMENT 'NULL if still active',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_theme_versions_theme_id` FOREIGN KEY (`theme_id`) REFERENCES `themes` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `theme_event_types`;
CREATE TABLE `theme_event_types` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `theme_version_id` SMALLINT UNSIGNED NOT NULL COMMENT '→ theme_versions.id',
    `event_type_id` TINYINT UNSIGNED NOT NULL COMMENT '→ event_types.id',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_theme_event_types_theme_version_id` FOREIGN KEY (`theme_version_id`) REFERENCES `theme_versions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_theme_event_types_event_type_id` FOREIGN KEY (`event_type_id`) REFERENCES `event_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `system_configurations`;
CREATE TABLE `system_configurations` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `config_key` VARCHAR(100) NOT NULL COMMENT 'Unique configuration key',
    `config_value` TEXT NOT NULL COMMENT 'Current value',
    `value_type` VARCHAR(10) NOT NULL DEFAULT 'text' COMMENT 'text | number | boolean | json',
    `description` VARCHAR(500) NULL DEFAULT NULL COMMENT 'What this setting controls',
    `updated_at` DATETIME NULL DEFAULT NULL,
    `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ users.id',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `workspaces`;
CREATE TABLE `workspaces` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Internal surrogate key',
    `slug` VARCHAR(60) NOT NULL COMMENT 'URL-safe public identifier. Immutable after creation.',
    `groom_name` VARCHAR(100) NOT NULL COMMENT 'Groom display name',
    `bride_name` VARCHAR(100) NOT NULL COMMENT 'Bride display name',
    `wedding_date` DATE NOT NULL COMMENT 'Primary event date. Governs phase transitions.',
    `event_type_id` TINYINT UNSIGNED NOT NULL COMMENT '→ event_types.id. Immutable after creation.',
    `commercial_plan_id` TINYINT UNSIGNED NOT NULL COMMENT '→ commercial_plans.id',
    `custom_guest_limit` SMALLINT UNSIGNED NULL DEFAULT NULL COMMENT 'Configurable guest limit for Custom plan',
    `workspace_status` VARCHAR(20) NOT NULL DEFAULT 'provisioned' COMMENT 'provisioned | setup | active | live | post_event | archived | purged',
    `created_by` BIGINT UNSIGNED NULL COMMENT '→ users.id (Super Admin who provisioned)',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `activated_at` DATETIME NULL DEFAULT NULL COMMENT 'First invitation sent timestamp',
    `wedding_day_at` DATETIME NULL DEFAULT NULL COMMENT 'Timestamp workspace entered Live phase',
    `archived_at` DATETIME NULL DEFAULT NULL COMMENT 'Timestamp workspace was archived',
    `purged_at` DATETIME NULL DEFAULT NULL COMMENT 'Timestamp workspace was purged',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_workspaces_slug` (`slug`),
    KEY `idx_workspaces_status` (`workspace_status`),
    KEY `idx_workspaces_wedding_date` (`wedding_date`),
    CONSTRAINT `fk_workspaces_event_type_id` FOREIGN KEY (`event_type_id`) REFERENCES `event_types` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_workspaces_commercial_plan_id` FOREIGN KEY (`commercial_plan_id`) REFERENCES `commercial_plans` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Internal surrogate key',
    `workspace_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ workspaces.id. NULL for Super Admin.',
    `role_id` TINYINT UNSIGNED NOT NULL COMMENT '→ roles.id',
    `email` VARCHAR(191) NULL DEFAULT NULL COMMENT 'Login email for Admin and Couple. NULL for Crew.',
    `username` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Login username for Crew. NULL for Admin/Couple.',
    `password_hash` VARCHAR(255) NOT NULL COMMENT 'bcrypt hash. Never plain text.',
    `display_name` VARCHAR(100) NOT NULL COMMENT 'Name shown in UI and audit logs',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = deactivated, cannot authenticate',
    `created_by` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ users.id. NULL for seeded Super Admin.',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `last_login_at` DATETIME NULL DEFAULT NULL COMMENT 'Last successful authentication timestamp',
    `deactivated_at` DATETIME NULL DEFAULT NULL COMMENT 'NULL if active',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_email` (`email`),
    KEY `idx_users_workspace_id` (`workspace_id`),
    KEY `idx_users_is_active` (`is_active`),
    CONSTRAINT `fk_users_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_users_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_users_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `retention_policies`;
CREATE TABLE `retention_policies` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id. One row per workspace.',
    `active_retention_days` SMALLINT UNSIGNED NOT NULL DEFAULT 180 COMMENT 'Days active post-wedding before archive',
    `archive_retention_days` SMALLINT UNSIGNED NOT NULL DEFAULT 365 COMMENT 'Days archived before purge',
    `is_overridden` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = manually overridden from plan default',
    `overridden_by` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ users.id (Super Admin). NULL if not overridden.',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_retention_workspace` (`workspace_id`),
    CONSTRAINT `fk_retention_policies_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_retention_policies_overridden_by` FOREIGN KEY (`overridden_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Internal surrogate',
    `session_token` VARCHAR(128) NOT NULL COMMENT 'Cryptographically random opaque token stored in cookie',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '→ users.id',
    `role_snapshot` VARCHAR(20) NOT NULL COMMENT 'Role at session creation: super_admin | couple | crew',
    `workspace_snapshot` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'workspace_id at login. NULL for Super Admin.',
    `ip_address` VARCHAR(45) NULL DEFAULT NULL COMMENT 'IPv4 or IPv6 address',
    `user_agent` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Browser/client string',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = expired or destroyed',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Login timestamp',
    `last_activity_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Updated on every authenticated request',
    `expires_at` DATETIME NOT NULL COMMENT 'Absolute expiry. Session invalid after this.',
    `destroyed_at` DATETIME NULL DEFAULT NULL COMMENT 'Set on explicit logout',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_sessions_token` (`session_token`),
    KEY `idx_sessions_user_id` (`user_id`),
    KEY `idx_sessions_expires_at` (`expires_at`),
    CONSTRAINT `fk_sessions_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `support_sessions`;
CREATE TABLE `support_sessions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `admin_user_id` BIGINT UNSIGNED NOT NULL COMMENT '→ users.id (Super Admin)',
    `target_workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `parent_session_id` BIGINT UNSIGNED NOT NULL COMMENT '→ sessions.id',
    `reason` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Optional note for audit clarity',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = currently in support mode',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Support mode entry timestamp',
    `closed_at` DATETIME NULL DEFAULT NULL COMMENT 'Support mode exit timestamp',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_support_sessions_admin_user_id` FOREIGN KEY (`admin_user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_support_sessions_target_workspace_id` FOREIGN KEY (`target_workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_support_sessions_parent_session_id` FOREIGN KEY (`parent_session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `crew_accounts`;
CREATE TABLE `crew_accounts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '→ users.id. One crew account per user.',
    `display_name` VARCHAR(100) NOT NULL COMMENT 'Name shown in check-in UI and audit logs',
    `desk_label` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Optional desk identifier: Desk A, VIP Reception',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = deactivated',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deactivated_at` DATETIME NULL DEFAULT NULL COMMENT 'NULL if active',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_crew_user_workspace` (`user_id`, `workspace_id`),
    CONSTRAINT `fk_crew_accounts_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_crew_accounts_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invitations`;
CREATE TABLE `invitations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id. Unique — one invitation per workspace.',
    `theme_version_id` SMALLINT UNSIGNED NOT NULL COMMENT '→ theme_versions.id. Pinned at selection.',
    `color_variant` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Selected color variant slug from theme manifest',
    `groom_display_name` VARCHAR(100) NOT NULL COMMENT 'Name shown on invitation',
    `bride_display_name` VARCHAR(100) NOT NULL COMMENT 'Name shown on invitation',
    `love_story` TEXT NULL DEFAULT NULL COMMENT 'Couple narrative text',
    `music_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether background music is active',
    `music_file_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ media_files.id. NULL if music disabled.',
    `rsvp_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether RSVP form is active',
    `rsvp_opens_at` DATETIME NULL DEFAULT NULL COMMENT 'RSVP form becomes available after this timestamp',
    `rsvp_closes_at` DATETIME NULL DEFAULT NULL COMMENT 'RSVP form closes at this timestamp',
    `rsvp_allow_edit` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Whether guests can edit RSVP before closing',
    `wish_wall_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Whether wish wall is shown',
    `gift_info_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether gift info section is shown',
    `gift_info_content` TEXT NULL DEFAULT NULL COMMENT 'Gift details, bank info, registry links',
    `invitation_status` VARCHAR(20) NOT NULL DEFAULT 'draft' COMMENT 'draft | configured | published | rsvp_open | rsvp_closed | archived',
    `published_at` DATETIME NULL DEFAULT NULL COMMENT 'First guest URL access timestamp',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_invitations_workspace` (`workspace_id`),
    KEY `idx_invitations_rsvp_closes` (`rsvp_closes_at`),
    CONSTRAINT `fk_invitations_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_invitations_theme_version_id` FOREIGN KEY (`theme_version_id`) REFERENCES `theme_versions` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invitation_sections`;
CREATE TABLE `invitation_sections` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `invitation_id` BIGINT UNSIGNED NOT NULL COMMENT '→ invitations.id',
    `section_slug` VARCHAR(50) NOT NULL COMMENT 'Theme section identifier: hero, story, gallery, venue, rsvp, wishes, gift, music',
    `is_visible` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Whether this section displays on the invitation',
    `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Display order within the invitation',
    `section_config` JSON NULL DEFAULT NULL COMMENT 'Section-specific configuration object',
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_invitation_sections_invitation_id` FOREIGN KEY (`invitation_id`) REFERENCES `invitations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `wedding_events`;
CREATE TABLE `wedding_events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `event_name` VARCHAR(100) NOT NULL COMMENT 'Ceremony name: Reception, Holy Matrimony, Akad Nikah, etc.',
    `event_date` DATETIME NOT NULL COMMENT 'Date and time of ceremony',
    `venue_name` VARCHAR(150) NOT NULL COMMENT 'Venue display name',
    `venue_address` TEXT NULL DEFAULT NULL COMMENT 'Full venue address',
    `maps_url` VARCHAR(1000) NULL DEFAULT NULL COMMENT 'Google Maps or similar URL',
    `dress_code` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Optional dress code instruction',
    `description` TEXT NULL DEFAULT NULL COMMENT 'Additional ceremony notes',
    `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Display order',
    `is_primary` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = primary/anchor event (Reception)',
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Soft delete flag',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `deleted_at` DATETIME NULL DEFAULT NULL COMMENT 'Soft delete timestamp',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_wedding_events_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `event_sessions`;
CREATE TABLE `event_sessions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `wedding_event_id` BIGINT UNSIGNED NOT NULL COMMENT '→ wedding_events.id',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `session_name` VARCHAR(100) NOT NULL DEFAULT 'Default Session' COMMENT 'e.g. Session 1, Session 2, Default Session',
    `start_time` DATETIME NOT NULL COMMENT 'Session start timestamp',
    `end_time` DATETIME NULL DEFAULT NULL COMMENT 'Optional session end timestamp',
    `venue_name` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Optional room/venue override',
    `venue_address` TEXT NULL DEFAULT NULL COMMENT 'Optional address override',
    `description` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Optional notes for guest',
    `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `is_default` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = default session for this wedding event',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_event_sessions_event` (`wedding_event_id`),
    KEY `idx_event_sessions_workspace` (`workspace_id`),
    CONSTRAINT `fk_event_sessions_wedding_event_id` FOREIGN KEY (`wedding_event_id`) REFERENCES `wedding_events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_event_sessions_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `guests`;
CREATE TABLE `guests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Internal ID — never in URLs',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `guest_code` VARCHAR(20) NOT NULL COMMENT 'Cryptographically random public token. *UQ scoped to workspace via unique index uq_guests_code_workspace.',
    `full_name` VARCHAR(150) NOT NULL COMMENT 'Guest full name. Used in greetings and check-in display.',
    `side` VARCHAR(10) NOT NULL COMMENT 'groom | bride | couple',
    `guest_group` VARCHAR(30) NOT NULL COMMENT 'family | relatives | friends | colleagues | church | business_partners | vip | others',
    `invited_pax` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Max attendees. Minimum 1.',
    `phone_number` VARCHAR(20) NULL DEFAULT NULL COMMENT 'WhatsApp-compatible number. Used for pre-fill links.',
    `notes` TEXT NULL DEFAULT NULL COMMENT 'Internal notes. Not visible to guest.',
    `qr_code_path` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Path to generated QR image file',
    `invitation_status` VARCHAR(10) NOT NULL DEFAULT 'not_sent' COMMENT 'not_sent | sent | opened',
    `rsvp_status` VARCHAR(10) NOT NULL DEFAULT 'pending' COMMENT 'pending | attending | declined',
    `checkin_status` VARCHAR(15) NOT NULL DEFAULT 'not_arrived' COMMENT 'not_arrived | checked_in | no_show',
    `first_opened_at` DATETIME NULL DEFAULT NULL COMMENT 'First invitation URL open timestamp',
    `last_opened_at` DATETIME NULL DEFAULT NULL COMMENT 'Most recent URL open — updated on every open',
    `invitation_sent_at` DATETIME NULL DEFAULT NULL COMMENT 'When couple first sent invitation to this guest',
    `rsvp_submitted_at` DATETIME NULL DEFAULT NULL COMMENT 'When guest submitted RSVP',
    `checked_in_at` DATETIME NULL DEFAULT NULL COMMENT 'When guest was checked in',
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Soft delete flag',
    `deleted_at` DATETIME NULL DEFAULT NULL COMMENT 'Soft delete timestamp',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_guests_code_workspace` (`workspace_id`, `guest_code`),
    KEY `idx_guests_workspace_id` (`workspace_id`),
    KEY `idx_guests_workspace_side` (`workspace_id`, `side`),
    KEY `idx_guests_workspace_group` (`workspace_id`, `guest_group`),
    KEY `idx_guests_rsvp_status` (`workspace_id`, `rsvp_status`),
    KEY `idx_guests_checkin_status` (`workspace_id`, `checkin_status`),
    KEY `idx_guests_invitation_status` (`workspace_id`, `invitation_status`),
    KEY `idx_guests_is_deleted` (`workspace_id`, `is_deleted`),
    KEY `idx_guests_full_name` (`workspace_id`, `full_name`),
    CONSTRAINT `fk_guests_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `guest_events`;
CREATE TABLE `guest_events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guest_id` BIGINT UNSIGNED NOT NULL COMMENT '→ guests.id',
    `wedding_event_id` BIGINT UNSIGNED NOT NULL COMMENT '→ wedding_events.id',
    `event_session_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ event_sessions.id. NULL = default session',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT 'Denormalized for isolation enforcement',
    `rsvp_status` VARCHAR(10) NOT NULL DEFAULT 'pending' COMMENT 'Per-event RSVP: pending | attending | declined',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_guest_events_session` (`event_session_id`),
    CONSTRAINT `fk_guest_events_guest_id` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_guest_events_wedding_event_id` FOREIGN KEY (`wedding_event_id`) REFERENCES `wedding_events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_guest_events_event_session_id` FOREIGN KEY (`event_session_id`) REFERENCES `event_sessions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `rsvps`;
CREATE TABLE `rsvps` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guest_id` BIGINT UNSIGNED NOT NULL COMMENT '→ guests.id. One RSVP per guest.',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT 'Denormalized workspace reference',
    `response` VARCHAR(10) NOT NULL COMMENT 'attending | declined',
    `confirmed_pax` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Confirmed attendees. 0 if declined. ≤ invited_pax.',
    `message` TEXT NULL DEFAULT NULL COMMENT 'Optional personal message from guest',
    `is_locked` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = locked after RSVP closing date',
    `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Initial submission timestamp',
    `updated_at` DATETIME NULL DEFAULT NULL COMMENT 'Most recent edit timestamp. NULL if never edited.',
    `locked_at` DATETIME NULL DEFAULT NULL COMMENT 'Timestamp when RSVP was locked',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_rsvp_guest` (`guest_id`),
    CONSTRAINT `fk_rsvps_guest_id` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `guest_timelines`;
CREATE TABLE `guest_timelines` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Sequential for ordering',
    `guest_id` BIGINT UNSIGNED NOT NULL COMMENT '→ guests.id',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT 'Denormalized',
    `event_type` VARCHAR(30) NOT NULL COMMENT 'created | invitation_generated | invitation_sent | opened | rsvp_submitted | reminder_sent | checked_in | no_show | archived',
    `event_detail` JSON NULL DEFAULT NULL COMMENT 'Structured detail about the event',
    `actor_type` VARCHAR(20) NOT NULL COMMENT 'system | couple | guest | crew',
    `actor_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Soft reference — not enforced FK',
    `actor_name` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Snapshot of actor name at event time',
    `occurred_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Immutable event timestamp',
    PRIMARY KEY (`id`),
    KEY `idx_guest_timelines_guest` (`guest_id`, `occurred_at`),
    CONSTRAINT `fk_guest_timelines_guest_id` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `wishes`;
CREATE TABLE `wishes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `guest_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ guests.id. NULL if submitted anonymously.',
    `sender_name` VARCHAR(100) NOT NULL COMMENT 'Name entered by submitter',
    `message` TEXT NOT NULL COMMENT 'Congratulatory message content',
    `is_visible` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = hidden by couple moderation',
    `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `hidden_at` DATETIME NULL DEFAULT NULL COMMENT 'NULL if visible',
    PRIMARY KEY (`id`),
    KEY `idx_wishes_workspace_visible` (`workspace_id`, `is_visible`, `submitted_at`),
    CONSTRAINT `fk_wishes_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_wishes_guest_id` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `checkins`;
CREATE TABLE `checkins` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guest_id` BIGINT UNSIGNED NOT NULL COMMENT '→ guests.id',
    `wedding_event_id` BIGINT UNSIGNED NOT NULL COMMENT '→ wedding_events.id',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT 'Denormalized',
    `operator_id` BIGINT UNSIGNED NOT NULL COMMENT '→ users.id (Crew or Couple)',
    `checkin_method` VARCHAR(10) NOT NULL COMMENT 'qr_scan | manual',
    `actual_pax` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Actual attendees at check-in. Minimum 1.',
    `has_angpao` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether guest brought gift envelopes',
    `angpao_count` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of envelopes. 0 if has_angpao = 0.',
    `notes` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Operator note',
    `is_correction` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether this corrects a previous check-in',
    `corrects_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ checkins.id. Self-reference for corrections.',
    `checked_in_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Immutable event timestamp',
    PRIMARY KEY (`id`),
    KEY `idx_checkins_workspace_event` (`workspace_id`, `wedding_event_id`),
    KEY `idx_checkins_guest_event` (`guest_id`, `wedding_event_id`),
    KEY `idx_checkins_checked_in_at` (`workspace_id`, `checked_in_at`),
    KEY `idx_checkins_has_angpao` (`workspace_id`, `has_angpao`),
    CONSTRAINT `fk_checkins_guest_id` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_checkins_wedding_event_id` FOREIGN KEY (`wedding_event_id`) REFERENCES `wedding_events` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_checkins_operator_id` FOREIGN KEY (`operator_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_checkins_corrects_id` FOREIGN KEY (`corrects_id`) REFERENCES `checkins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `label_print_jobs`;
CREATE TABLE `label_print_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `checkin_id` BIGINT UNSIGNED NOT NULL COMMENT '→ checkins.id',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT 'Denormalized',
    `template_type` VARCHAR(20) NOT NULL COMMENT 'angpao | souvenir | vip',
    `labels_generated` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Number of labels in this job',
    `is_acknowledged` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether operator confirmed print',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Print job trigger timestamp',
    `acknowledged_at` DATETIME NULL DEFAULT NULL COMMENT 'Print acknowledgement timestamp',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_label_print_jobs_checkin_id` FOREIGN KEY (`checkin_id`) REFERENCES `checkins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `vendors`;
CREATE TABLE `vendors` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `vendor_name` VARCHAR(150) NOT NULL COMMENT 'Vendor or business display name',
    `service_category` VARCHAR(50) NOT NULL COMMENT 'catering | photography | decoration | entertainment | venue | transportation | attire | documentation | other',
    `contact_name` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Primary contact person',
    `contact_phone` VARCHAR(20) NULL DEFAULT NULL COMMENT 'WhatsApp number',
    `contact_email` VARCHAR(191) NULL DEFAULT NULL COMMENT 'Email address',
    `agreed_budget` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Contracted or estimated cost',
    `actual_cost` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Final agreed cost. May differ from budget.',
    `notes` TEXT NULL DEFAULT NULL COMMENT 'Internal notes',
    `vendor_status` VARCHAR(15) NOT NULL DEFAULT 'planning' COMMENT 'planning | booked | completed | cancelled',
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Soft delete flag',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `deleted_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_vendors_workspace_status` (`workspace_id`, `vendor_status`),
    CONSTRAINT `fk_vendors_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `vendor_payments`;
CREATE TABLE `vendor_payments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT '→ vendors.id',
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT 'Denormalized',
    `milestone_name` VARCHAR(100) NOT NULL COMMENT 'Down Payment, Second Payment, Final Payment, Post-Event',
    `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Payment amount. Must be > 0.',
    `due_date` DATE NULL DEFAULT NULL COMMENT 'Expected payment date',
    `paid_date` DATE NULL DEFAULT NULL COMMENT 'Actual payment date. NULL if not yet paid.',
    `payment_method` VARCHAR(50) NULL DEFAULT NULL COMMENT 'bank_transfer, cash, etc. Optional.',
    `notes` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Payment notes',
    `is_paid` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = pending, 1 = paid',
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Soft delete flag',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_vendor_payments_due_date` (`workspace_id`, `due_date`, `is_paid`),
    CONSTRAINT `fk_vendor_payments_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `message_templates`;
CREATE TABLE `message_templates` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `template_type` VARCHAR(20) NOT NULL COMMENT 'invitation | h1_reminder | wedding_day_reminder',
    `body_text` TEXT NOT NULL COMMENT 'Message body with {{guest_name}} and {{invitation_url}} tokens',
    `is_default` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = customized by couple, 1 = system default wording',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_message_template_type` (`workspace_id`, `template_type`),
    CONSTRAINT `fk_message_templates_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `communication_logs`;
CREATE TABLE `communication_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `guest_id` BIGINT UNSIGNED NOT NULL COMMENT '→ guests.id',
    `template_id` BIGINT UNSIGNED NOT NULL COMMENT '→ message_templates.id',
    `template_type` VARCHAR(20) NOT NULL COMMENT 'Denormalized snapshot at send time',
    `sent_by` BIGINT UNSIGNED NOT NULL COMMENT '→ users.id',
    `delivery_status` VARCHAR(10) NOT NULL DEFAULT 'sent' COMMENT 'sent | delivered | read | failed',
    `sent_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Immutable',
    `delivered_at` DATETIME NULL DEFAULT NULL COMMENT 'Future WhatsApp API',
    `read_at` DATETIME NULL DEFAULT NULL COMMENT 'Future WhatsApp API',
    PRIMARY KEY (`id`),
    KEY `idx_comm_logs_guest_id` (`guest_id`, `template_type`),
    KEY `idx_comm_logs_workspace_sent` (`workspace_id`, `sent_at`),
    CONSTRAINT `fk_communication_logs_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_communication_logs_guest_id` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_communication_logs_template_id` FOREIGN KEY (`template_id`) REFERENCES `message_templates` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_communication_logs_sent_by` FOREIGN KEY (`sent_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `media_files`;
CREATE TABLE `media_files` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `original_filename` VARCHAR(255) NOT NULL COMMENT 'Filename as supplied. Display only — never used on disk.',
    `storage_path` VARCHAR(1000) NOT NULL COMMENT 'System-generated path in storage/uploads/. Never publicly exposed.',
    `file_category` VARCHAR(20) NOT NULL COMMENT 'profile_photo | gallery | hero | music | qr_code | export | report',
    `file_type` VARCHAR(100) NOT NULL COMMENT 'MIME type verified server-side',
    `file_size` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'File size in bytes',
    `processing_status` VARCHAR(15) NOT NULL DEFAULT 'pending' COMMENT 'pending | processing | ready | failed',
    `uploaded_by` BIGINT UNSIGNED NOT NULL COMMENT '→ users.id',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Upload timestamp',
    `processed_at` DATETIME NULL DEFAULT NULL COMMENT 'All variants generated timestamp',
    `deleted_at` DATETIME NULL DEFAULT NULL COMMENT 'NULL = active. Set on deletion.',
    PRIMARY KEY (`id`),
    KEY `idx_media_files_workspace` (`workspace_id`, `file_category`),
    CONSTRAINT `fk_media_files_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `media_variants`;
CREATE TABLE `media_variants` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `media_file_id` BIGINT UNSIGNED NOT NULL COMMENT '→ media_files.id',
    `variant_type` VARCHAR(20) NOT NULL COMMENT 'thumbnail | display | full_width | gallery',
    `storage_path` VARCHAR(1000) NOT NULL COMMENT 'On-disk path for this variant',
    `width` SMALLINT UNSIGNED NULL DEFAULT NULL COMMENT 'Pixel width',
    `height` SMALLINT UNSIGNED NULL DEFAULT NULL COMMENT 'Pixel height',
    `file_size` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Variant file size in bytes',
    `format` VARCHAR(10) NOT NULL COMMENT 'webp | jpeg | png',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Generation timestamp',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_media_variants_media_file_id` FOREIGN KEY (`media_file_id`) REFERENCES `media_files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `workspace_configurations`;
CREATE TABLE `workspace_configurations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `config_key` VARCHAR(100) NOT NULL COMMENT 'Must match a system_configurations key',
    `config_value` TEXT NOT NULL COMMENT 'Workspace-specific override value',
    `updated_at` DATETIME NULL DEFAULT NULL,
    `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ users.id',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_workspace_config_key` (`workspace_id`, `config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `printer_configurations`;
CREATE TABLE `printer_configurations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id. One row per workspace.',
    `angpao_label_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether angpao labels are generated post check-in',
    `souvenir_label_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether souvenir labels are generated',
    `vip_label_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether VIP labels are generated',
    `label_width_mm` SMALLINT UNSIGNED NULL DEFAULT NULL COMMENT 'Label width in millimetres',
    `label_height_mm` SMALLINT UNSIGNED NULL DEFAULT NULL COMMENT 'Label height in millimetres',
    `copies_per_checkin` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Default copies per check-in',
    `angpao_label_fields` JSON NULL DEFAULT NULL COMMENT 'Structured field definitions for angpao label',
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_printer_config_workspace` (`workspace_id`),
    CONSTRAINT `fk_printer_configurations_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `notification_type` VARCHAR(30) NOT NULL COMMENT 'rsvp_closing_soon | payment_due | wedding_approaching | low_invitation_rate | setup_incomplete',
    `priority` VARCHAR(10) NOT NULL DEFAULT 'normal' COMMENT 'critical | high | normal',
    `title` VARCHAR(150) NOT NULL COMMENT 'Short notification heading',
    `message` TEXT NOT NULL COMMENT 'Full notification message',
    `reference_type` VARCHAR(30) NULL DEFAULT NULL COMMENT 'vendor | invitation | workspace',
    `reference_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Soft reference to related entity. Not FK.',
    `is_read` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = unread, 1 = acknowledged',
    `expires_at` DATETIME NOT NULL COMMENT 'Notification irrelevant after this timestamp',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `read_at` DATETIME NULL DEFAULT NULL COMMENT 'Timestamp couple acknowledged',
    PRIMARY KEY (`id`),
    KEY `idx_notifications_workspace` (`workspace_id`, `is_read`, `expires_at`),
    CONSTRAINT `fk_notifications_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Sequential for ordering',
    `actor_type` VARCHAR(20) NOT NULL COMMENT 'super_admin | couple | crew | guest | system',
    `actor_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Soft reference — NOT enforced FK. Survives actor deletion.',
    `actor_display_name` VARCHAR(100) NULL DEFAULT NULL COMMENT 'Snapshot of actor name at event time',
    `workspace_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Soft reference. NULL for platform-level events.',
    `action_category` VARCHAR(20) NOT NULL COMMENT 'authentication | system | business | checkin | configuration | support | data_change',
    `action_type` VARCHAR(60) NOT NULL COMMENT 'Machine-readable event code: guest.imported, checkin.qr, vendor.payment.recorded',
    `target_entity_type` VARCHAR(30) NULL DEFAULT NULL COMMENT 'guest | vendor | workspace | invitation | checkin',
    `target_entity_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Soft reference to affected entity',
    `before_state` JSON NULL DEFAULT NULL COMMENT 'Entity state snapshot before action',
    `after_state` JSON NULL DEFAULT NULL COMMENT 'Entity state snapshot after action',
    `ip_address` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Request origin IP',
    `session_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Soft reference → sessions.id',
    `occurred_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'UTC event timestamp. Immutable.',
    PRIMARY KEY (`id`),
    KEY `idx_audit_logs_workspace` (`workspace_id`, `occurred_at`),
    KEY `idx_audit_logs_action_type` (`action_type`, `occurred_at`),
    KEY `idx_audit_logs_actor` (`actor_id`, `occurred_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DEFERRED FOREIGN KEY CONSTRAINTS (Circular dependencies resolver)

ALTER TABLE `workspaces` ADD CONSTRAINT `fk_workspaces_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `invitations` ADD CONSTRAINT `fk_invitations_music_file_id` FOREIGN KEY (`music_file_id`) REFERENCES `media_files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;


-- BUDGET PLANNING TABLES

DROP TABLE IF EXISTS `budget_payments`;
DROP TABLE IF EXISTS `budget_expenses`;
DROP TABLE IF EXISTS `budget_categories`;
DROP TABLE IF EXISTS `budget_settings`;

CREATE TABLE `budget_settings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `total_budget` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Configured workspace wedding budget',
    `currency` VARCHAR(10) NOT NULL DEFAULT 'IDR' COMMENT 'Budget currency representation',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_budget_settings_workspace` (`workspace_id`),
    CONSTRAINT `fk_budget_settings_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `budget_categories` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `name` VARCHAR(100) NOT NULL COMMENT 'Category name (Venue, Catering, etc)',
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Soft delete flag',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `deleted_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_budget_categories_workspace` (`workspace_id`, `is_deleted`),
    CONSTRAINT `fk_budget_categories_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `budget_expenses` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `category_id` BIGINT UNSIGNED NOT NULL COMMENT '→ budget_categories.id',
    `name` VARCHAR(255) NOT NULL COMMENT 'Expense description',
    `vendor_name` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Text-only vendor representation',
    `estimated_cost` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `actual_cost` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `status` VARCHAR(30) NOT NULL DEFAULT 'Planned' COMMENT 'Planned | Booked | Partially Paid | Paid | Cancelled',
    `payment_method` VARCHAR(50) NULL DEFAULT NULL,
    `notes` TEXT NULL DEFAULT NULL,
    `due_date` DATE NULL DEFAULT NULL,
    `reminder_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `reminder_date` DATE NULL DEFAULT NULL,
    `reminder_notes` TEXT NULL DEFAULT NULL,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `deleted_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_budget_expenses_workspace` (`workspace_id`, `category_id`, `is_deleted`),
    CONSTRAINT `fk_budget_expenses_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_budget_expenses_category_id` FOREIGN KEY (`category_id`) REFERENCES `budget_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `budget_payments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `expense_id` BIGINT UNSIGNED NOT NULL COMMENT '→ budget_expenses.id',
    `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `payment_date` DATE NOT NULL,
    `payment_method` VARCHAR(50) NULL DEFAULT NULL,
    `reference_no` VARCHAR(100) NULL DEFAULT NULL,
    `notes` TEXT NULL DEFAULT NULL,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_budget_payments_expense` (`workspace_id`, `expense_id`, `is_deleted`),
    CONSTRAINT `fk_budget_payments_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_budget_payments_expense_id` FOREIGN KEY (`expense_id`) REFERENCES `budget_expenses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- TIMELINE PLANNER TABLES

DROP TABLE IF EXISTS `timeline_milestones`;
DROP TABLE IF EXISTS `timeline_tasks`;
DROP TABLE IF EXISTS `timeline_phases`;

CREATE TABLE `timeline_phases` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `name` VARCHAR(100) NOT NULL COMMENT 'Phase title (e.g. 12+ Months, 1 Week)',
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_timeline_phases_workspace` (`workspace_id`),
    CONSTRAINT `fk_timeline_phases_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `timeline_tasks` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `phase_id` BIGINT UNSIGNED NOT NULL COMMENT '→ timeline_phases.id',
    `category` VARCHAR(50) NOT NULL COMMENT 'Venue | Church | Legal | Budget | etc',
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NULL DEFAULT NULL,
    `due_date` DATE NULL DEFAULT NULL,
    `priority` VARCHAR(20) NOT NULL DEFAULT 'Normal' COMMENT 'Low | Normal | High | Critical',
    `status` VARCHAR(20) NOT NULL DEFAULT 'Not Started' COMMENT 'Not Started | In Progress | Waiting | Completed | Cancelled',
    `owner` VARCHAR(100) NULL DEFAULT NULL,
    `estimated_duration` INT NULL DEFAULT NULL COMMENT 'Estimated duration in days',
    `notes` TEXT NULL DEFAULT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `reminder_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `reminder_date` DATE NULL DEFAULT NULL,
    `reminder_notes` TEXT NULL DEFAULT NULL,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `deleted_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_timeline_tasks_workspace` (`workspace_id`, `phase_id`, `is_deleted`),
    CONSTRAINT `fk_timeline_tasks_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_timeline_tasks_phase_id` FOREIGN KEY (`phase_id`) REFERENCES `timeline_phases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `timeline_milestones` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `title` VARCHAR(255) NOT NULL,
    `target_date` DATE NULL DEFAULT NULL,
    `is_completed` TINYINT(1) NOT NULL DEFAULT 0,
    `completed_at` DATETIME NULL DEFAULT NULL,
    `notes` TEXT NULL DEFAULT NULL,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_timeline_milestones_workspace` (`workspace_id`, `is_deleted`),
    CONSTRAINT `fk_timeline_milestones_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- VENDOR WORKSPACE TABLES

DROP TABLE IF EXISTS `vendor_communications`;
DROP TABLE IF EXISTS `vendor_quotations`;
DROP TABLE IF EXISTS `vendors`;
DROP TABLE IF EXISTS `vendor_categories`;

-- Drop foreign key columns if they exist to prevent schema recreation issues
ALTER TABLE `timeline_tasks` DROP FOREIGN KEY `fk_timeline_tasks_vendor_id`;
ALTER TABLE `timeline_tasks` DROP COLUMN `vendor_id`;
ALTER TABLE `budget_expenses` DROP FOREIGN KEY `fk_budget_expenses_vendor_id`;
ALTER TABLE `budget_expenses` DROP COLUMN `vendor_id`;

CREATE TABLE `vendor_categories` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `name` VARCHAR(100) NOT NULL COMMENT 'Category label (Venue, catering, etc)',
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_vendor_categories_workspace` (`workspace_id`, `is_deleted`),
    CONSTRAINT `fk_vendor_categories_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vendors` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
    `category_id` BIGINT UNSIGNED NOT NULL COMMENT '→ vendor_categories.id',
    `name` VARCHAR(255) NOT NULL,
    `company` VARCHAR(255) NULL DEFAULT NULL,
    `contact_person` VARCHAR(100) NULL DEFAULT NULL,
    `phone` VARCHAR(30) NULL DEFAULT NULL,
    `whatsapp` VARCHAR(30) NULL DEFAULT NULL,
    `email` VARCHAR(100) NULL DEFAULT NULL,
    `website` VARCHAR(255) NULL DEFAULT NULL,
    `instagram` VARCHAR(100) NULL DEFAULT NULL,
    `address` TEXT NULL DEFAULT NULL,
    `city` VARCHAR(100) NULL DEFAULT NULL,
    `notes` TEXT NULL DEFAULT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'Researching' COMMENT 'Researching | Contacted | Quoted | Negotiating | Booked | Completed | Cancelled',
    `contract_number` VARCHAR(100) NULL DEFAULT NULL,
    `contract_date` DATE NULL DEFAULT NULL,
    `contract_value` DECIMAL(15,2) NULL DEFAULT NULL,
    `contract_signed` TINYINT(1) NOT NULL DEFAULT 0,
    `budget_expense_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '→ budget_expenses.id',
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_vendors_workspace` (`workspace_id`, `is_deleted`),
    CONSTRAINT `fk_vendors_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_vendors_category_id` FOREIGN KEY (`category_id`) REFERENCES `vendor_categories` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vendor_quotations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT '→ vendors.id',
    `package_name` VARCHAR(255) NOT NULL,
    `quoted_price` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `negotiated_price` DECIMAL(15,2) NULL DEFAULT NULL,
    `valid_until` DATE NULL DEFAULT NULL,
    `notes` TEXT NULL DEFAULT NULL,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_vendor_quotations_vendor` (`vendor_id`, `is_deleted`),
    CONSTRAINT `fk_vendor_quotations_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vendor_communications` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vendor_id` BIGINT UNSIGNED NOT NULL COMMENT '→ vendors.id',
    `meeting_date` DATE NOT NULL,
    `channel` VARCHAR(20) NOT NULL COMMENT 'Call | WhatsApp | Email | Meeting',
    `notes` TEXT NULL DEFAULT NULL,
    `next_follow_up` DATE NULL DEFAULT NULL,
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_vendor_communications_vendor` (`vendor_id`, `is_deleted`),
    CONSTRAINT `fk_vendor_communications_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mapped Alter integration columns
ALTER TABLE `timeline_tasks` ADD COLUMN `vendor_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `phase_id`;
ALTER TABLE `timeline_tasks` ADD CONSTRAINT `fk_timeline_tasks_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `budget_expenses` ADD COLUMN `vendor_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `category_id`;
ALTER TABLE `budget_expenses` ADD CONSTRAINT `fk_budget_expenses_vendor_id` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS = 1;



CREATE TABLE IF NOT EXISTS `wedding_gift_settings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `invitation_id` BIGINT UNSIGNED NOT NULL COMMENT '-> invitations.id',
    `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `title` VARCHAR(150) NOT NULL DEFAULT 'Wedding Gift',
    `subtitle` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_invitation` (`invitation_id`),
    CONSTRAINT `fk_wg_settings_invitation` FOREIGN KEY (`invitation_id`) REFERENCES `invitations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wedding_gift_accounts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `gift_setting_id` BIGINT UNSIGNED NOT NULL COMMENT '-> wedding_gift_settings.id',
    `provider_name` VARCHAR(100) NOT NULL COMMENT 'Bank/Wallet Name e.g. BCA',
    `account_number` VARCHAR(100) NOT NULL,
    `account_holder` VARCHAR(150) NOT NULL,
    `account_type` VARCHAR(50) NOT NULL DEFAULT 'bank_transfer' COMMENT 'bank_transfer, qris, etc',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_wg_accounts_setting` FOREIGN KEY (`gift_setting_id`) REFERENCES `wedding_gift_settings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;