-- MySQL schema for مخزوني برو

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fullName VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','staff') NOT NULL DEFAULT 'staff'
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(30),
  email VARCHAR(100)
);

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description VARCHAR(255)
);

CREATE TABLE suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  contactPerson VARCHAR(100),
  phone VARCHAR(30)
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  barcode VARCHAR(50),
  category VARCHAR(50),
  price DECIMAL(10,2) NOT NULL,
  cost DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL,
  minStock INT NOT NULL,
  supplierId INT,
  FOREIGN KEY (supplierId) REFERENCES suppliers(id) ON DELETE SET NULL
);

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customerName VARCHAR(100),
  date VARCHAR(20),
  timestamp BIGINT,
  totalAmount DECIMAL(10,2),
  discount DECIMAL(10,2),
  finalAmount DECIMAL(10,2)
);

CREATE TABLE invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT,
  product_id INT,
  name VARCHAR(100),
  price DECIMAL(10,2),
  quantity INT,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);

CREATE TABLE store_settings (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  address VARCHAR(255),
  currency VARCHAR(10),
  phone1 VARCHAR(30),
  phone2 VARCHAR(30)
);

INSERT INTO store_settings (id, name, address, currency, phone1, phone2) VALUES (1, 'مخزوني برو', 'الدار البيضاء، المغرب', 'MAD', '0522000000', '');
