-- Birthday Manager - Database Tables Creation SQL
-- Replace wp_ with your actual WordPress table prefix if different
-- Run this directly in phpMyAdmin or your MySQL client

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- Table 1: bm_birthdays - Main CRM table for birthday leads
CREATE TABLE IF NOT EXISTS `wp_bm_birthdays` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `wp_user_id` bigint(20) UNSIGNED NULL,
  `full_name` varchar(191) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `email` varchar(191) NOT NULL,
  `dob` date NOT NULL,
  `source` varchar(50) NOT NULL DEFAULT 'order',
  `source_ref` varchar(191) NULL,
  `preferred_venue` varchar(50) NULL,
  `experience_type` varchar(50) NULL,
  `group_size` int NULL,
  `budget_range` varchar(50) NULL,
  `birthday_year` int NULL,
  `birthday_status` varchar(50) NOT NULL DEFAULT 'new',
  `lead_score` int NOT NULL DEFAULT 0,
  `last_contacted_at` datetime NULL,
  `notes` longtext NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email` (`email`),
  KEY `phone` (`phone`),
  KEY `dob` (`dob`),
  KEY `birthday_status` (`birthday_status`),
  KEY `source` (`source`),
  KEY `wp_user_id` (`wp_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 2: bm_birthday_messages - Message log
CREATE TABLE IF NOT EXISTS `wp_bm_birthday_messages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `birthday_id` bigint(20) UNSIGNED NOT NULL,
  `direction` varchar(20) NOT NULL,
  `message_type` varchar(50) NOT NULL,
  `wati_message_id` varchar(191) NULL,
  `status` varchar(50) NULL,
  `payload` longtext NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `birthday_id` (`birthday_id`),
  KEY `direction` (`direction`),
  KEY `message_type` (`message_type`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 3: bm_birthday_bookings - Booking links
CREATE TABLE IF NOT EXISTS `wp_bm_birthday_bookings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `birthday_id` bigint(20) UNSIGNED NOT NULL,
  `venue` varchar(50) NOT NULL,
  `experience_type` varchar(50) NOT NULL,
  `event_date` datetime NOT NULL,
  `group_size` int NOT NULL,
  `booking_ref` varchar(191) NULL,
  `deposit_amount` decimal(10,2) NULL,
  `total_estimated` decimal(10,2) NULL,
  `status` varchar(50) NOT NULL DEFAULT 'pending',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `birthday_id` (`birthday_id`),
  KEY `venue` (`venue`),
  KEY `event_date` (`event_date`),
  KEY `status` (`status`),
  KEY `booking_ref` (`booking_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 4: bm_birthday_friends - Optional friends/guests table
CREATE TABLE IF NOT EXISTS `wp_bm_birthday_friends` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `birthday_id` bigint(20) UNSIGNED NOT NULL,
  `full_name` varchar(191) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `email` varchar(191) NULL,
  `preferred_venue` varchar(50) NULL,
  `experience_type` varchar(50) NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `birthday_id` (`birthday_id`),
  KEY `phone` (`phone`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

