MAINHACK

Viewing / Editing File: 20251115201541_teal_sound.sql

-- Hesabat Arabia Ben Fresh Database Schema
-- Arabic Accounting System with Deferred Sales

-- Create database
CREATE DATABASE IF NOT EXISTS hesabat_arabia DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hesabat_arabia;

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('manager', 'accountant', 'representative') NOT NULL,
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Clients table
CREATE TABLE clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    balance DECIMAL(12,2) DEFAULT 0.00,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    unit VARCHAR(20) DEFAULT 'قطعة',
    price DECIMAL(10,2) NOT NULL,
    stock_quantity DECIMAL(10,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Shifts table
CREATE TABLE shifts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    representative_id INT NOT NULL,
    status ENUM('requested', 'active', 'end_requested', 'closed') DEFAULT 'requested',
    start_requested_at TIMESTAMP NULL,
    started_at TIMESTAMP NULL,
    started_by INT NULL,
    end_requested_at TIMESTAMP NULL,
    ended_at TIMESTAMP NULL,
    ended_by INT NULL,
    opening_cash DECIMAL(10,2) DEFAULT 0.00,
    closing_cash DECIMAL(10,2) DEFAULT 0.00,
    total_sales DECIMAL(12,2) DEFAULT 0.00,
    total_payments DECIMAL(12,2) DEFAULT 0.00,
    total_returns DECIMAL(12,2) DEFAULT 0.00,
    notes TEXT,
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (started_by) REFERENCES users(id),
    FOREIGN KEY (ended_by) REFERENCES users(id)
);

-- Shift products (stock assigned to representative)
CREATE TABLE shift_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    shift_id INT NOT NULL,
    product_id INT NOT NULL,
    assigned_quantity DECIMAL(10,2) NOT NULL,
    remaining_quantity DECIMAL(10,2) NOT NULL,
    sold_quantity DECIMAL(10,2) DEFAULT 0.00,
    returned_quantity DECIMAL(10,2) DEFAULT 0.00,
    FOREIGN KEY (shift_id) REFERENCES shifts(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Invoices table
CREATE TABLE invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    representative_id INT NOT NULL,
    shift_id INT NOT NULL,
    invoice_total DECIMAL(12,2) NOT NULL,
    amount_paid_at_creation DECIMAL(12,2) DEFAULT 0.00,
    remaining_amount DECIMAL(12,2) NOT NULL,
    previous_balance DECIMAL(12,2) NOT NULL,
    new_total_debt DECIMAL(12,2) NOT NULL,
    status ENUM('paid', 'partially_paid', 'unpaid') DEFAULT 'unpaid',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id)
);

-- Invoice items table
CREATE TABLE invoice_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Payments/Receipts table
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    receipt_number VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    representative_id INT,
    shift_id INT,
    amount DECIMAL(12,2) NOT NULL,
    previous_balance DECIMAL(12,2) NOT NULL,
    new_balance DECIMAL(12,2) NOT NULL,
    payment_type ENUM('cash', 'linked_to_invoice') DEFAULT 'cash',
    linked_invoice_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id),
    FOREIGN KEY (linked_invoice_id) REFERENCES invoices(id)
);

-- Returns table
CREATE TABLE returns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    return_number VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    representative_id INT NOT NULL,
    shift_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    return_value DECIMAL(12,2) NOT NULL,
    reason TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    processed_by INT NULL,
    client_balance_before DECIMAL(12,2),
    client_balance_after DECIMAL(12,2),
    shift_balance_adjustment DECIMAL(12,2) DEFAULT 0.00,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (representative_id) REFERENCES users(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (processed_by) REFERENCES users(id)
);

-- Audit log table
CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT,
    old_value TEXT,
    new_value TEXT,
    description TEXT,
    ip_address VARCHAR(45),
    is_edited BOOLEAN DEFAULT FALSE,
    edited_by INT NULL,
    edited_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (edited_by) REFERENCES users(id)
);

-- Insert default admin user
INSERT INTO users (username, password, full_name, role, phone, is_active) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'مدير النظام', 'manager', '+201000000000', TRUE);

-- Insert sample products
INSERT INTO products (name, unit, price, stock_quantity) VALUES 
('تجريبي', 'كيلو', 350.00, 100.00),
('منتج تجريبي 2', 'قطعة', 25.50, 200.00),
('منتج تجريبي 3', 'علبة', 75.00, 50.00);

-- Insert sample client
INSERT INTO clients (name, phone, address, balance, created_by) VALUES 
('عميل تجريبي', '+201010455459', 'عنوان تجريبي', 500.00, 1);

Cancel / Back

[ MAINHACK ]

Viewing / Editing File: 20251115201541_teal_sound.sql

Server Info

System Features

User Info