-- Database Schema for Door to Door Women in Support of Tinubu's Reforms (D2DWSTR) Campaign Platform
-- Compatible with MySQL 5.7+ / MariaDB 10.3+ (XAMPP phpMyAdmin)

CREATE DATABASE IF NOT EXISTS `d2dwstr_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `d2dwstr_db`;

-- Users & Admin Roles
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(100) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `role` VARCHAR(50) NOT NULL,
    `email` VARCHAR(150),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Supporters
CREATE TABLE IF NOT EXISTS `supporters` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `supporter_id` VARCHAR(50) UNIQUE NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `phone` VARCHAR(50) NOT NULL,
    `email` VARCHAR(150),
    `state` VARCHAR(100) NOT NULL,
    `lga` VARCHAR(100) NOT NULL,
    `ward` VARCHAR(100),
    `gender` VARCHAR(20),
    `pledge_2027` TINYINT(1) DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `status` VARCHAR(30) DEFAULT 'Verified'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Volunteers
CREATE TABLE IF NOT EXISTS `volunteers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `full_name` VARCHAR(150) NOT NULL,
    `gender` VARCHAR(20),
    `phone` VARCHAR(50) NOT NULL,
    `email` VARCHAR(150),
    `state` VARCHAR(100) NOT NULL,
    `lga` VARCHAR(100) NOT NULL,
    `ward` VARCHAR(100),
    `occupation` VARCHAR(100),
    `skills` TEXT,
    `department` VARCHAR(100) NOT NULL,
    `availability` VARCHAR(50),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `status` VARCHAR(30) DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Coordinators
CREATE TABLE IF NOT EXISTS `coordinators` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `full_name` VARCHAR(150) NOT NULL,
    `role` VARCHAR(100) NOT NULL,
    `zone` VARCHAR(100),
    `state` VARCHAR(100),
    `phone` VARCHAR(50),
    `email` VARCHAR(150),
    `photo` VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Events
CREATE TABLE IF NOT EXISTS `events` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(200) NOT NULL,
    `category` VARCHAR(50),
    `event_date` DATE,
    `event_time` VARCHAR(50),
    `venue` VARCHAR(255),
    `state` VARCHAR(100),
    `lga` VARCHAR(100),
    `description` TEXT,
    `banner` VARCHAR(255),
    `status` VARCHAR(50) DEFAULT 'Upcoming',
    `stream_url` VARCHAR(255),
    `map_url` VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Event RSVPs
CREATE TABLE IF NOT EXISTS `event_registrations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `event_id` INT,
    `full_name` VARCHAR(150),
    `phone` VARCHAR(50),
    `email` VARCHAR(150),
    `state` VARCHAR(100),
    `registered_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`event_id`) REFERENCES `events`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- News & Speeches
CREATE TABLE IF NOT EXISTS `news` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `author` VARCHAR(100) DEFAULT 'D2DWSTR Media Centre',
    `published_date` DATE,
    `summary` TEXT,
    `content` LONGTEXT,
    `featured_image` VARCHAR(255),
    `tags` VARCHAR(255),
    `is_featured` TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Priorities / Manifesto (15 sectors)
CREATE TABLE IF NOT EXISTS `priorities` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(150) NOT NULL,
    `category` VARCHAR(100) NOT NULL,
    `icon` VARCHAR(50),
    `short_summary` TEXT,
    `full_description` LONGTEXT,
    `key_targets` TEXT,
    `achievements_count` INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Achievements Tracker (Before/After)
CREATE TABLE IF NOT EXISTS `achievements` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `sector` VARCHAR(100) NOT NULL,
    `state` VARCHAR(100) NOT NULL,
    `year` INT,
    `status` VARCHAR(50) DEFAULT 'Completed',
    `before_img` VARCHAR(255),
    `after_img` VARCHAR(255),
    `description` LONGTEXT,
    `impact_stats` VARCHAR(255),
    `source_ref` VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 36 States + FCT Impact Directory
CREATE TABLE IF NOT EXISTS `state_impacts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `state_name` VARCHAR(100) UNIQUE NOT NULL,
    `zone` VARCHAR(50) NOT NULL,
    `capital` VARCHAR(100),
    `governor` VARCHAR(150),
    `federal_projects` LONGTEXT,
    `nelfund_beneficiaries` VARCHAR(50),
    `women_nano_grants` VARCHAR(50),
    `cct_beneficiaries` VARCHAR(50),
    `road_projects` LONGTEXT,
    `agric_inputs` LONGTEXT,
    `healthcare_centres` VARCHAR(50),
    `key_highlight` LONGTEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Fact Check & Verification
CREATE TABLE IF NOT EXISTS `fact_checks` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `claim` TEXT NOT NULL,
    `verdict` VARCHAR(50) NOT NULL,
    `debunk_summary` TEXT NOT NULL,
    `full_analysis` LONGTEXT,
    `evidence_source` TEXT,
    `publication_date` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Donations
CREATE TABLE IF NOT EXISTS `donations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `donor_name` VARCHAR(150),
    `donor_email` VARCHAR(150),
    `donor_phone` VARCHAR(50),
    `amount` DECIMAL(12,2),
    `payment_method` VARCHAR(50),
    `reference_no` VARCHAR(100) UNIQUE,
    `status` VARCHAR(50) DEFAULT 'Confirmed',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contact Messages
CREATE TABLE IF NOT EXISTS `contact_messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150),
    `email` VARCHAR(150),
    `phone` VARCHAR(50),
    `subject` VARCHAR(200),
    `message` TEXT,
    `status` VARCHAR(30) DEFAULT 'New',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Newsletter Subscribers
CREATE TABLE IF NOT EXISTS `newsletter_subscribers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(150) UNIQUE,
    `phone` VARCHAR(50),
    `preferred_language` VARCHAR(20) DEFAULT 'English',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Audit Logs
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_name` VARCHAR(100),
    `action` VARCHAR(255),
    `target_module` VARCHAR(100),
    `ip_address` VARCHAR(50),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;