CREATE DATABASE IF NOT EXISTS newploit;
USE newploit;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(64) NOT NULL,
    password VARCHAR(128) NOT NULL,
    email VARCHAR(128) NOT NULL,
    role VARCHAR(32) NOT NULL DEFAULT 'user',
    api_key VARCHAR(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (username, password, email, role, api_key) VALUES
 ('admin','admin123','admin@insecure.newploit.com','admin','sk_admin_AKIAIOSFODNN7EXAMPLE'),
 ('root','toor','root@insecure.newploit.com','admin','sk_root_deadbeefcafebabe'),
 ('user','password','user@insecure.newploit.com','user','sk_user_abc123'),
 ('test','test','test@insecure.newploit.com','user','sk_test_xyz789'),
 ('imtaqin','newploit2024','taqin@insecure.newploit.com','admin','sk_tq_pocketpentester'),
 ('guest','guest','guest@insecure.newploit.com','guest',NULL);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    category VARCHAR(64) DEFAULT NULL,
    price DECIMAL(10,2) DEFAULT NULL,
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO products (name, category, price, description) VALUES
 ('Pentest Laptop','hardware',2599.00,'Rugged laptop for offensive research'),
 ('YubiKey','hardware',49.00,'Hardware FIDO2 authenticator'),
 ('Burp Pro','software',499.00,'Web app testing'),
 ('Newploit Pro','subscription',29.99,'Monthly subscription for cloud scanners'),
 ('Ghidra Cloud','subscription',99.00,'RE-as-a-service for mobile pentesting');

CREATE TABLE sessions (
    id VARCHAR(64) PRIMARY KEY,
    user_id INT NOT NULL,
    token TEXT,
    expires_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

GRANT ALL PRIVILEGES ON newploit.* TO 'dbuser'@'%' IDENTIFIED BY 'dbpass123';
FLUSH PRIVILEGES;