-- ============================================================
-- Asset Tracking System — Database Schema
-- Version: 1.0
-- Compatible with: MySQL 5.7+ / MariaDB 10.3+
-- ============================================================

-- Create database (uncomment if running manually)
-- CREATE DATABASE IF NOT EXISTS asset_tracking CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE asset_tracking;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ────────────────────────────────────────────────────────────
-- USERS TABLE
-- ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `username`      VARCHAR(50)     NOT NULL,
    `password_hash` VARCHAR(255)    NOT NULL,
    `full_name`     VARCHAR(100)    DEFAULT NULL,
    `email`         VARCHAR(150)    DEFAULT NULL,
    `role`          ENUM('admin','user') NOT NULL DEFAULT 'user',
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    `last_login`    DATETIME        DEFAULT NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    KEY `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ────────────────────────────────────────────────────────────
-- INVOICES TABLE
-- ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `invoices` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `invoice_number` VARCHAR(50)    NOT NULL,
    `invoice_date`  DATE            NOT NULL,
    `customer_name` VARCHAR(200)    NOT NULL,
    `customer_contact` VARCHAR(200) DEFAULT NULL,
    `total_amount`  DECIMAL(12,2)   DEFAULT 0.00,
    `currency`      VARCHAR(10)     DEFAULT 'AUD',
    `file_name`     VARCHAR(255)    DEFAULT NULL,
    `notes`         TEXT            DEFAULT NULL,
    `processed_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_invoice_number` (`invoice_number`),
    KEY `idx_invoice_date` (`invoice_date`),
    KEY `idx_customer` (`customer_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ────────────────────────────────────────────────────────────
-- ASSETS TABLE
-- ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `assets` (
    `id`                INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `serial_number`     VARCHAR(100)    NOT NULL,
    `item_description`  TEXT            NOT NULL,
    `product_type`      VARCHAR(100)    DEFAULT NULL,
    `model`             VARCHAR(200)    DEFAULT NULL,
    `warranty_years`    INT             DEFAULT 1,
    `warranty_expiry_date` DATE         DEFAULT NULL,
    `invoice_id`        INT UNSIGNED    DEFAULT NULL,
    `employee_name`     VARCHAR(150)    DEFAULT NULL,
    `office_location`   VARCHAR(200)    DEFAULT NULL,
    `unit_price`        DECIMAL(10,2)   DEFAULT 0.00,
    `status`            ENUM('active','retired','lost','in_repair') NOT NULL DEFAULT 'active',
    `notes`             TEXT            DEFAULT NULL,
    `created_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_serial_number` (`serial_number`),
    KEY `idx_product_type` (`product_type`),
    KEY `idx_model` (`model`),
    KEY `idx_invoice_id` (`invoice_id`),
    KEY `idx_employee` (`employee_name`),
    KEY `idx_location` (`office_location`),
    KEY `idx_status` (`status`),
    KEY `idx_warranty_expiry` (`warranty_expiry_date`),
    CONSTRAINT `fk_assets_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ────────────────────────────────────────────────────────────
-- ACTIVITY LOGS TABLE
-- ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id`        INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `user_id`   INT UNSIGNED    DEFAULT NULL,
    `action`    VARCHAR(100)    NOT NULL,
    `details`   TEXT            DEFAULT NULL,
    `ip_address` VARCHAR(45)   DEFAULT NULL,
    `timestamp` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_action` (`action`),
    KEY `idx_timestamp` (`timestamp`),
    CONSTRAINT `fk_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ────────────────────────────────────────────────────────────
-- DEFAULT ADMIN USER
-- ⚠️  IMPORTANT: Change this password immediately after setup!
-- Default credentials: admin / admin123
-- ────────────────────────────────────────────────────────────
INSERT INTO `users` (`username`, `password_hash`, `full_name`, `role`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'admin');

-- NOTE: The password hash above is a placeholder.
-- Run this PHP one-liner on your server to generate the real hash:
--   php -r "echo password_hash('admin123', PASSWORD_DEFAULT);"
-- Then UPDATE the row:
--   UPDATE users SET password_hash='<output>' WHERE username='admin';
--
-- OR: The application's first-run setup will handle this automatically.