-- Gold Jewellery Software Database
-- MySQL 8.4 LTS
CREATE DATABASE IF NOT EXISTS gold_jewellery CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gold_jewellery;
SET FOREIGN_KEY_CHECKS=0;
DROP VIEW IF EXISTS vw_inventory_summary;
DROP VIEW IF EXISTS vw_customer_outstanding;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS sales_items;
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS stock_movements;
DROP TABLE IF EXISTS karagir_transactions;
DROP TABLE IF EXISTS purchase_items;
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS jewellery_items;
DROP TABLE IF EXISTS gold_rates;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS karagirs;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50) NOT NULL UNIQUE,
 description VARCHAR(255),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 role_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 phone VARCHAR(30),
 is_active BOOLEAN NOT NULL DEFAULT TRUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_code VARCHAR(50) NOT NULL UNIQUE,
 name VARCHAR(150) NOT NULL,
 phone VARCHAR(30),
 email VARCHAR(190),
 address TEXT,
 gstin VARCHAR(30),
 opening_balance DECIMAL(14,2) DEFAULT 0,
 credit_limit DECIMAL(14,2) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(phone), INDEX(name)
) ENGINE=InnoDB;

CREATE TABLE suppliers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 supplier_code VARCHAR(50) NOT NULL UNIQUE,
 name VARCHAR(150) NOT NULL,
 phone VARCHAR(30), email VARCHAR(190), address TEXT, gstin VARCHAR(30),
 opening_balance DECIMAL(14,2) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(phone)
) ENGINE=InnoDB;

CREATE TABLE karagirs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 karagir_code VARCHAR(50) NOT NULL UNIQUE,
 name VARCHAR(150) NOT NULL,
 phone VARCHAR(30), address TEXT, skill VARCHAR(150),
 opening_gold_weight DECIMAL(12,3) DEFAULT 0,
 opening_balance DECIMAL(14,2) DEFAULT 0,
 is_active BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE gold_rates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 rate_date DATE NOT NULL,
 purity VARCHAR(20) NOT NULL,
 rate_per_gram DECIMAL(14,2) NOT NULL,
 source VARCHAR(100),
 created_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_gold_rate(rate_date,purity),
 INDEX(rate_date),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE jewellery_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 item_code VARCHAR(80) NOT NULL UNIQUE,
 barcode VARCHAR(100) UNIQUE,
 name VARCHAR(200) NOT NULL,
 category VARCHAR(100), subcategory VARCHAR(100), design_no VARCHAR(100),
 purity VARCHAR(20) NOT NULL,
 gross_weight DECIMAL(12,3) DEFAULT 0,
 stone_weight DECIMAL(12,3) DEFAULT 0,
 other_weight DECIMAL(12,3) DEFAULT 0,
 net_weight DECIMAL(12,3) GENERATED ALWAYS AS
   (GREATEST(gross_weight-stone_weight-other_weight,0)) STORED,
 making_charge_type ENUM('AMOUNT','PER_GRAM','PERCENT') DEFAULT 'AMOUNT',
 making_charge DECIMAL(14,2) DEFAULT 0,
 purchase_cost DECIMAL(14,2) DEFAULT 0,
 status ENUM('AVAILABLE','RESERVED','SOLD','MELTED','RETURNED','DAMAGED') DEFAULT 'AVAILABLE',
 location VARCHAR(100), notes TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(status), INDEX(category), INDEX(purity)
) ENGINE=InnoDB;

CREATE TABLE purchases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_no VARCHAR(50) NOT NULL UNIQUE,
 supplier_id BIGINT UNSIGNED NOT NULL,
 purchase_date DATE NOT NULL,
 subtotal DECIMAL(14,2) DEFAULT 0,
 discount DECIMAL(14,2) DEFAULT 0,
 tax DECIMAL(14,2) DEFAULT 0,
 total_amount DECIMAL(14,2) DEFAULT 0,
 paid_amount DECIMAL(14,2) DEFAULT 0,
 status ENUM('DRAFT','POSTED','CANCELLED') DEFAULT 'DRAFT',
 notes TEXT, created_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(supplier_id) REFERENCES suppliers(id),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(purchase_date)
) ENGINE=InnoDB;

CREATE TABLE purchase_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_id BIGINT UNSIGNED NOT NULL,
 jewellery_item_id BIGINT UNSIGNED NULL,
 item_code VARCHAR(80) NOT NULL,
 description VARCHAR(255),
 gross_weight DECIMAL(12,3) DEFAULT 0,
 stone_weight DECIMAL(12,3) DEFAULT 0,
 net_weight DECIMAL(12,3) DEFAULT 0,
 purity VARCHAR(20) NOT NULL,
 rate_per_gram DECIMAL(14,2) DEFAULT 0,
 making_charge DECIMAL(14,2) DEFAULT 0,
 amount DECIMAL(14,2) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
 FOREIGN KEY(jewellery_item_id) REFERENCES jewellery_items(id) ON DELETE SET NULL,
 INDEX(purchase_id)
) ENGINE=InnoDB;

CREATE TABLE stock_movements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 jewellery_item_id BIGINT UNSIGNED NOT NULL,
 movement_type ENUM('PURCHASE','SALE','SALE_RETURN','TRANSFER_IN','TRANSFER_OUT',
 'KARAGIR_ISSUE','KARAGIR_RECEIVE','ADJUSTMENT','MELTING') NOT NULL,
 reference_type VARCHAR(50), reference_id BIGINT UNSIGNED,
 quantity DECIMAL(12,3) DEFAULT 1, weight DECIMAL(12,3) DEFAULT 0,
 location_from VARCHAR(100), location_to VARCHAR(100),
 movement_date DATETIME DEFAULT CURRENT_TIMESTAMP,
 created_by BIGINT UNSIGNED NULL, notes TEXT,
 FOREIGN KEY(jewellery_item_id) REFERENCES jewellery_items(id),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(jewellery_item_id,movement_date), INDEX(reference_type,reference_id)
) ENGINE=InnoDB;

CREATE TABLE karagir_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 transaction_no VARCHAR(50) NOT NULL UNIQUE,
 karagir_id BIGINT UNSIGNED NOT NULL,
 transaction_date DATE NOT NULL,
 transaction_type ENUM('ISSUE','RECEIVE','ADJUSTMENT','PAYMENT') NOT NULL,
 gold_weight DECIMAL(12,3) DEFAULT 0, purity VARCHAR(20),
 wastage_weight DECIMAL(12,3) DEFAULT 0, making_charge DECIMAL(14,2) DEFAULT 0,
 amount DECIMAL(14,2) DEFAULT 0,
 jewellery_item_id BIGINT UNSIGNED NULL, reference_no VARCHAR(80), notes TEXT,
 created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(karagir_id) REFERENCES karagirs(id),
 FOREIGN KEY(jewellery_item_id) REFERENCES jewellery_items(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(karagir_id,transaction_date)
) ENGINE=InnoDB;

CREATE TABLE orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_no VARCHAR(50) NOT NULL UNIQUE,
 customer_id BIGINT UNSIGNED NOT NULL,
 order_date DATE NOT NULL,
 expected_delivery_date DATE NULL,
 status ENUM('DRAFT','CONFIRMED','IN_PRODUCTION','READY','DELIVERED','CANCELLED') DEFAULT 'DRAFT',
 subtotal DECIMAL(14,2) DEFAULT 0, discount DECIMAL(14,2) DEFAULT 0,
 tax DECIMAL(14,2) DEFAULT 0, total_amount DECIMAL(14,2) DEFAULT 0,
 advance_amount DECIMAL(14,2) DEFAULT 0, balance_amount DECIMAL(14,2) DEFAULT 0,
 notes TEXT, created_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(customer_id) REFERENCES customers(id),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(customer_id), INDEX(status)
) ENGINE=InnoDB;

CREATE TABLE order_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL,
 jewellery_item_id BIGINT UNSIGNED NULL,
 description VARCHAR(255) NOT NULL, purity VARCHAR(20),
 gross_weight DECIMAL(12,3) DEFAULT 0, net_weight DECIMAL(12,3) DEFAULT 0,
 estimated_amount DECIMAL(14,2) DEFAULT 0, making_charge DECIMAL(14,2) DEFAULT 0,
 quantity INT UNSIGNED DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
 FOREIGN KEY(jewellery_item_id) REFERENCES jewellery_items(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE sales (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 invoice_no VARCHAR(50) NOT NULL UNIQUE,
 customer_id BIGINT UNSIGNED NULL,
 sale_date DATETIME DEFAULT CURRENT_TIMESTAMP,
 subtotal DECIMAL(14,2) DEFAULT 0, making_charges DECIMAL(14,2) DEFAULT 0,
 stone_charges DECIMAL(14,2) DEFAULT 0, discount DECIMAL(14,2) DEFAULT 0,
 tax DECIMAL(14,2) DEFAULT 0, total_amount DECIMAL(14,2) DEFAULT 0,
 paid_amount DECIMAL(14,2) DEFAULT 0, balance_amount DECIMAL(14,2) DEFAULT 0,
 status ENUM('DRAFT','POSTED','CANCELLED','RETURNED') DEFAULT 'DRAFT',
 created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(sale_date), INDEX(customer_id)
) ENGINE=InnoDB;

CREATE TABLE sales_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sale_id BIGINT UNSIGNED NOT NULL,
 jewellery_item_id BIGINT UNSIGNED NOT NULL,
 item_code VARCHAR(80) NOT NULL, purity VARCHAR(20) NOT NULL,
 gross_weight DECIMAL(12,3) DEFAULT 0, stone_weight DECIMAL(12,3) DEFAULT 0,
 net_weight DECIMAL(12,3) DEFAULT 0, gold_rate DECIMAL(14,2) DEFAULT 0,
 gold_value DECIMAL(14,2) DEFAULT 0, making_charge DECIMAL(14,2) DEFAULT 0,
 stone_charge DECIMAL(14,2) DEFAULT 0, discount DECIMAL(14,2) DEFAULT 0,
 tax DECIMAL(14,2) DEFAULT 0, line_total DECIMAL(14,2) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(sale_id) REFERENCES sales(id) ON DELETE CASCADE,
 FOREIGN KEY(jewellery_item_id) REFERENCES jewellery_items(id),
 INDEX(sale_id)
) ENGINE=InnoDB;

CREATE TABLE payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 payment_no VARCHAR(50) NOT NULL UNIQUE,
 customer_id BIGINT UNSIGNED NULL, sale_id BIGINT UNSIGNED NULL, order_id BIGINT UNSIGNED NULL,
 payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
 payment_type ENUM('RECEIPT','REFUND','ADVANCE','ADJUSTMENT') NOT NULL,
 mode ENUM('CASH','CARD','UPI','BANK_TRANSFER','CHEQUE','OTHER') NOT NULL,
 amount DECIMAL(14,2) NOT NULL, reference_no VARCHAR(100), notes TEXT,
 created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL,
 FOREIGN KEY(sale_id) REFERENCES sales(id) ON DELETE SET NULL,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(payment_date), INDEX(customer_id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL,
 table_name VARCHAR(100), record_id BIGINT UNSIGNED,
 old_data JSON, new_data JSON, ip_address VARCHAR(45),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(table_name,record_id), INDEX(user_id,created_at)
) ENGINE=InnoDB;

CREATE OR REPLACE VIEW vw_inventory_summary AS
SELECT id,item_code,barcode,name,category,purity,gross_weight,net_weight,status,location
FROM jewellery_items WHERE status IN ('AVAILABLE','RESERVED');

CREATE OR REPLACE VIEW vw_customer_outstanding AS
SELECT c.id,c.customer_code,c.name,c.phone,
       COALESCE(SUM(s.balance_amount),0) AS sales_balance,
       COALESCE(SUM(o.balance_amount),0) AS order_balance
FROM customers c
LEFT JOIN sales s ON s.customer_id=c.id AND s.status='POSTED'
LEFT JOIN orders o ON o.customer_id=c.id AND o.status NOT IN ('CANCELLED','DELIVERED')
GROUP BY c.id,c.customer_code,c.name,c.phone;

INSERT INTO roles(name,description) VALUES
('ADMIN','Full system access'),
('MANAGER','Business and reporting access'),
('SALES','Sales and invoice access'),
('INVENTORY','Inventory and stock access'),
('KARAGIR','Karagir account access')
ON DUPLICATE KEY UPDATE description=VALUES(description);

-- Create application users from PHP with password_hash().
-- Do not store plain-text passwords.