database.sql

-- Police Directory Database Schema
-- Run this file to set up the database

CREATE DATABASE IF NOT EXISTS police_directory CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE police_directory;

-- Stations table
CREATE TABLE IF NOT EXISTS stations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_name VARCHAR(255) NOT NULL,
    town VARCHAR(100) NOT NULL,
    region VARCHAR(100) NOT NULL,
    phone_number VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_town (town),
    INDEX idx_region (region),
    INDEX idx_search (town, region)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin users table (for admin panel authentication)
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (username: admin, password: admin123)
-- IMPORTANT: Change this password after first login!
INSERT INTO admin_users (username, password_hash) VALUES 
('admin', '$2y$10$FGgSyvkY0MdtcE1T.CDu5.00XTkH2Yce3iyKFsC8sjr7u3DAZTdeC');

-- Sample data for testing
INSERT INTO stations (station_name, town, region, phone_number) VALUES
('Accra Central Police Station', 'Accra', 'Greater Accra', '0302-664691'),
('Nima Police Station', 'Accra', 'Greater Accra', '0302-234567'),
('Tema Community 1 Police Station', 'Tema', 'Greater Accra', '0303-202345'),
('Kumasi Central Police Station', 'Kumasi', 'Ashanti', '0322-022346'),
('Asokwa Police Station', 'Kumasi', 'Ashanti', '0322-087654'),
('Takoradi Central Police Station', 'Takoradi', 'Western', '0312-023456'),
('Cape Coast Police Station', 'Cape Coast', 'Central', '0332-132046'),
('Tamale Central Police Station', 'Tamale', 'Northern', '0372-022350'),
('Koforidua Police Station', 'Koforidua', 'Eastern', '0342-022156'),
('Ho Police Station', 'Ho', 'Volta', '0362-026666'),
('Sunyani Police Station', 'Sunyani', 'Bono', '0352-027206'),
('Bolgatanga Police Station', 'Bolgatanga', 'Upper East', '0382-023333'),
('Wa Police Station', 'Wa', 'Upper West', '0392-022210'),
('Osu Police Station', 'Accra', 'Greater Accra', '0302-776311'),
('Madina Police Station', 'Accra', 'Greater Accra', '0302-501815');