-- Wrapz N Fryz CRM — clean installation schema (MariaDB 10.6+ / MySQL 8+)
-- Import into a NEW EMPTY database. Do not import the Nosh dump into this database.
SET NAMES utf8mb4;
CREATE TABLE franchises (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,code VARCHAR(40) NOT NULL UNIQUE,contact_name VARCHAR(190) DEFAULT '',phone VARCHAR(30) DEFAULT '',email VARCHAR(190) DEFAULT '',status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE stores (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,franchise_id INT UNSIGNED NOT NULL,name VARCHAR(190) NOT NULL,code VARCHAR(40) NOT NULL UNIQUE,city VARCHAR(100) DEFAULT '',address TEXT,status ENUM('Active','Inactive') DEFAULT 'Active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(franchise_id),FOREIGN KEY(franchise_id) REFERENCES franchises(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE crm_users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,franchise_id INT UNSIGNED NULL,store_id INT UNSIGNED NULL,name VARCHAR(190) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('MasterAdmin','FranchiseOwner','StoreOwner') NOT NULL,status ENUM('Active','Inactive') DEFAULT 'Active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(franchise_id),INDEX(store_id),FOREIGN KEY(franchise_id) REFERENCES franchises(id),FOREIGN KEY(store_id) REFERENCES stores(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE vendors(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,contact_person VARCHAR(190) DEFAULT '',phone VARCHAR(30) DEFAULT '',email VARCHAR(190) DEFAULT '',gstin VARCHAR(32) DEFAULT '',status ENUM('Active','Inactive') DEFAULT 'Active') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE categories(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,status ENUM('Active','Inactive') DEFAULT 'Active') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE products(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,category_id INT UNSIGNED NULL,sku VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,unit VARCHAR(30) DEFAULT 'pcs',purchase_price DECIMAL(13,2) NOT NULL DEFAULT 0,selling_price DECIMAL(13,2) NOT NULL DEFAULT 0,reorder_level DECIMAL(13,2) NOT NULL DEFAULT 0,status ENUM('Active','Inactive') DEFAULT 'Active',FOREIGN KEY(category_id) REFERENCES categories(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE warehouses(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,code VARCHAR(40) NOT NULL UNIQUE,location VARCHAR(255) DEFAULT '',status ENUM('Active','Inactive') DEFAULT 'Active') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE stock_balances(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id INT UNSIGNED NOT NULL,store_id INT UNSIGNED NOT NULL DEFAULT 0,warehouse_id INT UNSIGNED NOT NULL DEFAULT 0,quantity DECIMAL(14,2) DEFAULT 0,UNIQUE KEY uq_balance(product_id,store_id,warehouse_id),FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE stock_movements(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id INT UNSIGNED NOT NULL,store_id INT UNSIGNED NOT NULL DEFAULT 0,warehouse_id INT UNSIGNED NOT NULL DEFAULT 0,movement ENUM('IN','OUT') NOT NULL,quantity DECIMAL(14,2) NOT NULL,reason VARCHAR(255) DEFAULT '',user_id INT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(product_id,created_at),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(user_id) REFERENCES crm_users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE orders(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_number VARCHAR(50) NOT NULL UNIQUE,franchise_id INT UNSIGNED NOT NULL,store_id INT UNSIGNED NOT NULL,created_by INT UNSIGNED NOT NULL,status ENUM('Submitted','Approved','Preparing','Dispatched','Delivered','Cancelled') NOT NULL DEFAULT 'Submitted',total DECIMAL(14,2) DEFAULT 0,requested_delivery DATE DEFAULT NULL,notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(franchise_id,store_id),FOREIGN KEY(franchise_id) REFERENCES franchises(id),FOREIGN KEY(store_id) REFERENCES stores(id),FOREIGN KEY(created_by) REFERENCES crm_users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE order_items(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id BIGINT UNSIGNED NOT NULL,product_id INT UNSIGNED NOT NULL,quantity DECIMAL(14,2) NOT NULL,unit_price DECIMAL(13,2) NOT NULL,line_total DECIMAL(14,2) NOT NULL,FOREIGN KEY(order_id) REFERENCES orders(id),FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE invoices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,invoice_number VARCHAR(60) NOT NULL UNIQUE,order_id BIGINT UNSIGNED NOT NULL UNIQUE,franchise_id INT UNSIGNED NOT NULL,store_id INT UNSIGNED NOT NULL,issue_date DATE NOT NULL,total DECIMAL(14,2) NOT NULL,balance_due DECIMAL(14,2) NOT NULL,status ENUM('Unpaid','Part Paid','Paid') DEFAULT 'Unpaid',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(order_id) REFERENCES orders(id),INDEX(franchise_id,store_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE payments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,invoice_id BIGINT UNSIGNED NOT NULL,franchise_id INT UNSIGNED NOT NULL,store_id INT UNSIGNED NOT NULL,amount DECIMAL(14,2) NOT NULL,method VARCHAR(30) DEFAULT '',reference VARCHAR(100) DEFAULT '',paid_on DATE NOT NULL,created_by INT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(franchise_id,store_id),FOREIGN KEY(invoice_id) REFERENCES invoices(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE expenses(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,franchise_id INT UNSIGNED NOT NULL,store_id INT UNSIGNED NOT NULL,expense_date DATE NOT NULL,category VARCHAR(100) NOT NULL,amount DECIMAL(14,2) NOT NULL,notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(franchise_id,store_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE purchase_orders(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,vendor_id INT UNSIGNED NOT NULL,warehouse_id INT UNSIGNED NOT NULL,reference VARCHAR(100) DEFAULT '',order_date DATE NOT NULL,expected_date DATE DEFAULT NULL,status ENUM('Draft','Submitted','Approved','Closed','Cancelled') DEFAULT 'Draft',notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(vendor_id) REFERENCES vendors(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE purchase_order_items(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,purchase_order_id BIGINT UNSIGNED NOT NULL,product_id INT UNSIGNED NOT NULL,quantity DECIMAL(14,2) NOT NULL,unit_cost DECIMAL(13,2) NOT NULL,FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id),FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE stock_transfers(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,source_warehouse_id INT UNSIGNED DEFAULT NULL,destination_store_id INT UNSIGNED DEFAULT NULL,reference VARCHAR(100) DEFAULT '',status ENUM('Draft','Submitted','Approved','Closed','Cancelled') DEFAULT 'Draft',created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE wastage(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,franchise_id INT UNSIGNED NOT NULL,store_id INT UNSIGNED NOT NULL,product_id INT UNSIGNED NOT NULL,quantity DECIMAL(14,2) NOT NULL,reason VARCHAR(255) DEFAULT '',recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(franchise_id,store_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE enquiries(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,phone VARCHAR(30) DEFAULT '',email VARCHAR(190) DEFAULT '',location VARCHAR(190) DEFAULT '',status VARCHAR(40) NOT NULL DEFAULT 'New',next_followup DATE DEFAULT NULL,remarks TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE notifications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,franchise_id INT UNSIGNED DEFAULT NULL,store_id INT UNSIGNED DEFAULT NULL,title VARCHAR(190) NOT NULL,body TEXT,is_read TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(user_id,is_read)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE activity_log(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,action VARCHAR(100) NOT NULL,entity_type VARCHAR(80) NOT NULL,entity_id BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(created_at),FOREIGN KEY(user_id) REFERENCES crm_users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO categories(name) VALUES ('Wrap ingredients'),('Fries & seasonings'),('Beverage supplies'),('Packaging'),('Sauces & condiments');

-- CI4 advanced operational extensions
CREATE TABLE IF NOT EXISTS order_status_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL,old_status VARCHAR(30) NOT NULL,new_status VARCHAR(30) NOT NULL,changed_by INT UNSIGNED NOT NULL,changed_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX(order_id,changed_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_receipts(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,purchase_order_id BIGINT UNSIGNED NOT NULL,product_id INT UNSIGNED NOT NULL,quantity DECIMAL(14,2) NOT NULL,received_by INT UNSIGNED NOT NULL,received_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX(purchase_order_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;