CREATE DATABASE IF NOT EXISTS inventory_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE inventory_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(80) NOT NULL DEFAULT 'Inventory Manager',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS api_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(128) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    CONSTRAINT fk_api_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS app_settings (
    id INT PRIMARY KEY,
    company_name VARCHAR(180) NOT NULL,
    company_phone VARCHAR(40) NOT NULL,
    company_email VARCHAR(150) NOT NULL,
    company_address VARCHAR(255) NOT NULL,
    invoice_prefix VARCHAR(20) NOT NULL DEFAULT 'INV',
    invoice_footer VARCHAR(255) NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(40) NOT NULL,
    address VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(80) NOT NULL UNIQUE,
    barcode VARCHAR(120) NOT NULL UNIQUE,
    category VARCHAR(100) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    reorder_level INT NOT NULL DEFAULT 10,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    supplier_name VARCHAR(150) NOT NULL,
    attribute_primary VARCHAR(120) NOT NULL DEFAULT '',
    attribute_secondary VARCHAR(120) NOT NULL DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS stock_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    movement_type ENUM('in', 'out', 'adjustment') NOT NULL,
    quantity INT NOT NULL,
    note VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_stock_movements_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS sales_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sales_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE IF NOT EXISTS sales_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sales_order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sales_order_items_order FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_order_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(30) NOT NULL DEFAULT 'info',
    title VARCHAR(150) NOT NULL,
    subtitle VARCHAR(255) NOT NULL,
    quantity_label VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email, password, role) VALUES
('Mizanur Rahman', 'admin@stockflow.com', '$2y$10$L27HuRw.gRdcs0zfFELIUuT.HdVUgZ78OMRuiTBZevnSKB4gfNzWO', 'Inventory Manager')
ON DUPLICATE KEY UPDATE
name = VALUES(name), password = VALUES(password), role = VALUES(role);

INSERT INTO app_settings (
    id,
    company_name,
    company_phone,
    company_email,
    company_address,
    invoice_prefix,
    invoice_footer
) VALUES (
    1,
    'StockFlow Distribution',
    '+8801711000000',
    'support@stockflow.com',
    'House 12, Sector 7, Uttara, Dhaka',
    'INV',
    'Thank you for choosing StockFlow. Goods once sold may be subject to your company return policy.'
)
ON DUPLICATE KEY UPDATE
company_name = VALUES(company_name),
company_phone = VALUES(company_phone),
company_email = VALUES(company_email),
company_address = VALUES(company_address),
invoice_prefix = VALUES(invoice_prefix),
invoice_footer = VALUES(invoice_footer);

INSERT INTO customers (name, phone, address) VALUES
('Rahim Traders', '01711000001', 'Dhaka, Bangladesh'),
('City Mart', '01711000002', 'Chattogram, Bangladesh'),
('Blue Basket', '01711000003', 'Khulna, Bangladesh')
ON DUPLICATE KEY UPDATE
phone = VALUES(phone), address = VALUES(address);

INSERT INTO products (
    name,
    sku,
    barcode,
    category,
    stock,
    reorder_level,
    price,
    supplier_name,
    attribute_primary,
    attribute_secondary
) VALUES
('Wireless Barcode Scanner', 'INV-2048', '8901263040012', 'Hardware', 84, 18, 149.00, 'Vision Tech', '1 Year', 'SN-AX2048'),
('Packing Box Large', 'PKG-1001', '8901263040013', 'Packaging', 23, 30, 12.50, 'Pack Right', 'Carton', '2027-12'),
('Thermal Label Roll', 'LBL-9901', '8901263040014', 'Consumable', 172, 40, 6.25, 'PaperHub Supplies', 'Roll', '2026-11'),
('POS Tablet Stand', 'POS-3320', '8901263040015', 'Accessories', 11, 15, 49.00, 'Counter Space', 'Aluminium', 'Black')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
barcode = VALUES(barcode),
category = VALUES(category),
stock = VALUES(stock),
reorder_level = VALUES(reorder_level),
price = VALUES(price),
supplier_name = VALUES(supplier_name),
attribute_primary = VALUES(attribute_primary),
attribute_secondary = VALUES(attribute_secondary);

INSERT INTO stock_movements (product_id, movement_type, quantity, note) VALUES
(1, 'in', 84, 'Initial demo stock'),
(2, 'in', 23, 'Initial demo stock'),
(3, 'in', 172, 'Initial demo stock'),
(4, 'in', 11, 'Initial demo stock');

INSERT INTO sales_orders (order_number, customer_id, total_amount, created_at) VALUES
('SO-DEMO-1001', 1, 1280.00, NOW()),
('SO-DEMO-1002', 2, 1150.00, NOW())
ON DUPLICATE KEY UPDATE total_amount = VALUES(total_amount), customer_id = VALUES(customer_id);

INSERT INTO sales_order_items (sales_order_id, product_id, quantity, unit_price) VALUES
((SELECT id FROM sales_orders WHERE order_number = 'SO-DEMO-1001'), 1, 4, 320.00),
((SELECT id FROM sales_orders WHERE order_number = 'SO-DEMO-1002'), 3, 184, 6.25)
ON DUPLICATE KEY UPDATE quantity = VALUES(quantity), unit_price = VALUES(unit_price);

INSERT INTO activities (type, title, subtitle, quantity_label) VALUES
('success', 'Restocked Label Roll', 'Thermal Label Roll arrived from PaperHub Supplies.', '+60 units'),
('warning', 'Low stock alert', 'POS Tablet Stand dropped below threshold.', '11 left'),
('info', 'New order packed', 'Invoice SO-DEMO-1001 is ready for dispatch.', '-4 units');