-- CAC Gambia Database Setup
-- Run this SQL in HeidiSQL to create all tables and seed data

-- Create database (if not exists)
-- CREATE DATABASE IF NOT EXISTS cacthegambia;
-- USE cacthegambia;

-- ============================================
-- TABLES
-- ============================================

-- Users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) NULL,
  `is_admin` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Departments table
CREATE TABLE IF NOT EXISTS `departments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `activities` text NULL,
  `age` varchar(255) NULL,
  `meeting` varchar(255) NULL,
  `image` varchar(255) NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `departments_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Menu items table
CREATE TABLE IF NOT EXISTS `menu_items` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NULL,
  `url` varchar(255) NOT NULL,
  `icon` varchar(255) NULL,
  `parent_id` bigint UNSIGNED NULL,
  `order` int NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  KEY `menu_items_parent_id_foreign` (`parent_id`),
  CONSTRAINT `menu_items_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Donation settings table
CREATE TABLE IF NOT EXISTS `donation_settings` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `bank_name` varchar(255) NULL,
  `account_name` varchar(255) NULL,
  `account_number` varchar(255) NULL,
  `sort_code` varchar(255) NULL,
  `mobile_money` varchar(255) NULL,
  `mobile_provider` varchar(255) NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SEED DATA
-- ============================================

-- Insert admin user (password: 12345 - bcrypt hash)
INSERT INTO `users` (`name`, `email`, `password`, `is_admin`, `created_at`, `updated_at`) VALUES
('Admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, NOW(), NOW());

-- Insert departments
INSERT INTO `departments` (`name`, `slug`, `description`, `activities`, `age`, `meeting`, `is_active`, `created_at`, `updated_at`) VALUES
('Men\'s Fellowship', 'mens-fellowship', 'The Men\'s Fellowship ministry is dedicated to strengthening the spiritual and physical man of God. We aim to raise godly men who will lead their families, church, and nation in righteousness.', 'Bible study, Prayer meetings, Community service, Men\'s conference', 'Adult men', 'Saturdays after first service', 1, NOW(), NOW()),
('Women\'s Fellowship', 'womens-fellowship', 'The Women\'s Fellowship is a ministry that caters to the spiritual growth and welfare of women in the church. We focus on holiness, prayer, and women empowerment.', 'Bible study, Prayer meetings, Women\'s conference, Charity outreach', 'Adult women', 'Saturdays after first service', 1, NOW(), NOW()),
('Youth Fellowship', 'youth-fellowship', 'The Youth Fellowship is designed to reach and disciple young people for Christ. We aim to raise a generation of youth who are bold for God and impactful in their spheres of influence.', 'Youth worship, Bible study, Sports, Evangelism, Retreats', '13-35 years', 'Fridays 6pm, Sundays after first service', 1, NOW(), NOW()),
('Children\'s Church', 'childrens-church', 'Our Children\'s Church ministry is dedicated to teaching children the Word of God in a fun and engaging way. We nurture young minds to know and love Jesus from an early age.', 'Sunday school, Bible stories, Songs, Crafts, Games', '3-12 years', 'Sundays during main service', 1, NOW(), NOW()),
('Choir & Music Ministry', 'choir-music-ministry', 'The Choir and Music Ministry leads the congregation in worship through songs. We aim to glorify God through excellence in music and worship.', 'Choir practice, Worship team rehearsal, Music training', 'All ages', 'Thursdays 6pm', 1, NOW(), NOW()),
('Prayer Band', 'prayer-band', 'The Prayer Band is a dedicated group of intercessors who stand in the gap for the church, nation, and world. We believe in the power of prayer.', 'All-night prayer, Morning intercession, Fasting', 'All ages', 'Daily', 1, NOW(), NOW()),
('Welfare Ministry', 'welfare-ministry', 'The Welfare Ministry cares for the physical needs of church members and the community. We provide assistance to those in need.', 'Food bank, Financial assistance, Hospital visits', 'All ages', 'As needed', 1, NOW(), NOW()),
('Evangelism Team', 'evangelism-team', 'The Evangelism Team is committed to sharing the gospel of Jesus Christ both locally and internationally. We reach the lost through various outreach programs.', 'Street evangelism, Hospital visitation, Prison ministry, Crusades', 'All ages', 'Monthly', 1, NOW(), NOW());

-- Insert menu items
INSERT INTO `menu_items` (`name`, `slug`, `url`, `icon`, `parent_id`, `order`, `is_active`, `created_at`, `updated_at`) VALUES
('Home', 'home', '/', 'fas fa-home', NULL, 1, 1, NOW(), NOW()),
('About', 'about', '/about', 'fas fa-info-circle', NULL, 2, 1, NOW(), NOW()),
('Branches', 'branches', '/branches', 'fas fa-map-marker-alt', NULL, 3, 1, NOW(), NOW()),
('Departments', 'departments', '/departments', 'fas fa-users', NULL, 4, 1, NOW(), NOW()),
('Pastors', 'pastors', '/pastors', 'fas fa-bible', NULL, 5, 1, NOW(), NOW()),
('Contact', 'contact', '/contact', 'fas fa-envelope', NULL, 6, 1, NOW(), NOW()),
('Donate', 'donate', '/donation', 'fas fa-donate', NULL, 7, 1, NOW(), NOW());

-- Insert donation settings
INSERT INTO `donation_settings` (`bank_name`, `account_name`, `account_number`, `sort_code`, `mobile_money`, `mobile_provider`, `created_at`, `updated_at`) VALUES
('EcoBank Gambia', 'Christ Apostolic Church Gambia', '0015096780001', 'ECOBANK', '+220 300 0000', 'EcoCash', NOW(), NOW());

-- ============================================
-- COMPLETE!
-- ============================================
