-- ============================================================
-- Database: political_aspirant_website
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- ============================================================

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

CREATE DATABASE IF NOT EXISTS `political_aspirant_website`
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE `political_aspirant_website`;

-- ============================================================
-- 1. roles
-- ============================================================
CREATE TABLE `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(80) NOT NULL,
  `slug` VARCHAR(80) NOT NULL,
  `description` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_roles_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`name`, `slug`, `description`) VALUES
('Super Administrator', 'super_admin',     'Full access to all features and settings'),
('Content Administrator','content_admin',  'Manages all content but cannot change system settings or users'),
('Project Manager',      'project_manager','Creates and manages project records'),
('Media Officer',        'media_officer',  'Manages gallery, videos and media library'),
('Community Liaison',    'community_liaison','Manages community requests and contacts'),
('Editor',               'editor',         'Creates and edits content; cannot publish without approval'),
('Viewer',               'viewer',         'Read-only access to the admin panel');

-- ============================================================
-- 2. permissions
-- ============================================================
CREATE TABLE `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `module` VARCHAR(60) NOT NULL,
  `description` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permissions_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `permissions` (`name`, `slug`, `module`) VALUES
('Manage Users',           'manage_users',           'users'),
('Manage Settings',        'manage_settings',        'settings'),
('Create Projects',        'create_projects',        'projects'),
('Edit Projects',          'edit_projects',          'projects'),
('Delete Projects',        'delete_projects',        'projects'),
('Approve Projects',       'approve_projects',       'projects'),
('Publish Projects',       'publish_projects',       'projects'),
('Create News',            'create_news',            'news'),
('Edit News',              'edit_news',              'news'),
('Approve News',           'approve_news',           'news'),
('Publish News',           'publish_news',           'news'),
('Delete News',            'delete_news',            'news'),
('Manage Events',          'manage_events',          'events'),
('Manage Gallery',         'manage_gallery',         'gallery'),
('Manage Videos',          'manage_videos',          'videos'),
('Manage Documents',       'manage_documents',       'documents'),
('Manage Requests',        'manage_requests',        'requests'),
('Export Requests',        'export_requests',        'requests'),
('View Contacts',          'view_contacts',          'contacts'),
('Reply Contacts',         'reply_contacts',         'contacts'),
('Manage Subscribers',     'manage_subscribers',     'subscribers'),
('Manage Testimonials',    'manage_testimonials',    'testimonials'),
('Approve Testimonials',   'approve_testimonials',   'testimonials'),
('Manage Priorities',      'manage_priorities',      'priorities'),
('Manage Speeches',        'manage_speeches',        'speeches'),
('Manage Communities',     'manage_communities',     'communities'),
('Manage Biography',       'manage_biography',       'biography'),
('Manage Aspirant Profile','manage_aspirant',        'aspirant'),
('View Audit Logs',        'view_audit_logs',        'audit'),
('Self Approve Content',   'self_approve_content',   'workflow');

-- ============================================================
-- 3. role_permissions
-- ============================================================
CREATE TABLE `role_permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_perm` (`role_id`, `permission_id`),
  KEY `fk_rp_role` (`role_id`),
  KEY `fk_rp_perm` (`permission_id`),
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Grant all permissions to super_admin (role_id=1)
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions`;

-- ============================================================
-- 4. users
-- ============================================================
CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `full_name` VARCHAR(160) NOT NULL,
  `email` VARCHAR(200) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role_id` INT UNSIGNED NOT NULL DEFAULT 7,
  `status` ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `must_change_password` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login` DATETIME,
  `last_login_ip` VARCHAR(45),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`),
  UNIQUE KEY `uq_users_uuid` (`uuid`),
  KEY `fk_users_role` (`role_id`),
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default super-admin account (password: ChangeMeImmediately!2026)
INSERT INTO `users` (`uuid`, `full_name`, `email`, `password`, `role_id`, `status`, `must_change_password`) VALUES
(UUID(), 'System Administrator', 'admin@example.com',
 '$2y$12$ZtfJ32EPvcoKfn6QSKQFxei0s6SZNl7mhYavmfXA0AmJ8ZU0HiK/a', 1, 'active', 1);

-- ============================================================
-- 5. login_attempts
-- ============================================================
CREATE TABLE `login_attempts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(200) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` VARCHAR(500),
  `success` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_la_email` (`email`),
  KEY `idx_la_ip` (`ip_address`),
  KEY `idx_la_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. password_reset_tokens
-- ============================================================
CREATE TABLE `password_reset_tokens` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_prt_user` (`user_id`),
  KEY `idx_prt_token` (`token`),
  CONSTRAINT `fk_prt_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. audit_logs
-- ============================================================
CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `action` VARCHAR(120) NOT NULL,
  `module` VARCHAR(80) NOT NULL,
  `record_id` INT UNSIGNED DEFAULT NULL,
  `old_values` JSON DEFAULT NULL,
  `new_values` JSON DEFAULT NULL,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_al_user` (`user_id`),
  KEY `idx_al_module` (`module`),
  KEY `idx_al_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. site_settings
-- ============================================================
CREATE TABLE `site_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(120) NOT NULL,
  `setting_value` TEXT,
  `setting_group` VARCHAR(80) NOT NULL DEFAULT 'general',
  `is_public` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_by` INT UNSIGNED DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ss_key` (`setting_key`),
  KEY `idx_ss_group` (`setting_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `site_settings` (`setting_key`, `setting_value`, `setting_group`, `is_public`) VALUES
('site_name',             'Kubau/Ikara Federal Constituency', 'general', 1),
('site_tagline',          'Public Service, Administrative Experience and Community Development', 'general', 1),
('site_email',            '[OFFICIAL EMAIL ADDRESS]', 'general', 1),
('site_phone',            '[OFFICIAL PHONE NUMBER]', 'general', 1),
('site_address',          '[OFFICIAL OFFICE ADDRESS]', 'general', 1),
('primary_color',         '#0B6B3A', 'appearance', 1),
('deep_green',            '#064B2A', 'appearance', 1),
('gold_accent',           '#D4A017', 'appearance', 1),
('dark_navy',             '#10243E', 'appearance', 1),
('off_white',             '#F8F9F5', 'appearance', 1),
('logo_path',             '', 'appearance', 1),
('facebook_url',          '[FACEBOOK PROFILE URL]', 'social', 1),
('twitter_url',           '[TWITTER PROFILE URL]', 'social', 1),
('instagram_url',         '[INSTAGRAM PROFILE URL]', 'social', 1),
('youtube_url',           '[YOUTUBE CHANNEL URL]', 'social', 1),
('whatsapp_url',          '[WHATSAPP CONTACT URL]', 'social', 1),
('meta_description',      'Official website documenting the public service record, community development projects and leadership experience of [ASPIRANT FULL NAME], political aspirant for Kubau/Ikara Federal Constituency.', 'seo', 1),
('meta_keywords',         'Kubau, Ikara, Kaduna, Federal Constituency, community development, projects', 'seo', 1),
('google_analytics_id',   '', 'analytics', 0),
('enable_analytics',      '1', 'analytics', 0),
('cookie_notice_text',    'This website uses cookies to improve your experience. By continuing to browse you accept our privacy policy.', 'privacy', 1),
('maintenance_mode',      '0', 'general', 0),
('maintenance_message',   'The website is temporarily undergoing maintenance. Please check back shortly.', 'general', 1),
('smtp_host',             '', 'email', 0),
('smtp_port',             '587', 'email', 0),
('smtp_user',             '', 'email', 0),
('smtp_pass',             '', 'email', 0),
('smtp_from_email',       '', 'email', 0),
('smtp_from_name',        '', 'email', 0),
('double_optin',          '1', 'email', 0),
('items_per_page',        '12', 'general', 0),
('developer_credit',      '', 'general', 1),
('data_retention_days',   '365', 'privacy', 0),
('show_stats_strip',      '1', 'general', 1),
('years_public_service',  '', 'stats', 1),
('completed_projects',    '', 'stats', 1),
('communities_reached',   '', 'stats', 1),
('ongoing_initiatives',   '', 'stats', 1),
('stats_verified',        '0', 'stats', 0);

-- ============================================================
-- 9. translations
-- ============================================================
CREATE TABLE `translations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lang` CHAR(5) NOT NULL,
  `t_key` VARCHAR(200) NOT NULL,
  `t_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_trans_lang_key` (`lang`, `t_key`),
  KEY `idx_trans_lang` (`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `translations` (`lang`, `t_key`, `t_value`) VALUES
('ha', 'nav_home',              'Gida'),
('ha', 'nav_about',             'Game da shi'),
('ha', 'nav_journey',           'Tafiyar Jagoranci'),
('ha', 'nav_projects',          'Ayyuka'),
('ha', 'nav_priorities',        'Muhimman Batutuwa'),
('ha', 'nav_communities',       'Al''umma'),
('ha', 'nav_news',              'Labarai'),
('ha', 'nav_media',             'Kafofin Watsa Labarai'),
('ha', 'nav_events',            'Abubuwan da ke zuwa'),
('ha', 'nav_contact',           'Tuntuba'),
('ha', 'nav_submit_request',    'Aika Bukata'),
('ha', 'hero_title',            '[CIKAKKEN SUNA]'),
('ha', 'hero_subtitle',         'Tsohon Shugaban Majalisar Kubau · Tsohon Babban Akawu na Jihar Kaduna'),
('ha', 'translation_pending',   'Ana shirya fassarar Hausa.');

-- ============================================================
-- 10. aspirant_profile
-- ============================================================
CREATE TABLE `aspirant_profile` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(200) NOT NULL DEFAULT '[ASPIRANT FULL NAME]',
  `full_name_ha` VARCHAR(200),
  `title` VARCHAR(60),
  `date_of_birth` DATE,
  `state_of_origin` VARCHAR(80),
  `lga_of_origin` VARCHAR(120),
  `constituency` VARCHAR(160) NOT NULL DEFAULT 'Kubau/Ikara Federal Constituency',
  `phone` VARCHAR(30),
  `email` VARCHAR(200),
  `office_address` TEXT,
  `profile_photo` VARCHAR(500),
  `profile_document` VARCHAR(500),
  `brief_bio_en` TEXT,
  `brief_bio_ha` TEXT,
  `campaign_slogan_en` VARCHAR(300),
  `campaign_slogan_ha` VARCHAR(300),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aspirant_profile`
  (`full_name`, `constituency`, `brief_bio_en`) VALUES
('[ASPIRANT FULL NAME]',
 'Kubau/Ikara Federal Constituency',
 '[ASPIRANT FULL NAME] has served in public leadership and financial administration, including service as Chairman of Kubau Local Government Area and Accountant-General of Kaduna State. This platform provides access to his verified professional background, community engagements and development records.');

-- ============================================================
-- 11. biography_sections
-- ============================================================
CREATE TABLE `biography_sections` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `section_key` VARCHAR(80) NOT NULL,
  `title_en` VARCHAR(200) NOT NULL,
  `title_ha` VARCHAR(200),
  `content_en` LONGTEXT,
  `content_ha` LONGTEXT,
  `display_order` SMALLINT NOT NULL DEFAULT 0,
  `status` ENUM('published','draft') NOT NULL DEFAULT 'draft',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bio_key` (`section_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `biography_sections` (`section_key`, `title_en`, `display_order`, `status`) VALUES
('early_life',         'Early Life',                                    1, 'draft'),
('education',          'Education',                                     2, 'draft'),
('professional',       'Professional Career',                          3, 'draft'),
('kubau_chairmanship', 'Chairman of Kubau Local Government Area',      4, 'draft'),
('accountant_general', 'Accountant-General of Kaduna State',           5, 'draft'),
('community',          'Community Engagement',                         6, 'draft'),
('aspiration',         'Current Political Aspiration',                 7, 'draft');

-- ============================================================
-- 12. aspirant_biography  (single row, id = 1)
-- ============================================================
CREATE TABLE `aspirant_biography` (
  `id`                   INT UNSIGNED NOT NULL,
  `bio_en`               LONGTEXT,
  `bio_ha`               LONGTEXT,
  `date_of_birth`        DATE,
  `state_of_origin`      VARCHAR(100),
  `lga_of_origin`        VARCHAR(100),
  `education_summary_en` TEXT,
  `education_summary_ha` TEXT,
  `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aspirant_biography` (`id`, `created_at`, `updated_at`) VALUES (1, NOW(), NOW());

-- ============================================================
-- 13. aspirant_experiences
-- ============================================================
CREATE TABLE `aspirant_experiences` (
  `id`                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `type`                VARCHAR(30)   NOT NULL,
  `title_en`            VARCHAR(300)  NOT NULL,
  `title_ha`            VARCHAR(300),
  `organisation_en`     VARCHAR(300),
  `organisation_ha`     VARCHAR(300),
  `start_year`          VARCHAR(10),
  `end_year`            VARCHAR(10),
  `is_current`          TINYINT(1)    NOT NULL DEFAULT 0,
  `description_en`      LONGTEXT,
  `description_ha`      LONGTEXT,
  `verification_status` VARCHAR(40)   NOT NULL DEFAULT 'unverified',
  `sort_order`          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ae_type` (`type`),
  KEY `idx_ae_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. leadership_experiences
-- ============================================================
CREATE TABLE `leadership_experiences` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `position_title_en` VARCHAR(200) NOT NULL,
  `position_title_ha` VARCHAR(200),
  `organisation_en` VARCHAR(200) NOT NULL,
  `organisation_ha` VARCHAR(200),
  `start_date` DATE,
  `end_date` DATE,
  `is_current` TINYINT(1) NOT NULL DEFAULT 0,
  `summary_en` TEXT,
  `summary_ha` TEXT,
  `responsibilities_en` LONGTEXT,
  `responsibilities_ha` LONGTEXT,
  `outcomes_en` LONGTEXT,
  `outcomes_ha` LONGTEXT,
  `image` VARCHAR(500),
  `display_order` SMALLINT NOT NULL DEFAULT 0,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_status` ENUM('unverified','under_verification','verified_internally','verified_with_docs','independently_confirmed','disputed','withdrawn') NOT NULL DEFAULT 'unverified',
  `publication_status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_by` INT UNSIGNED,
  `approved_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_le_order` (`display_order`),
  KEY `idx_le_status` (`publication_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `leadership_experiences`
  (`position_title_en`, `organisation_en`, `is_current`, `display_order`, `publication_status`) VALUES
('[EARLY CAREER POSITION]',            '[ORGANISATION NAME]',              0, 1, 'draft'),
('Chairman',                           'Kubau Local Government Area',      0, 2, 'draft'),
('Accountant-General',                 'Kaduna State Government',          0, 3, 'draft'),
('Political Aspirant — Federal Constituency', 'Kubau/Ikara',              1, 4, 'draft');

-- ============================================================
-- 13. priorities
-- ============================================================
CREATE TABLE `priorities` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title_en` VARCHAR(200) NOT NULL,
  `title_ha` VARCHAR(200),
  `slug` VARCHAR(350),
  `icon` VARCHAR(80) NOT NULL DEFAULT 'bi-star',
  `colour` VARCHAR(20),
  `image` VARCHAR(500),
  `summary_en` TEXT,
  `summary_ha` TEXT,
  `description_en` LONGTEXT,
  `description_ha` LONGTEXT,
  `challenge_en` LONGTEXT,
  `challenge_ha` LONGTEXT,
  `approach_en` LONGTEXT,
  `approach_ha` LONGTEXT,
  `outcomes_en` LONGTEXT,
  `outcomes_ha` LONGTEXT,
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `publication_status` ENUM('draft','approved','published','archived') NOT NULL DEFAULT 'draft',
  `created_by` INT UNSIGNED,
  `approved_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_priorities_slug` (`slug`),
  KEY `idx_pri_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `priorities`
  (`title_en`, `icon`, `summary_en`, `display_order`, `publication_status`) VALUES
('Education',               'bi-book',          'Improving access to quality education in Kubau and Ikara.', 1, 'draft'),
('Healthcare',              'bi-heart-pulse',    'Expanding healthcare access and facilities.', 2, 'draft'),
('Youth Empowerment',       'bi-people',         'Creating skills and employment pathways for youth.', 3, 'draft'),
('Agriculture',             'bi-flower1',        'Supporting farmers with resources, markets and technology.', 4, 'draft'),
('Rural Infrastructure',    'bi-signpost-2',     'Improving roads, drainage and public facilities.', 5, 'draft'),
('Water Supply',            'bi-droplet',        'Expanding safe water access across communities.', 6, 'draft'),
('Women''s Economic Inclusion','bi-gender-female','Supporting women''s economic participation.', 7, 'draft'),
('Digital Skills',          'bi-laptop',         'Building digital literacy and connectivity.', 8, 'draft'),
('Security Collaboration',  'bi-shield-check',   'Supporting community safety initiatives.', 9, 'draft'),
('Accountable Representation','bi-clipboard-check','Transparent and responsive representation.', 10, 'draft');

-- ============================================================
-- 14. local_governments
-- ============================================================
CREATE TABLE `local_governments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `state` VARCHAR(80) NOT NULL DEFAULT 'Kaduna',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `local_governments` (`name`, `state`) VALUES
('Kubau', 'Kaduna'),
('Ikara', 'Kaduna');

-- ============================================================
-- 15. wards
-- ============================================================
CREATE TABLE `wards` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lga_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_wards_lga` (`lga_id`),
  CONSTRAINT `fk_wards_lga` FOREIGN KEY (`lga_id`) REFERENCES `local_governments`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kubau wards (LGA id=1) — placeholder names
INSERT INTO `wards` (`lga_id`, `name`) VALUES
(1,'[KUBAU WARD 1]'),(1,'[KUBAU WARD 2]'),(1,'[KUBAU WARD 3]'),
(1,'[KUBAU WARD 4]'),(1,'[KUBAU WARD 5]'),(1,'[KUBAU WARD 6]'),
(1,'[KUBAU WARD 7]'),(1,'[KUBAU WARD 8]'),(1,'[KUBAU WARD 9]'),(1,'[KUBAU WARD 10]');
-- Ikara wards (LGA id=2)
INSERT INTO `wards` (`lga_id`, `name`) VALUES
(2,'[IKARA WARD 1]'),(2,'[IKARA WARD 2]'),(2,'[IKARA WARD 3]'),
(2,'[IKARA WARD 4]'),(2,'[IKARA WARD 5]'),(2,'[IKARA WARD 6]'),
(2,'[IKARA WARD 7]'),(2,'[IKARA WARD 8]'),(2,'[IKARA WARD 9]'),(2,'[IKARA WARD 10]');

-- ============================================================
-- 16. communities
-- ============================================================
CREATE TABLE `communities` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lga_id` INT UNSIGNED NOT NULL,
  `ward_id` INT UNSIGNED,
  `name_en` VARCHAR(120) NOT NULL,
  `name_ha` VARCHAR(120),
  `description_en` TEXT,
  `description_ha` TEXT,
  `latitude` DECIMAL(10,8),
  `longitude` DECIMAL(11,8),
  `image` VARCHAR(500),
  `publication_status` ENUM('published','draft') NOT NULL DEFAULT 'draft',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_comm_lga` (`lga_id`),
  KEY `fk_comm_ward` (`ward_id`),
  CONSTRAINT `fk_comm_lga` FOREIGN KEY (`lga_id`) REFERENCES `local_governments`(`id`),
  CONSTRAINT `fk_comm_ward` FOREIGN KEY (`ward_id`) REFERENCES `wards`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 17. project_categories
-- ============================================================
CREATE TABLE `project_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `name_ha` VARCHAR(100),
  `icon` VARCHAR(80) NOT NULL DEFAULT 'bi-building',
  `color` VARCHAR(20) NOT NULL DEFAULT '#0B6B3A',
  `display_order` SMALLINT NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `project_categories` (`name`, `icon`, `color`, `display_order`) VALUES
('Education',          'bi-book',           '#0B6B3A', 1),
('Healthcare',         'bi-heart-pulse',    '#dc3545', 2),
('Roads',              'bi-signpost-2',     '#6c757d', 3),
('Drainage',           'bi-water',          '#0d6efd', 4),
('Water',              'bi-droplet',        '#17a2b8', 5),
('Agriculture',        'bi-flower1',        '#28a745', 6),
('Electricity',        'bi-lightning',      '#ffc107', 7),
('Youth Empowerment',  'bi-people',         '#6f42c1', 8),
('Women Empowerment',  'bi-gender-female',  '#e83e8c', 9),
('Skills Development', 'bi-tools',          '#fd7e14', 10),
('Digital Inclusion',  'bi-laptop',         '#20c997', 11),
('Markets',            'bi-shop',           '#795548', 12),
('Public Buildings',   'bi-building',       '#607d8b', 13),
('Security Support',   'bi-shield-check',   '#343a40', 14),
('Sports',             'bi-trophy',         '#ff5722', 15),
('Social Welfare',     'bi-people-fill',    '#9c27b0', 16),
('Community Support',  'bi-house-heart',    '#009688', 17),
('Other',              'bi-three-dots',     '#adb5bd', 18);

-- ============================================================
-- 18. projects
-- ============================================================
CREATE TABLE `projects` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reference_number` VARCHAR(40) NOT NULL,
  `title_en` VARCHAR(300) NOT NULL,
  `title_ha` VARCHAR(300),
  `slug` VARCHAR(350) NOT NULL,
  `summary_en` TEXT,
  `summary_ha` TEXT,
  `description_en` LONGTEXT,
  `description_ha` LONGTEXT,
  `lga_id` INT UNSIGNED,
  `ward_id` INT UNSIGNED,
  `community_id` INT UNSIGNED,
  `address` VARCHAR(300),
  `latitude` DECIMAL(10,8),
  `longitude` DECIMAL(11,8),
  `category_id` INT UNSIGNED,
  `project_type` VARCHAR(80),
  `start_date` DATE,
  `completion_date` DATE,
  `expected_completion_date` DATE,
  `status` ENUM('proposed','planned','ongoing','completed','commissioned','under_maintenance','delayed','suspended','archived') NOT NULL DEFAULT 'proposed',
  `percentage_completed` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `implementing_org` VARCHAR(300),
  `funding_source` VARCHAR(300),
  `approved_budget` DECIMAL(18,2),
  `actual_expenditure` DECIMAL(18,2),
  `show_budget` TINYINT(1) NOT NULL DEFAULT 0,
  `beneficiaries` INT UNSIGNED,
  `objectives_en` LONGTEXT,
  `objectives_ha` LONGTEXT,
  `scope_en` LONGTEXT,
  `scope_ha` LONGTEXT,
  `current_condition_en` TEXT,
  `current_condition_ha` TEXT,
  `challenges_en` TEXT,
  `challenges_ha` TEXT,
  `public_notes_en` TEXT,
  `public_notes_ha` TEXT,
  `featured_image` VARCHAR(500),
  `last_progress_update` DATETIME,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `publication_status` ENUM('draft','submitted','under_review','returned','approved','published','unpublished','archived') NOT NULL DEFAULT 'draft',
  `verification_status` ENUM('unverified','under_verification','verified_internally','verified_with_docs','independently_confirmed','disputed','withdrawn') NOT NULL DEFAULT 'unverified',
  `date_published` DATETIME,
  `created_by` INT UNSIGNED,
  `submitted_by` INT UNSIGNED,
  `reviewed_by` INT UNSIGNED,
  `approved_by` INT UNSIGNED,
  `published_by` INT UNSIGNED,
  `reviewer_notes` TEXT,
  `views` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_projects_slug` (`slug`),
  UNIQUE KEY `uq_projects_ref` (`reference_number`),
  KEY `fk_proj_lga` (`lga_id`),
  KEY `fk_proj_ward` (`ward_id`),
  KEY `fk_proj_comm` (`community_id`),
  KEY `fk_proj_cat` (`category_id`),
  KEY `idx_proj_status` (`status`),
  KEY `idx_proj_pub` (`publication_status`),
  KEY `idx_proj_featured` (`is_featured`),
  CONSTRAINT `fk_proj_lga`  FOREIGN KEY (`lga_id`)       REFERENCES `local_governments`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_proj_ward` FOREIGN KEY (`ward_id`)      REFERENCES `wards`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_proj_comm` FOREIGN KEY (`community_id`) REFERENCES `communities`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_proj_cat`  FOREIGN KEY (`category_id`)  REFERENCES `project_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 19. project_images
-- ============================================================
CREATE TABLE `project_images` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` INT UNSIGNED NOT NULL,
  `image_path` VARCHAR(500) NOT NULL,
  `caption` VARCHAR(300),
  `image_type` ENUM('before','during','after','general') NOT NULL DEFAULT 'general',
  `alt_text` VARCHAR(300),
  `display_order` SMALLINT NOT NULL DEFAULT 0,
  `uploaded_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_pi_project` (`project_id`),
  CONSTRAINT `fk_pi_project` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 20. project_documents
-- ============================================================
CREATE TABLE `project_documents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(300) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_type` VARCHAR(20),
  `file_size` INT UNSIGNED,
  `description` TEXT,
  `uploaded_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_pd_project` (`project_id`),
  CONSTRAINT `fk_pd_project` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 21. project_progress_updates
-- ============================================================
CREATE TABLE `project_progress_updates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` INT UNSIGNED NOT NULL,
  `update_date` DATE NOT NULL,
  `title_en` VARCHAR(300),
  `title_ha` VARCHAR(300),
  `description_en` LONGTEXT,
  `description_ha` LONGTEXT,
  `percentage_completed` TINYINT UNSIGNED,
  `status` ENUM('proposed','planned','ongoing','completed','commissioned','under_maintenance','delayed','suspended','archived'),
  `image` VARCHAR(500),
  `created_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_ppu_project` (`project_id`),
  CONSTRAINT `fk_ppu_project` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 22. project_verifications
-- ============================================================
CREATE TABLE `project_verifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` INT UNSIGNED NOT NULL,
  `verification_type` VARCHAR(120),
  `verification_date` DATE,
  `verified_by` VARCHAR(200),
  `document` VARCHAR(500),
  `notes` TEXT,
  `public_visibility` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_pv_project` (`project_id`),
  CONSTRAINT `fk_pv_project` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 23. news_categories
-- ============================================================
CREATE TABLE `news_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `name_ha` VARCHAR(100),
  `slug` VARCHAR(120) NOT NULL,
  `description` TEXT,
  `display_order` SMALLINT NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_nc_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `news_categories` (`name`, `slug`, `display_order`) VALUES
('Press Release',       'press-release',     1),
('Community Engagement','community-engagement',2),
('Project Update',      'project-update',    3),
('Public Statement',    'public-statement',  4),
('Speech',              'speech',            5),
('Interview',           'interview',         6),
('Event Report',        'event-report',      7),
('Announcement',        'announcement',      8),
('Opinion',             'opinion',           9),
('Media Coverage',      'media-coverage',    10);

-- ============================================================
-- 24. tags
-- ============================================================
CREATE TABLE `tags` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(80) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tags_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 25. news
-- ============================================================
CREATE TABLE `news` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title_en` VARCHAR(400) NOT NULL,
  `title_ha` VARCHAR(400),
  `slug` VARCHAR(450) NOT NULL,
  `category_id` INT UNSIGNED,
  `summary_en` TEXT,
  `summary_ha` TEXT,
  `body_en` LONGTEXT,
  `body_ha` LONGTEXT,
  `featured_image` VARCHAR(500),
  `author_id` INT UNSIGNED,
  `source` VARCHAR(300),
  `publication_date` DATETIME,
  `location` VARCHAR(200),
  `related_project_id` INT UNSIGNED,
  `related_event_id` INT UNSIGNED,
  `seo_title_en` VARCHAR(200),
  `seo_title_ha` VARCHAR(200),
  `seo_description_en` VARCHAR(500),
  `seo_description_ha` VARCHAR(500),
  `publication_status` ENUM('draft','submitted','under_review','returned','approved','published','unpublished','archived') NOT NULL DEFAULT 'draft',
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `views` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_by` INT UNSIGNED,
  `approved_by` INT UNSIGNED,
  `published_by` INT UNSIGNED,
  `reviewer_notes` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_news_slug` (`slug`),
  KEY `fk_news_cat` (`category_id`),
  KEY `idx_news_pub` (`publication_status`),
  KEY `idx_news_date` (`publication_date`),
  KEY `idx_news_featured` (`is_featured`),
  CONSTRAINT `fk_news_cat` FOREIGN KEY (`category_id`) REFERENCES `news_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 26. news_tags
-- ============================================================
CREATE TABLE `news_tags` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `news_id` INT UNSIGNED NOT NULL,
  `tag_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_news_tag` (`news_id`, `tag_id`),
  KEY `fk_nt_tag` (`tag_id`),
  CONSTRAINT `fk_nt_news` FOREIGN KEY (`news_id`) REFERENCES `news`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_nt_tag`  FOREIGN KEY (`tag_id`)  REFERENCES `tags`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 27. speeches
-- ============================================================
CREATE TABLE `speeches` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title_en` VARCHAR(400) NOT NULL,
  `title_ha` VARCHAR(400),
  `slug` VARCHAR(450) NOT NULL,
  `speech_date` DATE,
  `occasion` VARCHAR(300),
  `location` VARCHAR(200),
  `summary_en` TEXT,
  `summary_ha` TEXT,
  `full_text_en` LONGTEXT,
  `full_text_ha` LONGTEXT,
  `video_url` VARCHAR(500),
  `document` VARCHAR(500),
  `publication_status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_by` INT UNSIGNED,
  `approved_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_speeches_slug` (`slug`),
  KEY `idx_sp_status` (`publication_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 28. events
-- ============================================================
CREATE TABLE `events` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title_en` VARCHAR(300) NOT NULL,
  `title_ha` VARCHAR(300),
  `slug` VARCHAR(350) NOT NULL,
  `event_type` VARCHAR(80),
  `description_en` LONGTEXT,
  `description_ha` LONGTEXT,
  `event_date` DATE NOT NULL,
  `start_time` TIME,
  `end_time` TIME,
  `venue` VARCHAR(300),
  `lga_id` INT UNSIGNED,
  `ward_id` INT UNSIGNED,
  `community_id` INT UNSIGNED,
  `latitude` DECIMAL(10,8),
  `longitude` DECIMAL(11,8),
  `cover_image` VARCHAR(500),
  `contact_person` VARCHAR(160),
  `contact_phone` VARCHAR(30),
  `registration_link` VARCHAR(500),
  `status` ENUM('upcoming','ongoing','completed','postponed','cancelled') NOT NULL DEFAULT 'upcoming',
  `publication_status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_by` INT UNSIGNED,
  `approved_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_events_slug` (`slug`),
  KEY `fk_ev_lga` (`lga_id`),
  KEY `idx_ev_date` (`event_date`),
  KEY `idx_ev_status` (`status`),
  CONSTRAINT `fk_ev_lga` FOREIGN KEY (`lga_id`) REFERENCES `local_governments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 29. gallery_albums
-- ============================================================
CREATE TABLE `gallery_albums` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title_en` VARCHAR(300) NOT NULL,
  `title_ha` VARCHAR(300),
  `slug` VARCHAR(350) NOT NULL,
  `album_date` DATE,
  `location` VARCHAR(200),
  `description_en` TEXT,
  `description_ha` TEXT,
  `cover_image` VARCHAR(500),
  `related_project_id` INT UNSIGNED,
  `related_event_id` INT UNSIGNED,
  `publication_status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ga_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 30. gallery_images
-- ============================================================
CREATE TABLE `gallery_images` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `album_id` INT UNSIGNED NOT NULL,
  `filename` VARCHAR(500) NOT NULL,
  `caption_en` VARCHAR(300),
  `caption_ha` VARCHAR(300),
  `alt_text` VARCHAR(300),
  `photographer_credit` VARCHAR(200),
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `related_project_id` INT UNSIGNED,
  `related_event_id` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME,
  PRIMARY KEY (`id`),
  KEY `fk_gi_album` (`album_id`),
  CONSTRAINT `fk_gi_album` FOREIGN KEY (`album_id`) REFERENCES `gallery_albums`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 31. videos
-- ============================================================
CREATE TABLE `videos` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title_en` VARCHAR(300) NOT NULL,
  `title_ha` VARCHAR(300),
  `slug` VARCHAR(350) NOT NULL,
  `platform` ENUM('youtube','facebook','uploaded') NOT NULL DEFAULT 'youtube',
  `video_url` VARCHAR(500) NOT NULL,
  `duration` VARCHAR(20),
  `thumbnail` VARCHAR(500),
  `description_en` TEXT,
  `description_ha` TEXT,
  `recorded_date` DATE,
  `category` VARCHAR(80),
  `related_project_id` INT UNSIGNED,
  `related_event_id` INT UNSIGNED,
  `publication_status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_videos_slug` (`slug`),
  KEY `idx_vid_status` (`publication_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 32. public_documents
-- ============================================================
CREATE TABLE `public_documents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title_en` VARCHAR(300) NOT NULL,
  `title_ha` VARCHAR(300),
  `slug` VARCHAR(350) NOT NULL,
  `description_en` TEXT,
  `description_ha` TEXT,
  `filename` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED,
  `mime_type` VARCHAR(100),
  `category` VARCHAR(80),
  `source_en` VARCHAR(300),
  `source_ha` VARCHAR(300),
  `publication_status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `document_date` DATE,
  `download_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pd_slug` (`slug`),
  KEY `idx_pd_status` (`publication_status`),
  KEY `idx_pd_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 33. testimonials
-- ============================================================
CREATE TABLE `testimonials` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `display_name` VARCHAR(160) NOT NULL,
  `community` VARCHAR(120),
  `lga_id` INT UNSIGNED,
  `occupation` VARCHAR(120),
  `testimonial_en` TEXT NOT NULL,
  `testimonial_ha` TEXT,
  `consent_given` TINYINT(1) NOT NULL DEFAULT 0,
  `approval_status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `approved_by` INT UNSIGNED,
  `publication_status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_test_approval` (`approval_status`),
  KEY `idx_test_pub` (`publication_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 34. contact_messages
-- ============================================================
CREATE TABLE `contact_messages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(160) NOT NULL,
  `email` VARCHAR(200),
  `phone` VARCHAR(30),
  `subject` VARCHAR(300),
  `message` TEXT NOT NULL,
  `preferred_response` ENUM('email','phone','either') NOT NULL DEFAULT 'email',
  `consent_given` TINYINT(1) NOT NULL DEFAULT 0,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `ip_address` VARCHAR(45),
  `replied_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cm_read` (`is_read`),
  KEY `idx_cm_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 35. community_requests
-- ============================================================
CREATE TABLE `community_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tracking_number` VARCHAR(30) NOT NULL,
  `full_name` VARCHAR(160) NOT NULL,
  `phone` VARCHAR(30),
  `email` VARCHAR(200),
  `lga_id` INT UNSIGNED,
  `ward_id` INT UNSIGNED,
  `community_name` VARCHAR(120),
  `category` VARCHAR(80) NOT NULL,
  `title` VARCHAR(300) NOT NULL,
  `description` TEXT NOT NULL,
  `location` VARCHAR(300),
  `photo` VARCHAR(500),
  `document` VARCHAR(500),
  `status` ENUM('received','under_review','referred','in_progress','resolved','closed','rejected','duplicate') NOT NULL DEFAULT 'received',
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `consent_given` TINYINT(1) NOT NULL DEFAULT 0,
  `acknowledgement_sent` TINYINT(1) NOT NULL DEFAULT 0,
  `ip_address` VARCHAR(45),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cr_tracking` (`tracking_number`),
  KEY `fk_cr_lga` (`lga_id`),
  KEY `idx_cr_status` (`status`),
  KEY `idx_cr_created` (`created_at`),
  CONSTRAINT `fk_cr_lga` FOREIGN KEY (`lga_id`) REFERENCES `local_governments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 36. community_request_actions
-- ============================================================
CREATE TABLE `community_request_actions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` INT UNSIGNED NOT NULL,
  `action_type` VARCHAR(80) NOT NULL,
  `note` TEXT,
  `is_internal` TINYINT(1) NOT NULL DEFAULT 1,
  `performed_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_cra_request` (`request_id`),
  CONSTRAINT `fk_cra_request` FOREIGN KEY (`request_id`) REFERENCES `community_requests`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 37. subscribers
-- ============================================================
CREATE TABLE `subscribers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(160) NOT NULL,
  `email` VARCHAR(200) NOT NULL,
  `phone` VARCHAR(30),
  `lga_id` INT UNSIGNED,
  `community` VARCHAR(120),
  `consent_given` TINYINT(1) NOT NULL DEFAULT 0,
  `is_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `confirmation_token` VARCHAR(100),
  `status` ENUM('active','unsubscribed') NOT NULL DEFAULT 'active',
  `ip_address` VARCHAR(45),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sub_email` (`email`),
  KEY `idx_sub_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 38. social_media_links
-- ============================================================
CREATE TABLE `social_media_links` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `platform` VARCHAR(60) NOT NULL,
  `url` VARCHAR(500),
  `icon` VARCHAR(80) NOT NULL DEFAULT 'bi-link',
  `display_order` SMALLINT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `social_media_links` (`platform`, `url`, `icon`, `display_order`) VALUES
('Facebook',  '[FACEBOOK PROFILE URL]',  'bi-facebook',  1),
('Twitter/X', '[TWITTER PROFILE URL]',   'bi-twitter-x', 2),
('Instagram', '[INSTAGRAM PROFILE URL]', 'bi-instagram', 3),
('YouTube',   '[YOUTUBE CHANNEL URL]',   'bi-youtube',   4),
('WhatsApp',  '[WHATSAPP CONTACT URL]',  'bi-whatsapp',  5);

-- ============================================================
-- 39. administrator_notifications
-- ============================================================
CREATE TABLE `administrator_notifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED,
  `type` VARCHAR(80) NOT NULL,
  `title` VARCHAR(300) NOT NULL,
  `message` TEXT,
  `link` VARCHAR(500),
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_an_user` (`user_id`),
  KEY `idx_an_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 40. media_library
-- ============================================================
CREATE TABLE `media_library` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `filename` VARCHAR(300) NOT NULL,
  `original_name` VARCHAR(300) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_type` ENUM('image','document','video','other') NOT NULL DEFAULT 'image',
  `mime_type` VARCHAR(100),
  `file_size` INT UNSIGNED,
  `alt_text` VARCHAR(300),
  `caption` VARCHAR(500),
  `uploaded_by` INT UNSIGNED,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ml_type` (`file_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 41. page_views
-- ============================================================
CREATE TABLE `page_views` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `page_type` VARCHAR(60) NOT NULL,
  `record_id` INT UNSIGNED DEFAULT NULL,
  `page_url` VARCHAR(500),
  `ip_hash` CHAR(64),
  `device_type` ENUM('mobile','tablet','desktop','unknown') NOT NULL DEFAULT 'unknown',
  `referrer` VARCHAR(500),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pv_type` (`page_type`),
  KEY `idx_pv_record` (`record_id`),
  KEY `idx_pv_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 42. consent_records
-- ============================================================
CREATE TABLE `consent_records` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(80) NOT NULL,
  `email` VARCHAR(200),
  `consent_text` TEXT,
  `ip_address` VARCHAR(45),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cr_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- END OF SCHEMA
-- ============================================================