-- db.sql (MySQL 8+)
CREATE DATABASE IF NOT EXISTS login_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE login_demo;

-- Users
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Per-user holdings (amount of shares)
CREATE TABLE IF NOT EXISTS holdings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount_shares DECIMAL(20,4) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX (user_id, created_at)
);

-- Global share value + latest update (visible to all users)
CREATE TABLE IF NOT EXISTS updates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  share_value DECIMAL(20,4) NOT NULL,
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed an admin (replace the hash later using register.php or manual insert)
-- Password here is 'Admin123!' (example). You can re-hash with PHP if needed.
INSERT INTO users (email, password_hash, role)
VALUES ('admin@example.com', '$2y$10$e7cM4S7WjYvIvhQ0nmiA1e0q8Ykz1Rmpn32f1OevcQ1bqzFTdM0ue', 'admin')
ON DUPLICATE KEY UPDATE email = email;

-- Seed an example update
INSERT INTO updates (share_value, note) VALUES (1.00, 'Initial listing value') ;