-- ============================================================
-- Collnetwork WiFi Subscriber Database Setup
-- Run as root: mysql -u root -p < setup_db.sql
-- ============================================================

-- Create database
CREATE DATABASE IF NOT EXISTS collnetwork_wifi 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- Create dedicated user
CREATE USER IF NOT EXISTS 'collnetwork'@'localhost' IDENTIFIED BY 'Coll@Net2025!';
GRANT ALL PRIVILEGES ON collnetwork_wifi.* TO 'collnetwork'@'localhost';
FLUSH PRIVILEGES;

USE collnetwork_wifi;

-- Subscribers table
CREATE TABLE IF NOT EXISTS subscribers (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    first_name      VARCHAR(100) NOT NULL,
    surname         VARCHAR(100) NOT NULL,
    email           VARCHAR(255) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    phone           VARCHAR(20),
    ip_address      VARCHAR(45),
    payment_status  ENUM('active', 'expired', 'pending', 'failed') DEFAULT 'pending',
    paystack_ref    VARCHAR(100),
    last_payment    DATETIME,
    expiry_date     DATETIME,
    mikrotik_added  TINYINT(1) DEFAULT 0,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_payment_status (payment_status),
    INDEX idx_expiry_date (expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payment logs table (audit trail)
CREATE TABLE IF NOT EXISTS payment_logs (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    subscriber_id   INT,
    paystack_ref    VARCHAR(100),
    amount          DECIMAL(10,2),
    status          VARCHAR(50),
    gateway_response TEXT,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Password reset tokens table
CREATE TABLE IF NOT EXISTS password_resets (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    token      VARCHAR(128) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    used       TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_token (token),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT 'Database setup complete!' AS Status;
SELECT 'DB: collnetwork_wifi' AS Info;
SELECT 'User: collnetwork@localhost' AS Info;
SELECT 'Password: Coll@Net2025!' AS Info;
