-- ============================================================
-- ISP Product Stock Management System
-- Database Schema + Sample Data
-- ============================================================

CREATE DATABASE IF NOT EXISTS stock_management
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE stock_management;

-- --------------------------------------------------------
-- Table: users
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id         INT          AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(50)  NOT NULL UNIQUE,
    password   VARCHAR(255) NOT NULL,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------
-- Table: products
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS products (
    id            INT          AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    description   TEXT,
    unit          VARCHAR(20)  DEFAULT 'pcs',
    current_stock INT          NOT NULL DEFAULT 0,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------
-- Table: stock_transactions
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS stock_transactions (
    id         INT                       AUTO_INCREMENT PRIMARY KEY,
    product_id INT                       NOT NULL,
    quantity   INT                       NOT NULL,
    type       ENUM('added','deducted')  NOT NULL,
    notes      TEXT,
    created_at TIMESTAMP                 DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- --------------------------------------------------------
-- Default admin user
-- NOTE: Password is set by install.php (bcrypt hash of 'admin123')
-- Do NOT manually insert a plain-text password here.
-- Run install.php to create the user automatically.
-- --------------------------------------------------------

-- --------------------------------------------------------
-- Sample products (optional – install.php inserts these too)
-- --------------------------------------------------------
-- INSERT INTO products (name, description, unit, current_stock) VALUES
-- ('ONU',    'Optical Network Unit for fiber connection', 'pcs',   50),
-- ('Pascot', 'Network patch cord connector',              'pcs',  120),
-- ('Cable',  'Fiber optic cable',                        'meter', 500),
-- ('Tie',    'Cable tie for wire management',            'pack',   30),
-- ('TJ Box', 'Terminal junction box',                    'pcs',    20);