-- Database schema for Shinda USSD + M-Pesa System
-- Note: Create the database manually first through your hosting control panel
-- Do not include CREATE DATABASE statement as it requires special privileges

-- Users table for admin dashboard
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin', 'presenter') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sessions table for USSD sessions
CREATE TABLE sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20) NOT NULL,
    selected_box INT NOT NULL,
    status ENUM('pending', 'paid', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Transactions table for M-Pesa payments
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    receipt VARCHAR(255),
    status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
    session_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id)
);

-- Game results table
CREATE TABLE game_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20) NOT NULL,
    selected_box INT NOT NULL,
    results TEXT NOT NULL,
    session_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id)
);

-- Services table for boxes
CREATE TABLE services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    box_number INT UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Settings table
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(255) UNIQUE NOT NULL,
    value TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO settings (key_name, value) VALUES
('admin_percentage', '80'),
('presenter_percentage', '80'),
('sms_api_key', 'your_sms_api_key'),
('sms_sender_id', 'SHINDA'),
('mpesa_consumer_key', 'your_consumer_key'),
('mpesa_consumer_secret', 'your_consumer_secret'),
('mpesa_shortcode', 'your_shortcode'),
('mpesa_passkey', 'your_passkey'),
('mpesa_mode', 'sandbox'),
('ussd_api_key', 'your_ussd_api_key'),
('maintenance_mode', '0');

-- Insert default services (boxes)
INSERT INTO services (box_number, name, amount) VALUES
(1, 'Box 1', 10.00),
(2, 'Box 2', 20.00),
(3, 'Box 3', 30.00),
(4, 'Box 4', 40.00),
(5, 'Box 5', 50.00);

-- Create a default super admin user (password: admin123)
INSERT INTO users (name, email, password, role) VALUES
('Super Admin', 'admin@shinda.co.ke', '$2y$10$VJzReGVX0PdGP88Gapnyiu58q/FmxK.VTk8u/BxtUB1GCWUJ3Z.mO', 'super_admin');