-- https://www.phpmyadmin.net/
--

START TRANSACTION;



-- --------------------------------------------------------

--
-- Table structure for table `confirmations`
--

CREATE TABLE `confirmations` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `contribution_id` bigint(20) UNSIGNED DEFAULT NULL,
  `gift_id` bigint(20) UNSIGNED DEFAULT NULL,
  `sent_to_phone` varchar(20) DEFAULT NULL,
  `sent_to_email` varchar(255) DEFAULT NULL,
  `sent_via` enum('sms','email','whatsapp') NOT NULL DEFAULT 'sms',
  `message_body` text DEFAULT NULL,
  `status` enum('sent','failed','pending') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `confirmations`
--

INSERT INTO `confirmations` (`id`, `contribution_id`, `gift_id`, `sent_to_phone`, `sent_to_email`, `sent_via`, `message_body`, `status`, `created_at`, `updated_at`) VALUES
(1, 9, NULL, '+255 712 345 678', NULL, 'sms', 'Asante Hamza Mwamposa! Mchango wako wa TZS 250,000 kwa Amina & Daniel umethibitishwa. - WeddingIS', 'sent', '2026-05-12 13:33:08', '2026-05-12 13:33:08'),
(2, 3, NULL, '+255743002156', NULL, 'sms', 'Asante Baraka Kimaro! Mchango wako wa TZS 50,000 kwa Amina & Daniel umethibitishwa. - WeddingIS', 'sent', '2026-05-12 13:33:17', '2026-05-12 13:33:17'),
(3, 7, NULL, '+255711444555', NULL, 'sms', 'Asante Peter Lema! Mchango wako wa TZS 30,000 kwa Amina & Daniel umethibitishwa. - WeddingIS', 'sent', '2026-05-12 13:33:20', '2026-05-12 13:33:20');

-- --------------------------------------------------------

--
-- Table structure for table `contributions`
--

CREATE TABLE `contributions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `event_id` bigint(20) UNSIGNED NOT NULL,
  `contributor_id` bigint(20) UNSIGNED DEFAULT NULL,
  `contributor_name` varchar(100) DEFAULT NULL,
  `contributor_phone` varchar(20) DEFAULT NULL,
  `type` enum('cash','gift') NOT NULL DEFAULT 'cash',
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `payment_method` enum('mpesa','airtel_money','tigopesa','cash','bank_transfer','other') NOT NULL DEFAULT 'cash',
  `payment_reference` varchar(100) DEFAULT NULL,
  `status` enum('pending','confirmed','rejected') NOT NULL DEFAULT 'pending',
  `recorded_by` bigint(20) UNSIGNED DEFAULT NULL,
  `confirmed_by` bigint(20) UNSIGNED DEFAULT NULL,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `contributions`
--

INSERT INTO `contributions` (`id`, `event_id`, `contributor_id`, `contributor_name`, `contributor_phone`, `type`, `amount`, `payment_method`, `payment_reference`, `status`, `recorded_by`, `confirmed_by`, `confirmed_at`, `notes`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 'Fatuma Ally', '+255712345678', 'cash', 150000.00, 'mpesa', 'MP2026A001', 'confirmed', 1, 1, '2026-05-10 08:21:39', NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(2, 1, NULL, 'Rose Njau', '+255769887341', 'cash', 200000.00, 'airtel_money', 'AM2026B002', 'confirmed', 1, 1, '2026-05-10 08:21:39', NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(3, 1, NULL, 'Baraka Kimaro', '+255743002156', 'cash', 50000.00, 'cash', NULL, 'confirmed', 1, 1, '2026-05-12 13:33:17', NULL, '2026-05-10 08:21:39', '2026-05-12 13:33:17'),
(4, 1, NULL, 'Sophia Laizer', '+255787541220', 'cash', 100000.00, 'mpesa', 'MP2026C004', 'confirmed', 1, 1, '2026-05-10 08:21:39', NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(5, 1, NULL, 'Hassan Msuya', '+255711222333', 'cash', 75000.00, 'tigopesa', 'TP2026D005', 'confirmed', 1, 1, '2026-05-10 08:21:39', NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(6, 1, NULL, 'Grace Mollel', '+255711333444', 'cash', 250000.00, 'bank_transfer', 'BT2026E006', 'confirmed', 1, 1, '2026-05-10 08:21:39', NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(7, 1, NULL, 'Peter Lema', '+255711444555', 'cash', 30000.00, 'cash', NULL, 'confirmed', 1, 1, '2026-05-12 13:33:20', NULL, '2026-05-10 08:21:39', '2026-05-12 13:33:20'),
(8, 1, NULL, 'Amina Hassan', '+255711555666', 'cash', 120000.00, 'mpesa', 'MP2026F008', 'confirmed', 1, 1, '2026-05-10 08:21:39', NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(9, 1, NULL, 'Hamza Mwamposa', '+255 712 345 678', 'cash', 250000.00, 'mpesa', 'MP0020949', 'confirmed', 1, 1, '2026-05-12 13:33:08', 'Saturday May 2026', '2026-05-12 13:32:46', '2026-05-12 13:33:08');

-- --------------------------------------------------------

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `couple_name` varchar(150) NOT NULL,
  `bride_name` varchar(80) DEFAULT NULL,
  `groom_name` varchar(80) DEFAULT NULL,
  `wedding_date` date NOT NULL,
  `venue` varchar(200) DEFAULT NULL,
  `target_budget` decimal(15,2) NOT NULL DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`, `couple_name`, `bride_name`, `groom_name`, `wedding_date`, `venue`, `target_budget`, `description`, `created_by`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Amina & Daniel', 'Amina Juma', 'Daniel Kimaro', '2026-06-14', 'Arusha, Tanzania', 7200000.00, NULL, 1, 1, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(2, 'nathan & nancy', 'Nancy bridal', 'Nathan nancy', '2026-08-01', 'SG Hotel', 100000000.00, NULL, 1, 1, '2026-05-17 06:30:01', '2026-05-17 06:30:01');

-- --------------------------------------------------------

--
-- Table structure for table `event_members`
--

CREATE TABLE `event_members` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `event_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `role` enum('admin','committee','viewer') NOT NULL DEFAULT 'viewer',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gifts`
--

CREATE TABLE `gifts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `event_id` bigint(20) UNSIGNED NOT NULL,
  `donor_id` bigint(20) UNSIGNED DEFAULT NULL,
  `donor_name` varchar(100) DEFAULT NULL,
  `donor_phone` varchar(20) DEFAULT NULL,
  `item_name` varchar(200) NOT NULL,
  `category` enum('kitchen_dining','bedroom_linen','electronics','furniture','clothing','other') NOT NULL DEFAULT 'other',
  `estimated_value` decimal(15,2) NOT NULL DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `status` enum('pledged','received','cancelled') NOT NULL DEFAULT 'pledged',
  `received_by` bigint(20) UNSIGNED DEFAULT NULL,
  `received_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `gifts`
--

INSERT INTO `gifts` (`id`, `event_id`, `donor_id`, `donor_name`, `donor_phone`, `item_name`, `category`, `estimated_value`, `description`, `status`, `received_by`, `received_at`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 'Mama Grace', '+255711000001', 'Dining Set (12 pcs)', 'kitchen_dining', 350000.00, NULL, 'received', 1, '2026-05-10 08:21:39', '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(2, 1, NULL, 'Uncle Hassan', '+255711000002', 'Bed Linen Set', 'bedroom_linen', 120000.00, NULL, 'pledged', NULL, NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(3, 1, NULL, 'Mwanaidi Juma', '+255711000003', 'Blender & Kettle', 'electronics', 180000.00, NULL, 'received', 1, '2026-05-10 08:21:39', '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(4, 1, NULL, 'Zawadi Msafiri', '+255711000004', 'Wall Mirror', 'furniture', 85000.00, NULL, 'pledged', NULL, NULL, '2026-05-10 08:21:39', '2026-05-10 08:21:39'),
(5, 1, NULL, 'Neema Tarimo', '+255711000005', 'Cutlery Set', 'kitchen_dining', 60000.00, NULL, 'received', 1, '2026-05-10 08:21:39', '2026-05-10 08:21:39', '2026-05-10 08:21:39');

-- --------------------------------------------------------

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` smallint(5) UNSIGNED NOT NULL,
  `reserved_at` int(10) UNSIGNED DEFAULT NULL,
  `available_at` int(10) UNSIGNED NOT NULL,
  `created_at` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '0001_01_01_000000_create_users_table', 1),
(2, '0001_01_01_000001_create_cache_table', 1),
(3, '0001_01_01_000002_create_jobs_table', 1),
(4, '2026_01_01_000002_create_events_table', 1),
(5, '2026_01_01_000003_create_contributions_gifts_table', 1);

-- --------------------------------------------------------

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sessions`
--

INSERT INTO `sessions` (`id`, `user_id`, `ip_address`, `user_agent`, `payload`, `last_activity`) VALUES
('kl7x1zTfTogkmsN4h6ZiRnnNBWQ3nmjhqirLGOKs', 1, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0', 'eyJfdG9rZW4iOiJOWnZHNElQZHpqNkJDcmpoM2o4WEQ5a2tPSFo1eWJGU3ZkMUFTRUlnIiwiX3ByZXZpb3VzIjp7InVybCI6Imh0dHA6XC9cLzEyNy4wLjAuMTo4MDAwXC9ldmVudHNcLzEiLCJyb3V0ZSI6ImV2ZW50cy5zaG93In0sIl9mbGFzaCI6eyJvbGQiOltdLCJuZXciOltdfSwibG9naW5fd2ViXzU5YmEzNmFkZGMyYjJmOTQwMTU4MGYwMTRjN2Y1OGVhNGUzMDk4OWQiOjF9', 1779010212);

--
-- Indexes for table `confirmations`
--
ALTER TABLE `confirmations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `confirmations_contribution_id_foreign` (`contribution_id`),
  ADD KEY `confirmations_gift_id_foreign` (`gift_id`);

--
-- Indexes for table `contributions`
--
ALTER TABLE `contributions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `contributions_event_id_foreign` (`event_id`),
  ADD KEY `contributions_contributor_id_foreign` (`contributor_id`),
  ADD KEY `contributions_recorded_by_foreign` (`recorded_by`),
  ADD KEY `contributions_confirmed_by_foreign` (`confirmed_by`);

--
-- Indexes for table `events`
--
ALTER TABLE `events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `events_created_by_foreign` (`created_by`);

--
-- Indexes for table `event_members`
--
ALTER TABLE `event_members`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `event_members_event_id_user_id_unique` (`event_id`,`user_id`),
  ADD KEY `event_members_user_id_foreign` (`user_id`);

--
-- Indexes for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`);

--
-- Indexes for table `gifts`
--
ALTER TABLE `gifts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `gifts_event_id_foreign` (`event_id`),
  ADD KEY `gifts_donor_id_foreign` (`donor_id`),
  ADD KEY `gifts_received_by_foreign` (`received_by`);

--
-- Indexes for table `jobs`
--
ALTER TABLE `jobs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `jobs_queue_index` (`queue`);

--
-- Indexes for table `job_batches`
--
ALTER TABLE `job_batches`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `migrations`
--
ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sessions_user_id_index` (`user_id`),
  ADD KEY `sessions_last_activity_index` (`last_activity`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `confirmations`
--
ALTER TABLE `confirmations`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `contributions`
--
ALTER TABLE `contributions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `event_members`
--
ALTER TABLE `event_members`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `gifts`
--
ALTER TABLE `gifts`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `jobs`
--
ALTER TABLE `jobs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `confirmations`
--
ALTER TABLE `confirmations`
  ADD CONSTRAINT `confirmations_contribution_id_foreign` FOREIGN KEY (`contribution_id`) REFERENCES `contributions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `confirmations_gift_id_foreign` FOREIGN KEY (`gift_id`) REFERENCES `gifts` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `contributions`
--
ALTER TABLE `contributions`
  ADD CONSTRAINT `contributions_confirmed_by_foreign` FOREIGN KEY (`confirmed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `contributions_contributor_id_foreign` FOREIGN KEY (`contributor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `contributions_event_id_foreign` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `contributions_recorded_by_foreign` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `events`
--
ALTER TABLE `events`
  ADD CONSTRAINT `events_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `event_members`
--
ALTER TABLE `event_members`
  ADD CONSTRAINT `event_members_event_id_foreign` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `event_members_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `gifts`
--
ALTER TABLE `gifts`
  ADD CONSTRAINT `gifts_donor_id_foreign` FOREIGN KEY (`donor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `gifts_event_id_foreign` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `gifts_received_by_foreign` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;
COMMIT;

