-- ============================================================
-- Lumbini Province Taekwondo Association (LPTA) - tekendow_db
-- Full Database SQL Dump
-- Generated: 2026-06-15
-- Compatible with: MySQL 5.7+ / MariaDB 10.3+
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- ============================================================
-- Create & use database
-- ============================================================
CREATE DATABASE IF NOT EXISTS `lumbinip_taekwondo`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `lumbinip_taekwondo`;

-- ============================================================
-- DROP TABLES (in reverse FK order)
-- ============================================================
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `activity_logs`;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `documents`;
DROP TABLE IF EXISTS `achievements`;
DROP TABLE IF EXISTS `events`;
DROP TABLE IF EXISTS `gallery_items`;
DROP TABLE IF EXISTS `albums`;
DROP TABLE IF EXISTS `news`;
DROP TABLE IF EXISTS `news_categories`;
DROP TABLE IF EXISTS `coaches`;
DROP TABLE IF EXISTS `referees`;
DROP TABLE IF EXISTS `members`;
DROP TABLE IF EXISTS `sessions`;
DROP TABLE IF EXISTS `password_reset_tokens`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `clubs`;
DROP TABLE IF EXISTS `districts`;
DROP TABLE IF EXISTS `permission_role`;
DROP TABLE IF EXISTS `permissions`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `cache`;
DROP TABLE IF EXISTS `cache_locks`;
DROP TABLE IF EXISTS `jobs`;
DROP TABLE IF EXISTS `job_batches`;
DROP TABLE IF EXISTS `failed_jobs`;
DROP TABLE IF EXISTS `migrations`;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- TABLE: migrations (Laravel internal)
-- ============================================================
CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('0001_01_01_000000_create_users_table', 1),
('0001_01_01_000001_create_cache_table', 1),
('0001_01_01_000002_create_jobs_table', 1),
('2026_06_14_000001_create_association_tables', 1);

-- ============================================================
-- TABLE: cache
-- ============================================================
CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: cache_locks
-- ============================================================
CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: jobs (Laravel Queue)
-- ============================================================
CREATE TABLE `jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL,
  `reserved_at` int(10) UNSIGNED DEFAULT NULL,
  `available_at` int(10) UNSIGNED NOT NULL,
  `created_at` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 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,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: failed_jobs
-- ============================================================
CREATE TABLE `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: roles
-- ============================================================
CREATE TABLE `roles` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`id`, `name`, `display_name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'admin',            'Super Administrator',        'Has full control over the association website and database.',          NOW(), NOW()),
(2, 'editor',           'Content Editor',             'Can manage news, gallery, and download sections.',                    NOW(), NOW()),
(3, 'district_manager', 'District Manager',           'Can manage clubs, coaches, and members in their district.',           NOW(), NOW()),
(4, 'coach',            'Club Trainer/Coach',         'Can view their club members and recommend belt promotions.',           NOW(), NOW()),
(5, 'member',           'Registered Member/Athlete',  'Can view their profile, rank history, and print certificates.',        NOW(), NOW());

-- ============================================================
-- TABLE: permissions
-- ============================================================
CREATE TABLE `permissions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `permissions` (`id`, `name`, `display_name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'manage_settings',  'Manage Settings',   'Edit website metadata, address, contact info, logo.',                  NOW(), NOW()),
(2, 'manage_users',     'Manage Users',      'Create/Edit system administrators and editors.',                       NOW(), NOW()),
(3, 'manage_members',   'Manage Members',    'Approve, suspend, and search registered members.',                     NOW(), NOW()),
(4, 'manage_referees',  'Manage Referees',   'Add/Edit official referees and certifications.',                        NOW(), NOW()),
(5, 'manage_coaches',   'Manage Coaches',    'Add/Edit affiliated coaches and training qualifications.',              NOW(), NOW()),
(6, 'manage_news',      'Manage News',       'Create and publish news articles and announcements.',                   NOW(), NOW()),
(7, 'manage_events',    'Manage Events',     'Add matches, tournaments, and schedules.',                              NOW(), NOW()),
(8, 'manage_gallery',   'Manage Gallery',    'Create albums and upload photos/videos.',                               NOW(), NOW()),
(9, 'manage_downloads', 'Manage Downloads',  'Upload rules, guidelines, and application forms.',                      NOW(), NOW());

-- ============================================================
-- TABLE: permission_role  (pivot)
-- ============================================================
CREATE TABLE `permission_role` (
  `permission_id` bigint(20) UNSIGNED NOT NULL,
  `role_id` bigint(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`permission_id`, `role_id`),
  CONSTRAINT `permission_role_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `permission_role_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin (role_id=1) gets ALL permissions
INSERT INTO `permission_role` (`permission_id`, `role_id`) VALUES
(1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1),
-- Editor (role_id=2): news, events, gallery, downloads
(6, 2), (7, 2), (8, 2), (9, 2),
-- District Manager (role_id=3): members, coaches
(3, 3), (5, 3);

-- ============================================================
-- TABLE: districts
-- ============================================================
CREATE TABLE `districts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `districts_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `districts` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1,  'Rupandehi',       NOW(), NOW()),
(2,  'Kapilvastu',      NOW(), NOW()),
(3,  'Nawalparasi West',NOW(), NOW()),
(4,  'Palpa',           NOW(), NOW()),
(5,  'Arghakhanchi',    NOW(), NOW()),
(6,  'Gulmi',           NOW(), NOW()),
(7,  'Dang',            NOW(), NOW()),
(8,  'Pyuthan',         NOW(), NOW()),
(9,  'Rolpa',           NOW(), NOW()),
(10, 'Eastern Rukum',   NOW(), NOW()),
(11, 'Banke',           NOW(), NOW()),
(12, 'Bardiya',         NOW(), NOW());

-- ============================================================
-- TABLE: clubs
-- ============================================================
CREATE TABLE `clubs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `district_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clubs_district_id_foreign` (`district_id`),
  CONSTRAINT `clubs_district_id_foreign` FOREIGN KEY (`district_id`) REFERENCES `districts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `clubs` (`id`, `district_id`, `name`, `address`, `contact_person`, `contact_phone`, `created_at`, `updated_at`) VALUES
(1, 1,  'Butwal Taekwondo Dojang',     'Milanchowk, Butwal',              'Ram Prasad Shrestha', '9857012345', NOW(), NOW()),
(2, 1,  'Bhairahawa Taekwondo Academy','Siddharthanagar, Bhairahawa',     'Gita Rana',           '9857054321', NOW(), NOW()),
(3, 2,  'Taulihawa TKD Club',          'Taulihawa Bazar',                 'Suresh Chaudhary',    '9847067890', NOW(), NOW()),
(4, 7,  'Ghorahi Taekwondo Center',    'Ghorahi, Dang',                   'Janak Oli',           '9857811223', NOW(), NOW()),
(5, 11, 'Nepalgunj TKD Club',          'Surkhet Road, Nepalgunj',         'Bimal Budha',         '9848099887', NOW(), NOW());

-- ============================================================
-- TABLE: users
-- ============================================================
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_role_id_foreign` (`role_id`),
  CONSTRAINT `users_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default password for all seeded users: "password"
-- Hash generated with bcrypt (12 rounds)
INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `role_id`, `status`, `created_at`, `updated_at`) VALUES
(1, 'LPTA Administrator',    'admin@lumbinitkd.org',   NOW(), '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 'active', NOW(), NOW()),
(2, 'Ramesh Bhandari',       'editor@lumbinitkd.org',  NOW(), '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 2, 'active', NOW(), NOW()),
(3, 'Sunil Thapa',           'manager@lumbinitkd.org', NOW(), '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 3, 'active', NOW(), NOW()),
(4, 'Devendra Bahadur Singh','coach@lumbinitkd.org',   NOW(), '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 4, 'active', NOW(), NOW()),
(5, 'Roshan Shrestha',       'member@lumbinitkd.org',  NOW(), '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 5, 'active', NOW(), NOW());

-- ============================================================
-- TABLE: password_reset_tokens
-- ============================================================
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 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,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: members
-- ============================================================
CREATE TABLE `members` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `member_id` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `belt_rank` varchar(255) NOT NULL DEFAULT 'White',
  `district_id` bigint(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) UNSIGNED DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `qr_code_path` varchar(255) DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `expiry_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `members_member_id_unique` (`member_id`),
  KEY `members_user_id_foreign` (`user_id`),
  KEY `members_district_id_foreign` (`district_id`),
  KEY `members_club_id_foreign` (`club_id`),
  CONSTRAINT `members_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `members_district_id_foreign` FOREIGN KEY (`district_id`) REFERENCES `districts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `members_club_id_foreign` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `members` (`id`, `user_id`, `member_id`, `first_name`, `last_name`, `email`, `phone`, `gender`, `dob`, `address`, `belt_rank`, `district_id`, `club_id`, `photo`, `qr_code_path`, `status`, `expiry_date`, `created_at`, `updated_at`) VALUES
(1, 5, 'LPTA-00001', 'Roshan',    'Shrestha',  'member@lumbinitkd.org', '9812345678', 'Male',   '2004-05-15', 'Devinagar, Butwal',          '1st Dan Black Belt', 1, 1, NULL, NULL, 'active',  '2027-12-31', NOW(), NOW()),
(2, NULL, 'LPTA-00002', 'Saraswati', 'Adhikari',  'saraswati@gmail.com',  '9822334455', 'Female', '2006-08-20', 'Siddharthanagar, Bhairahawa', 'Red Belt',           1, 2, NULL, NULL, 'active',  '2026-12-31', NOW(), NOW()),
(3, NULL, 'LPTA-00003', 'Anil',      'Thapa',     'anil.thapa@gmail.com', '9818822334', 'Male',   '2005-02-10', 'Taulihawa, Kapilvastu',       'Blue Belt',          2, 3, NULL, NULL, 'active',  '2026-12-31', NOW(), NOW()),
(4, NULL, 'LPTA-00004', 'Bipin',     'Budha',     'bipin@gmail.com',      '9801122334', 'Male',   '2007-11-05', 'Nepalgunj, Banke',            'Green Belt',         11, 5, NULL, NULL, 'active',  '2026-12-31', NOW(), NOW()),
(5, NULL, 'LPTA-00005', 'Rupa',      'Chaudhary', 'rupa.c@gmail.com',     '9845011928', 'Female', '2008-03-12', 'Ghorahi, Dang',               'Yellow Belt',        7,  4, NULL, NULL, 'pending', NULL,         NOW(), NOW());

-- ============================================================
-- TABLE: referees
-- ============================================================
CREATE TABLE `referees` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `certification_level` varchar(255) NOT NULL,
  `experience_years` int(11) NOT NULL DEFAULT 0,
  `contact_phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `bio` text DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `referees` (`id`, `name`, `certification_level`, `experience_years`, `contact_phone`, `email`, `photo`, `bio`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Milan Shrestha',   'International Referee (IR) Class A', 16, '9857099887', 'milan.referee@gmail.com', NULL, 'Milan has officiated in world-level championships and is the chief referee coordinator for Lumbini Province.', 'active', NOW(), NOW()),
(2, 'Rina Thapa',       'National Referee Class B',           9,  '9847012987', 'rina.tkd@yahoo.com',      NULL, 'Rina is an experienced national level referee specializing in Poomsae judging.',                           'active', NOW(), NOW()),
(3, 'Suresh Chaudhary', 'National Referee Class C',           5,  '9847067890', 'suresh.referee@gmail.com',NULL, 'Suresh represents Kapilvastu District and has been active in local sparring judging for 5 years.',          'active', NOW(), NOW());

-- ============================================================
-- TABLE: coaches
-- ============================================================
CREATE TABLE `coaches` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `qualification` varchar(255) NOT NULL,
  `experience_years` int(11) NOT NULL DEFAULT 0,
  `achievements` text DEFAULT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `bio` text DEFAULT NULL,
  `club_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `coaches_club_id_foreign` (`club_id`),
  CONSTRAINT `coaches_club_id_foreign` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `coaches` (`id`, `name`, `qualification`, `experience_years`, `achievements`, `specialization`, `contact_phone`, `email`, `photo`, `bio`, `club_id`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Devendra Bahadur Singh', '4th Dan Black Belt, National Coach Course', 12, 'Gold Medalist in 7th National Games (2016). Trained over 300 black belts.', 'Kyorugi (Sparring) Tactics & Conditioning', '9857011223', 'coach@lumbinitkd.org',    NULL, 'Devendra is one of the most respected coaches in Rupandehi. He has served as a provincial coach in multiple national tournaments.', 1, 'active', NOW(), NOW()),
(2, 'Kabita Rana',            '3rd Dan Black Belt, Poomsae Instructor License', 7,  'Bronze Medalist in South Asian Games (2019). Special Award for Best Female Coach 2024.', 'Poomsae (Forms) & Flexibility Training', '9847055667', 'kabita@lumbinitkd.org', NULL, 'Kabita specializes in artistic forms and flexibilities. She heads the junior classes at Bhairahawa Taekwondo Academy.',                 2, 'active', NOW(), NOW()),
(3, 'Janak Oli',              '3rd Dan Black Belt', 8, 'National Referee, Coach at Lumbini Sports Club',                                              'Self Defense & Kid Taekwondo',          '9857811223', 'janak@lumbinitkd.org',  NULL, 'Janak heads the Ghorahi Taekwondo Center, promoting physical fitness and self-defense for youth in Dang.',                            4, 'active', NOW(), NOW());

-- ============================================================
-- TABLE: news_categories
-- ============================================================
CREATE TABLE `news_categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `news_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `news_categories` (`id`, `name`, `slug`, `created_at`, `updated_at`) VALUES
(1, 'Championships',   'championships',   NOW(), NOW()),
(2, 'Announcements',   'announcements',   NOW(), NOW()),
(3, 'Seminars',        'seminars',        NOW(), NOW()),
(4, 'Belts & Grading', 'belts-grading',   NOW(), NOW());

-- ============================================================
-- TABLE: news
-- ============================================================
CREATE TABLE `news` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `views` int(11) NOT NULL DEFAULT 0,
  `author_id` bigint(20) UNSIGNED NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'published',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `news_slug_unique` (`slug`),
  KEY `news_category_id_foreign` (`category_id`),
  KEY `news_author_id_foreign` (`author_id`),
  CONSTRAINT `news_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `news_categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `news_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `news` (`id`, `category_id`, `title`, `slug`, `content`, `image`, `is_featured`, `views`, `author_id`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 'Lumbini Province Selection Tournament 2026 Announced',
   'lumbini-province-selection-tournament-2026-announced',
   'The Lumbini Province Taekwondo Association (LPTA) is pleased to announce that the upcoming Provincial Selection Tournament will be held from July 15 to July 18, 2026, at the Butwal Covered Hall. The tournament will select athletes to represent Lumbini Province in the upcoming National Games. Athletes must be registered and approved by their respective districts. Registrations are open through our online Member Portal. Weigh-ins will take place on July 14. We encourage all coaches to prepare their players and register them on time.',
   NULL, 1, 125, 1, 'published', NOW(), NOW()),
(2, 4, 'Black Belt Grading Ceremony Held in Rupandehi Dojang',
   'black-belt-grading-ceremony-held-in-rupandehi-dojang',
   'A grand Black Belt Grading Ceremony was successfully conducted on June 10, 2026, at the Butwal Taekwondo Dojang. Over 45 junior and senior athletes were graded by the grandmaster panel led by Chief Instructor Devendra Bahadur Singh. The athletes were evaluated on basic techniques, sparring (Kyorugi), forms (Poomsae), self-defense, and breaking (Gyokpa). Ten players successfully obtained their 1st Dan Black Belt, while three athletes advanced to 2nd Dan. Congratulations to all successful athletes!',
   NULL, 0, 84, 1, 'published', NOW(), NOW()),
(3, 3, 'Referees Refresher Course Scheduled for August in Dang',
   'referees-refresher-course-scheduled-for-august-in-dang',
   'Lumbini Province Taekwondo Association is organizing a provincial Referee Refresher Course on August 5, 2026, in Ghorahi, Dang. This course is mandatory for all Class B and Class C referees in the province to update their knowledge regarding the latest World Taekwondo (WT) sparring competition rules. The course will be conducted by International Referee Milan Shrestha. Registration starts on July 1 and ends on July 25. Accommodations will be provided for out-district participants.',
   NULL, 1, 62, 2, 'published', NOW(), NOW()),
(4, 1, 'Lumbini Athletes Win 12 Medals in National Junior Championship',
   'lumbini-athletes-win-12-medals-in-national-junior-championship',
   'Lumbini Province Taekwondo team achieved an outstanding victory in the recently concluded National Junior Taekwondo Championship held in Kathmandu. The team secured 4 Gold, 5 Silver, and 3 Bronze medals, finishing as the second runner-up in the overall team standing. Roshan Shrestha won Gold in the junior male under-54kg division, while Saraswati Adhikari bagged Silver in Individual Poomsae. LPTA President congratulated the team and announced a cash reward program to support their development.',
   NULL, 0, 210, 2, 'published', NOW(), NOW());

-- ============================================================
-- TABLE: albums
-- ============================================================
CREATE TABLE `albums` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `cover_image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `albums` (`id`, `name`, `description`, `cover_image`, `created_at`, `updated_at`) VALUES
(1, 'Provincial Selection 2025', 'Memorable highlights and action moments from the 2025 Lumbini Selection Championship.', NULL, NOW(), NOW());

-- ============================================================
-- TABLE: gallery_items
-- ============================================================
CREATE TABLE `gallery_items` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `album_id` bigint(20) UNSIGNED NOT NULL,
  `type` varchar(255) NOT NULL DEFAULT 'image',
  `file_path` varchar(255) NOT NULL,
  `video_url` varchar(255) DEFAULT NULL,
  `caption` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `gallery_items_album_id_foreign` (`album_id`),
  CONSTRAINT `gallery_items_album_id_foreign` FOREIGN KEY (`album_id`) REFERENCES `albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `gallery_items` (`id`, `album_id`, `type`, `file_path`, `video_url`, `caption`, `created_at`, `updated_at`) VALUES
(1, 1, 'image', 'demo_action1.jpg', NULL, 'Gold medal match in progress', NOW(), NOW()),
(2, 1, 'image', 'demo_action2.jpg', NULL, 'Award ceremony with LPTA committee', NOW(), NOW()),
(3, 1, 'video', 'https://www.youtube.com/embed/dQw4w9WgXcQ', NULL, 'Championship final matches video highlight', NOW(), NOW());

-- ============================================================
-- TABLE: events
-- ============================================================
CREATE TABLE `events` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `venue` varchar(255) NOT NULL,
  `registration_status` varchar(255) NOT NULL DEFAULT 'closed',
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `events_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `events` (`id`, `title`, `slug`, `description`, `start_date`, `end_date`, `venue`, `registration_status`, `image`, `created_at`, `updated_at`) VALUES
(1, 'Lumbini Provincial Taekwondo Championship 2026',
   'lumbini-provincial-taekwondo-championship-2026',
   'Official selection championship for the upcoming National Games. Competitions will include Kyorugi (Sparring) and Poomsae (Forms) for Cadet, Junior, and Senior divisions.',
   '2026-07-15 09:00:00', '2026-07-18 17:00:00', 'Butwal Covered Hall, Rupandehi', 'open', NULL, NOW(), NOW()),
(2, 'Referees Refresher Course 2026',
   'referees-refresher-course-2026',
   'Mandatory rules update course for national and district referees. Highlights include video replay training, scorekeeper software usage, and penalty criteria updates.',
   '2026-08-05 08:00:00', '2026-08-05 18:00:00', 'Ghorahi Multipurpose Hall, Dang', 'open', NULL, NOW(), NOW()),
(3, 'District Belt Grading - Palpa & Gulmi',
   'district-belt-grading-palpa-gulmi',
   'Combined belt testing ceremony for Color Belts (Yellow to Red) from Palpa and Gulmi districts.',
   '2026-06-28 10:00:00', '2026-06-28 16:00:00', 'Tansen Covered Hall, Palpa', 'closed', NULL, NOW(), NOW());

-- ============================================================
-- TABLE: achievements
-- ============================================================
CREATE TABLE `achievements` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` varchar(255) NOT NULL DEFAULT 'national',
  `medal_type` varchar(255) NOT NULL DEFAULT 'none',
  `player_name` varchar(255) NOT NULL,
  `achievement_date` date NOT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `achievements` (`id`, `title`, `description`, `type`, `medal_type`, `player_name`, `achievement_date`, `photo`, `created_at`, `updated_at`) VALUES
(1, 'Gold Medal in 9th National Games',          'Won Gold in the Flyweight (under 54kg) sparring division, defeating Province 3 in the finals.',                                               'national',      'gold',   'Roshan Shrestha',   '2024-10-18', NULL, NOW(), NOW()),
(2, 'Silver Medal in National Poomsae Championship', 'Scored 7.65 points in individual female under-18 Poomsae category to secure the Silver medal.',                                          'national',      'silver', 'Saraswati Adhikari','2025-03-22', NULL, NOW(), NOW()),
(3, 'Bronze Medal in South Asian TKD Championship',  'Represented Nepal in the 68kg Sparring and secured Bronze after a tight semi-final against India.',                                      'international', 'bronze', 'Anil Thapa',        '2024-12-05', NULL, NOW(), NOW());

-- ============================================================
-- TABLE: documents
-- ============================================================
CREATE TABLE `documents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` varchar(255) NOT NULL DEFAULT 'pdf',
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `documents` (`id`, `title`, `file_path`, `file_type`, `description`, `created_at`, `updated_at`) VALUES
(1, 'New Member Registration Guideline Form 2026', 'downloads/member_form_2026.pdf',      'pdf',  'Complete PDF application form and details needed for obtaining official association membership.', NOW(), NOW()),
(2, 'LPTA Official Tournament Rules & Regulations 2026', 'downloads/tournament_rules_2026.pdf', 'pdf',  'Detailed competition guidelines, scoring protocols, and weight boundaries for provincial matches.', NOW(), NOW()),
(3, 'Dan Promotion Grading Application Form',      'downloads/dan_grading_form.docx',     'docx', 'Official application form required for Black Belt Dan promotions (1st Dan to 4th Dan).', NOW(), NOW());

-- ============================================================
-- TABLE: settings
-- ============================================================
CREATE TABLE `settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`key`, `value`, `created_at`, `updated_at`) VALUES
-- Site identity
('site_name',              'LPTA - Lumbini Province Taekwondo Association',  NOW(), NOW()),
('site_tagline',           'Uniting Athletes Across Lumbini Province',        NOW(), NOW()),
('header_logo_text',       'LPTA',                                            NOW(), NOW()),
('header_logo_tagline',    'Lumbini Province Taekwondo Association',          NOW(), NOW()),
('header_logo_icon',       '',                                                NOW(), NOW()),
('header_logo_img',        '',                                                NOW(), NOW()),
-- Contact info
('contact_address',        'Butwal-11, Rupandehi, Lumbini Province, Nepal',  NOW(), NOW()),
('contact_phone',          '+977-71-123456',                                  NOW(), NOW()),
('contact_email',          'info@lumbinitkd.org',                             NOW(), NOW()),
-- Social media
('social_facebook',        'https://facebook.com/lumbinitkd',                NOW(), NOW()),
('social_youtube',         'https://youtube.com/lumbinitkd',                 NOW(), NOW()),
('social_instagram',       '',                                                NOW(), NOW()),
('social_tiktok',          '',                                                NOW(), NOW()),
-- Footer
('footer_about',           'Lumbini Province Taekwondo Association (LPTA) is the governing body for Taekwondo in Lumbini Province, Nepal.', NOW(), NOW()),
('footer_copyright',       '2026 Lumbini Province Taekwondo Association. All rights reserved.', NOW(), NOW()),
-- Navigation menu (JSON array of {label, url})
('nav_menu_json', '[{"label":"Home","url":"/"},{"label":"About","url":"/about"},{"label":"Events","url":"/events"},{"label":"News","url":"/news"},{"label":"Gallery","url":"/gallery"},{"label":"Achievements","url":"/achievements"},{"label":"Tree","url":"/tree"},{"label":"Contact","url":"/contact"}]', NOW(), NOW()),
-- Stats offsets (added on top of live DB counts)
('stats_members_offset',   '140', NOW(), NOW()),
('stats_clubs_offset',     '0',   NOW(), NOW()),
('stats_coaches_offset',   '0',   NOW(), NOW()),
('stats_referees_offset',  '0',   NOW(), NOW()),
('stats_gold_offset',      '12',  NOW(), NOW()),
('stats_silver_offset',    '18',  NOW(), NOW()),
('stats_bronze_offset',    '25',  NOW(), NOW()),
('stats_districts_offset', '0',   NOW(), NOW());

-- ============================================================
-- TABLE: activity_logs
-- ============================================================
CREATE TABLE `activity_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_logs_user_id_foreign` (`user_id`),
  CONSTRAINT `activity_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample activity log entries
INSERT INTO `activity_logs` (`user_id`, `action`, `details`, `ip_address`, `created_at`, `updated_at`) VALUES
(1, 'login',           'Admin logged in successfully.',              '127.0.0.1', NOW(), NOW()),
(1, 'member_approved', 'Approved member LPTA-00001 (Roshan Shrestha).', '127.0.0.1', NOW(), NOW()),
(2, 'news_created',    'Created news: Lumbini Province Selection Tournament 2026 Announced.', '127.0.0.1', NOW(), NOW()),
(2, 'news_created',    'Created news: Referees Refresher Course Scheduled for August in Dang.', '127.0.0.1', NOW(), NOW());

-- ============================================================
-- AUTO_INCREMENT reset
-- ============================================================
ALTER TABLE `roles`           AUTO_INCREMENT = 6;
ALTER TABLE `permissions`     AUTO_INCREMENT = 10;
ALTER TABLE `districts`       AUTO_INCREMENT = 13;
ALTER TABLE `clubs`           AUTO_INCREMENT = 6;
ALTER TABLE `users`           AUTO_INCREMENT = 6;
ALTER TABLE `members`         AUTO_INCREMENT = 6;
ALTER TABLE `referees`        AUTO_INCREMENT = 4;
ALTER TABLE `coaches`         AUTO_INCREMENT = 4;
ALTER TABLE `news_categories` AUTO_INCREMENT = 5;
ALTER TABLE `news`            AUTO_INCREMENT = 5;
ALTER TABLE `albums`          AUTO_INCREMENT = 2;
ALTER TABLE `gallery_items`   AUTO_INCREMENT = 4;
ALTER TABLE `events`          AUTO_INCREMENT = 4;
ALTER TABLE `achievements`    AUTO_INCREMENT = 4;
ALTER TABLE `documents`       AUTO_INCREMENT = 4;
ALTER TABLE `settings`        AUTO_INCREMENT = 25;
ALTER TABLE `activity_logs`   AUTO_INCREMENT = 5;

-- ============================================================
-- COMMIT
-- ============================================================
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- ============================================================
-- END OF DUMP
-- CREDENTIALS (for reference):
--   DB:       tekendow_db
--   User:     root (XAMPP default)
--   Password: (empty)
--   All seeded user password: password
-- ============================================================