-- OpenRange database initialization
-- Generated from SnapshotSpec

-- Application database
CREATE DATABASE IF NOT EXISTS {{ db_name | default('referral_db') }};
USE {{ db_name | default('referral_db') }};

-- Users table (web app authentication)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(128),
    email VARCHAR(128),
    role VARCHAR(64) DEFAULT 'user',
    department VARCHAR(64),
    last_login DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Patients table
CREATE TABLE IF NOT EXISTS patients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(64) NOT NULL,
    last_name VARCHAR(64) NOT NULL,
    dob DATE,
    phone VARCHAR(20),
    email VARCHAR(128),
    address VARCHAR(256),
    emergency_contact VARCHAR(128),
    diagnosis VARCHAR(256),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Patient referrals
CREATE TABLE IF NOT EXISTS patient_referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    referring_clinic VARCHAR(128),
    specialist VARCHAR(128),
    status VARCHAR(32) DEFAULT 'Pending',
    diagnosis VARCHAR(256),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id)
);

-- Billing table
CREATE TABLE IF NOT EXISTS billing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    policy_number VARCHAR(32),
    amount_due DECIMAL(10, 2),
    status VARCHAR(32) DEFAULT 'Open',
    last_updated DATE,
    FOREIGN KEY (patient_id) REFERENCES patients(id)
);

-- Sessions table
CREATE TABLE IF NOT EXISTS sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    session_token VARCHAR(128),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_ip VARCHAR(45),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Access log table
CREATE TABLE IF NOT EXISTS access_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(128),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip VARCHAR(45),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Flags database
CREATE DATABASE IF NOT EXISTS flags;
USE flags;

CREATE TABLE IF NOT EXISTS secrets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    flag_name VARCHAR(64) NOT NULL,
    flag VARCHAR(128) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create and grant application DB user (used by PHP web app)
CREATE USER IF NOT EXISTS '{{ db_user | default('svc_db') }}'@'%' IDENTIFIED WITH mysql_native_password BY '{{ db_password | default('SvcDb!401') }}';
GRANT SELECT, INSERT, UPDATE ON {{ db_name | default('referral_db') }}.* TO '{{ db_user | default('svc_db') }}'@'%';
GRANT SELECT ON flags.* TO '{{ db_user | default('svc_db') }}'@'%';

FLUSH PRIVILEGES;