-- ============================================================
--  سیستم مدیریت انبار و فروش (ERP)
--  فایل ساختار دیتابیس MySQL
--  نسخه: 1.0
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS inventory_erp2
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_persian_ci;

USE inventory_erp2;

-- ============================================================
-- جدول کاربران سیستم (ادمین، فروشنده، انباردار)
-- ============================================================
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50)  NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(100) DEFAULT NULL,
    role            VARCHAR(30) NOT NULL DEFAULT 'seller',
    status          ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_username (username),
    KEY idx_role (role),
    KEY idx_status (status)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول مشتریان
-- ============================================================
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    phone       VARCHAR(30)  DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_name (name),
    KEY idx_phone (phone)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول اجناس / محصولات
-- barcode کلید اصلی جستجو است (UNIQUE + INDEX)
-- ============================================================
DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    code            VARCHAR(50)  DEFAULT NULL,
    barcode         VARCHAR(64)  NOT NULL,
    size            VARCHAR(50)  DEFAULT NULL,
    purchase_price  DECIMAL(14,2) NOT NULL DEFAULT 0,
    sale_price      DECIMAL(14,2) NOT NULL DEFAULT 0,
    min_stock_alert INT UNSIGNED NOT NULL DEFAULT 5,
    status          ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_barcode (barcode),
    KEY idx_code (code),
    KEY idx_name (name),
    KEY idx_status (status)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول موجودی انبار (CACHE فقط - منبع اصلی stock_movements است)
-- ============================================================
DROP TABLE IF EXISTS stock;
CREATE TABLE stock (
    product_id  INT UNSIGNED NOT NULL PRIMARY KEY,
    quantity    INT NOT NULL DEFAULT 0,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_stock_product FOREIGN KEY (product_id)
        REFERENCES products(id) ON DELETE CASCADE,
    KEY idx_quantity (quantity)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول تاریخچه حرکات انبار (منبع اصلی و واقعی موجودی)
-- type: in (ورودی خرید), out (خروجی دستی), sale (فروش), consume (مصرف داخلی)
-- ============================================================
DROP TABLE IF EXISTS stock_movements;
CREATE TABLE stock_movements (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id  INT UNSIGNED NOT NULL,
    type        ENUM('in','out','sale','consume') NOT NULL,
    quantity    INT NOT NULL,
    price       DECIMAL(14,2) DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    ref_id      BIGINT UNSIGNED DEFAULT NULL COMMENT 'مثلا sale_id مربوطه',
    created_by  INT UNSIGNED DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_movement_product FOREIGN KEY (product_id)
        REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_movement_user FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,
    KEY idx_product_date (product_id, created_at),
    KEY idx_type (type),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول فروش‌ها (سرفصل فاکتور)
-- ============================================================
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_name   VARCHAR(150) DEFAULT NULL,
    customer_id     INT UNSIGNED DEFAULT NULL,
    total           DECIMAL(14,2) NOT NULL DEFAULT 0,
    discount        DECIMAL(14,2) NOT NULL DEFAULT 0,
    paid_amount     DECIMAL(14,2) NOT NULL DEFAULT 0,
    status          ENUM('completed','cancelled') NOT NULL DEFAULT 'completed',
    created_by      INT UNSIGNED DEFAULT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sale_customer FOREIGN KEY (customer_id)
        REFERENCES customers(id) ON DELETE SET NULL,
    CONSTRAINT fk_sale_user FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,
    KEY idx_created_at (created_at),
    KEY idx_customer (customer_id),
    KEY idx_status (status)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول اقلام فروش (خطوط فاکتور)
-- ============================================================
DROP TABLE IF EXISTS sale_items;
CREATE TABLE sale_items (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sale_id     BIGINT UNSIGNED NOT NULL,
    product_id  INT UNSIGNED NOT NULL,
    quantity    INT NOT NULL,
    price       DECIMAL(14,2) NOT NULL,
    cost_price  DECIMAL(14,2) NOT NULL DEFAULT 0 COMMENT 'قیمت خرید لحظه فروش برای محاسبه دقیق سود',
    CONSTRAINT fk_item_sale FOREIGN KEY (sale_id)
        REFERENCES sales(id) ON DELETE CASCADE,
    CONSTRAINT fk_item_product FOREIGN KEY (product_id)
        REFERENCES products(id) ON DELETE RESTRICT,
    KEY idx_sale (sale_id),
    KEY idx_product (product_id)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول مصارف (هزینه‌های متفرقه: معاش، غذا، ترانسپورت، قبوض و ...)
-- ============================================================
DROP TABLE IF EXISTS expenses;
CREATE TABLE expenses (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category    VARCHAR(30) NOT NULL COMMENT 'salary|food|transport|utilities|other',
    title       VARCHAR(150) NOT NULL,
    amount      DECIMAL(14,2) NOT NULL,
    note        TEXT NULL,
    spent_at    DATE NOT NULL,
    created_by  INT UNSIGNED NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_expense_user FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE RESTRICT,
    KEY idx_category (category),
    KEY idx_spent_at (spent_at),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- جدول لاگ ممیزی (Audit Log): ثبت تمام فعالیت‌ها و تغییرات سیستم
-- بدون کلید خارجی روی user_id تا لاگ‌ها پس از حذف کاربر هم باقی بمانند.
-- ============================================================
DROP TABLE IF EXISTS audit_logs;
CREATE TABLE audit_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NULL,
    username    VARCHAR(64) NULL,
    action      VARCHAR(40) NULL,
    entity      VARCHAR(40) NULL,
    entity_id   VARCHAR(64) NULL,
    method      VARCHAR(8) NULL,
    endpoint    VARCHAR(191) NULL,
    status_code SMALLINT UNSIGNED NULL,
    details     TEXT NULL,
    ip          VARCHAR(45) NULL,
    user_agent  VARCHAR(255) NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_user (user_id),
    KEY idx_action (action),
    KEY idx_entity (entity, entity_id),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- ============================================================
-- کنترل دسترسی مبتنی بر نقش (RBAC): نقش‌ها، مجوزها و نگاشت آن‌ها
-- ============================================================
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;

CREATE TABLE roles (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code       VARCHAR(30) NOT NULL,
    name       VARCHAR(60) NOT NULL,
    is_system  TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'نقش‌های سیستمی قابل حذف نیستند',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_code (code)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

CREATE TABLE permissions (
    code  VARCHAR(40) PRIMARY KEY,
    label VARCHAR(80) NOT NULL,
    grp   VARCHAR(40) NOT NULL DEFAULT ''
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

CREATE TABLE role_permissions (
    role_id         INT UNSIGNED NOT NULL,
    permission_code VARCHAR(40) NOT NULL,
    PRIMARY KEY (role_id, permission_code),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_rp_perm FOREIGN KEY (permission_code) REFERENCES permissions(code) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_persian_ci;

-- کاتالوگ مجوزها
INSERT INTO permissions (code, label, grp) VALUES
    ('sales.create',     'ثبت فروش (صندوق)',          'فروش'),
    ('sales.view',       'مشاهده فاکتورها',           'فروش'),
    ('products.manage',  'مدیریت اجناس (افزودن/ویرایش)', 'انبار'),
    ('products.delete',  'حذف/غیرفعال‌سازی اجناس',     'انبار'),
    ('stock.manage',     'عملیات انبار (ورود/خروج)',   'انبار'),
    ('customers.manage', 'مدیریت مشتریان',            'مشتریان'),
    ('expenses.manage',  'مدیریت مصارف',              'مالی'),
    ('dashboard.view',   'مشاهده داشبورد',            'گزارش'),
    ('reports.view',     'گزارش‌ها و تحلیل',           'گزارش'),
    ('users.manage',     'مدیریت کاربران',            'مدیریت سیستم'),
    ('roles.manage',     'مدیریت نقش‌ها و دسترسی‌ها',   'مدیریت سیستم'),
    ('audit.view',       'مشاهده لاگ ممیزی',          'مدیریت سیستم');

-- نقش‌های پیش‌فرض سیستم
INSERT INTO roles (code, name, is_system) VALUES
    ('admin',     'مدیر سیستم', 1),
    ('seller',    'فروشنده',    1),
    ('warehouse', 'انباردار',   1);

-- مدیر سیستم: تمام مجوزها
INSERT INTO role_permissions (role_id, permission_code)
SELECT r.id, p.code FROM roles r CROSS JOIN permissions p WHERE r.code = 'admin';

-- فروشنده: فروش + مشتریان
INSERT INTO role_permissions (role_id, permission_code)
SELECT r.id, c.code FROM roles r
JOIN (SELECT 'sales.create' AS code UNION SELECT 'sales.view' UNION SELECT 'customers.manage') c
WHERE r.code = 'seller';

-- انباردار: اجناس + انبار + مشاهده فاکتور و مشتریان
INSERT INTO role_permissions (role_id, permission_code)
SELECT r.id, c.code FROM roles r
JOIN (SELECT 'products.manage' AS code UNION SELECT 'stock.manage' UNION SELECT 'sales.view' UNION SELECT 'customers.manage') c
WHERE r.code = 'warehouse';

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- TRIGGER ها: حفظ یکپارچگی stock (cache) با stock_movements
-- هر گونه درج در stock_movements به صورت خودکار موجودی stock
-- را به‌روزرسانی می‌کند. هیچ کد PHP اجازه ندارد مستقیم
-- روی جدول stock بنویسد بدون درج movement.
-- ============================================================
DROP TRIGGER IF EXISTS trg_stock_after_movement_insert;
DELIMITER $$
CREATE TRIGGER trg_stock_after_movement_insert
AFTER INSERT ON stock_movements
FOR EACH ROW
BEGIN
    INSERT INTO stock (product_id, quantity)
    VALUES (
        NEW.product_id,
        CASE WHEN NEW.type = 'in' THEN NEW.quantity ELSE -NEW.quantity END
    )
    ON DUPLICATE KEY UPDATE
        quantity = quantity + (CASE WHEN NEW.type = 'in' THEN NEW.quantity ELSE -NEW.quantity END);
END$$
DELIMITER ;

-- ============================================================
-- نکته مهم: کاربر ادمین پیش‌فرض در این فایل ساخته نمی‌شود
-- چون هش رمز عبور باید توسط خود PHP (password_hash) تولید شود.
-- بعد از ایمپورت این فایل، اسکریپت زیر را یک بار در مرورگر باز کنید:
--     http://localhost/inventory-erp/database/setup_admin.php
-- این اسکریپت کاربر ادمین را با رمز انتخابی شما می‌سازد و
-- بعد از اجرای موفق، خودش را غیرفعال می‌کند.
-- ============================================================