130 lines
5.5 KiB
SQL
130 lines
5.5 KiB
SQL
CREATE TABLE IF NOT EXISTS companies (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_ar VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS branches (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
company_id INT NOT NULL,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_ar VARCHAR(255) NOT NULL,
|
|
address_en TEXT,
|
|
address_ar TEXT,
|
|
phone VARCHAR(20),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
branch_id INT,
|
|
company_id INT,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
full_name_en VARCHAR(255),
|
|
full_name_ar VARCHAR(255),
|
|
role ENUM('super_admin', 'branch_manager', 'cashier') DEFAULT 'cashier',
|
|
email VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
branch_id INT,
|
|
name_en VARCHAR(255),
|
|
name_ar VARCHAR(255),
|
|
phone VARCHAR(20) NOT NULL,
|
|
email VARCHAR(100),
|
|
address_en TEXT,
|
|
address_ar TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS items (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_ar VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS services (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name_en VARCHAR(255) NOT NULL,
|
|
name_ar VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS prices (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
branch_id INT NOT NULL,
|
|
item_id INT NOT NULL,
|
|
service_id INT NOT NULL,
|
|
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY (branch_id, item_id, service_id),
|
|
FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
branch_id INT NOT NULL,
|
|
customer_id INT,
|
|
user_id INT,
|
|
order_number VARCHAR(50) UNIQUE,
|
|
status ENUM('received', 'processing', 'ready', 'delivered', 'cancelled') DEFAULT 'received',
|
|
total_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
|
|
payment_status ENUM('unpaid', 'partially_paid', 'paid') DEFAULT 'unpaid',
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (branch_id) REFERENCES branches(id),
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS order_items (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
order_id INT NOT NULL,
|
|
item_id INT NOT NULL,
|
|
service_id INT NOT NULL,
|
|
quantity INT DEFAULT 1,
|
|
unit_price DECIMAL(10, 2) NOT NULL,
|
|
subtotal DECIMAL(10, 2) NOT NULL,
|
|
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (item_id) REFERENCES items(id),
|
|
FOREIGN KEY (service_id) REFERENCES services(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS payments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
order_id INT NOT NULL,
|
|
amount DECIMAL(10, 2) NOT NULL,
|
|
payment_method ENUM('cash', 'card', 'transfer') DEFAULT 'cash',
|
|
transaction_id VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Initial data seed
|
|
INSERT IGNORE INTO companies (name_en, name_ar) VALUES ('Laundry Brand', 'علامة غسيل');
|
|
INSERT IGNORE INTO branches (company_id, name_en, name_ar) VALUES (1, 'Main Branch', 'الفرع الرئيسي');
|
|
INSERT IGNORE INTO users (branch_id, company_id, username, password_hash, full_name_en, full_name_ar, role)
|
|
VALUES (1, 1, 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'مدير', 'super_admin');
|
|
-- password is 'password'
|
|
|
|
INSERT IGNORE INTO items (name_en, name_ar) VALUES ('Shirt', 'قميص'), ('Suit', 'بدلة'), ('T-Shirt', 'تيشيرت'), ('Pants', 'بنطال'), ('Dress', 'فستان');
|
|
INSERT IGNORE INTO services (name_en, name_ar) VALUES ('Wash Only', 'غسيل فقط'), ('Iron Only', 'كوي فقط'), ('Wash & Iron', 'غسيل وكوي'), ('Dry Clean', 'تنظيف جاف');
|
|
|
|
-- Default prices for Main Branch
|
|
INSERT IGNORE INTO prices (branch_id, item_id, service_id, price) VALUES
|
|
(1, 1, 1, 5.00), (1, 1, 2, 3.00), (1, 1, 3, 7.00),
|
|
(1, 2, 4, 30.00),
|
|
(1, 3, 3, 5.00),
|
|
(1, 4, 3, 6.00);
|