SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, label VARCHAR(100) NOT NULL, permissions JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS 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, phone VARCHAR(30), password VARCHAR(255) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME NULL, 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 IF NOT EXISTS employees (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, full_name VARCHAR(150) NOT NULL, phone VARCHAR(30), job_title VARCHAR(100), start_date DATE, monthly_salary DECIMAL(14,2) DEFAULT 0, daily_hours DECIMAL(5,2) DEFAULT 7.5, active TINYINT(1) DEFAULT 1, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_name VARCHAR(190) NOT NULL, contact_name VARCHAR(150), phone VARCHAR(30), whatsapp VARCHAR(30), email VARCHAR(190), tax_office VARCHAR(100), tax_number VARCHAR(30), address TEXT, billing_address TEXT, customer_type VARCHAR(50) DEFAULT 'Kurumsal', notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(company_name), INDEX(phone), INDEX(tax_number)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customer_addresses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT UNSIGNED NOT NULL, label VARCHAR(100), address TEXT NOT NULL, latitude DECIMAL(10,7), longitude DECIMAL(10,7), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS jobs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_no VARCHAR(30) NOT NULL UNIQUE, customer_id BIGINT UNSIGNED NOT NULL, title VARCHAR(190) NOT NULL, job_type VARCHAR(100), description TEXT, address TEXT, request_date DATE, measurement_date DATETIME, due_date DATE, installation_date DATETIME, assigned_employee_id BIGINT UNSIGNED NULL, priority ENUM('low','normal','high','urgent') DEFAULT 'normal', status VARCHAR(50) DEFAULT 'request_received', sale_total DECIMAL(14,2) DEFAULT 0, estimated_cost DECIMAL(14,2) DEFAULT 0, actual_cost DECIMAL(14,2) DEFAULT 0, 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(assigned_employee_id) REFERENCES employees(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(status), INDEX(due_date)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS job_status_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(50), new_status VARCHAR(50) NOT NULL, note TEXT, changed_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE, FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS measurements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NULL, notes TEXT, measured_at DATETIME DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS measurement_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, measurement_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, width DECIMAL(12,3) NOT NULL DEFAULT 0, height DECIMAL(12,3) NOT NULL DEFAULT 0, quantity DECIMAL(12,2) NOT NULL DEFAULT 1, unit VARCHAR(20) DEFAULT 'metre', area_m2 DECIMAL(14,3) GENERATED ALWAYS AS (width * height * quantity) STORED, notes TEXT,
 FOREIGN KEY(measurement_id) REFERENCES measurements(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS material_categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS materials (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id BIGINT UNSIGNED NULL, name VARCHAR(190) NOT NULL, unit VARCHAR(30) NOT NULL DEFAULT 'adet', purchase_price DECIMAL(14,2) DEFAULT 0, sale_price DECIMAL(14,2) DEFAULT 0, default_margin DECIMAL(6,2) DEFAULT 0, stock_qty DECIMAL(14,3) DEFAULT 0, min_stock_qty DECIMAL(14,3) DEFAULT 0, active TINYINT(1) DEFAULT 1, price_updated_at DATETIME, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(category_id) REFERENCES material_categories(id) ON DELETE SET NULL, UNIQUE(name)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS material_prices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, material_id BIGINT UNSIGNED NOT NULL, supplier_id BIGINT UNSIGNED NULL, price_type ENUM('purchase','sale') DEFAULT 'purchase', price DECIMAL(14,2) NOT NULL, effective_at DATETIME DEFAULT CURRENT_TIMESTAMP, created_by BIGINT UNSIGNED NULL,
 INDEX(material_id,effective_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS job_materials (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_id BIGINT UNSIGNED NOT NULL, material_id BIGINT UNSIGNED NULL, description VARCHAR(255), quantity DECIMAL(14,3) NOT NULL, unit VARCHAR(30), unit_cost DECIMAL(14,2) DEFAULT 0, unit_price DECIMAL(14,2) DEFAULT 0, total_cost DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_cost) STORED, total_price DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
 FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE, FOREIGN KEY(material_id) REFERENCES materials(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS job_costs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_id BIGINT UNSIGNED NOT NULL, cost_type VARCHAR(50) NOT NULL, description VARCHAR(255), estimated_amount DECIMAL(14,2) DEFAULT 0, actual_amount DECIMAL(14,2) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS quotes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quote_no VARCHAR(40) NOT NULL UNIQUE, job_id BIGINT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL, version INT NOT NULL DEFAULT 1, status VARCHAR(30) DEFAULT 'draft', subtotal DECIMAL(14,2) DEFAULT 0, discount_type ENUM('none','percent','amount') DEFAULT 'none', discount_value DECIMAL(14,2) DEFAULT 0, tax_rate DECIMAL(6,2) DEFAULT 20, tax_amount DECIMAL(14,2) DEFAULT 0, grand_total DECIMAL(14,2) DEFAULT 0, valid_until DATE, delivery_time VARCHAR(100), payment_terms TEXT, notes TEXT, approved_at DATETIME, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES jobs(id), FOREIGN KEY(customer_id) REFERENCES customers(id), FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS quote_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quote_id BIGINT UNSIGNED NOT NULL, description VARCHAR(255) NOT NULL, measurement VARCHAR(100), quantity DECIMAL(14,3) DEFAULT 1, unit VARCHAR(30), unit_price DECIMAL(14,2) DEFAULT 0, total DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED, sort_order INT DEFAULT 0,
 FOREIGN KEY(quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS quote_versions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quote_id BIGINT UNSIGNED NOT NULL, version INT NOT NULL, snapshot JSON NOT NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS quote_approvals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quote_id BIGINT UNSIGNED NOT NULL, approval_token CHAR(64) NOT NULL UNIQUE, approver_name VARCHAR(150), phone VARCHAR(30), status VARCHAR(30) DEFAULT 'pending', approved_at DATETIME, ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS contracts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, contract_no VARCHAR(40) NOT NULL UNIQUE, job_id BIGINT UNSIGNED NOT NULL, quote_id BIGINT UNSIGNED NOT NULL, content LONGTEXT, deposit_amount DECIMAL(14,2) DEFAULT 0, remaining_amount DECIMAL(14,2) DEFAULT 0, status VARCHAR(30) DEFAULT 'draft', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES jobs(id), FOREIGN KEY(quote_id) REFERENCES quotes(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS work_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_id BIGINT UNSIGNED NOT NULL, stage VARCHAR(50) DEFAULT 'approved', notes TEXT, started_at DATETIME, completed_at DATETIME, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS work_order_assignments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, work_order_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, task VARCHAR(255), status VARCHAR(30) DEFAULT 'assigned', started_at DATETIME, completed_at DATETIME,
 FOREIGN KEY(work_order_id) REFERENCES work_orders(id) ON DELETE CASCADE, FOREIGN KEY(employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS attendance (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, work_date DATE NOT NULL, check_in DATETIME, check_out DATETIME, minutes_worked INT DEFAULT 0, note VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(employee_id,work_date), FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS employee_leaves (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, leave_type VARCHAR(30), start_date DATE, end_date DATE, status VARCHAR(30) DEFAULT 'pending', notes TEXT, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS employee_overtime (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, work_date DATE, minutes INT DEFAULT 0, amount DECIMAL(14,2) DEFAULT 0, approved_by BIGINT UNSIGNED NULL, FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customer_accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT UNSIGNED NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customer_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, account_id BIGINT UNSIGNED NOT NULL, job_id BIGINT UNSIGNED NULL, txn_date DATE NOT NULL, description VARCHAR(255), debit DECIMAL(14,2) DEFAULT 0, credit DECIMAL(14,2) DEFAULT 0, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(account_id) REFERENCES customer_accounts(id) ON DELETE CASCADE, FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS suppliers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_name VARCHAR(190) NOT NULL, contact_name VARCHAR(150), phone VARCHAR(30), address TEXT, tax_info VARCHAR(255), iban VARCHAR(50), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS supplier_accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, supplier_id BIGINT UNSIGNED NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS supplier_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, account_id BIGINT UNSIGNED NOT NULL, txn_date DATE NOT NULL, description VARCHAR(255), debit DECIMAL(14,2) DEFAULT 0, credit DECIMAL(14,2) DEFAULT 0, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(account_id) REFERENCES supplier_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS supplier_products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, supplier_id BIGINT UNSIGNED NOT NULL, material_id BIGINT UNSIGNED NOT NULL, last_price DECIMAL(14,2), last_price_at DATETIME, UNIQUE(supplier_id,material_id), FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE, FOREIGN KEY(material_id) REFERENCES materials(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS supplier_price_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, supplier_id BIGINT UNSIGNED NOT NULL, material_id BIGINT UNSIGNED NOT NULL, price DECIMAL(14,2) NOT NULL, recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP, purchase_id BIGINT UNSIGNED NULL, INDEX(material_id,recorded_at), FOREIGN KEY(supplier_id) REFERENCES suppliers(id), FOREIGN KEY(material_id) REFERENCES materials(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS purchases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, purchase_no VARCHAR(40) NOT NULL UNIQUE, supplier_id BIGINT UNSIGNED NOT NULL, purchase_date DATE NOT NULL, total DECIMAL(14,2) DEFAULT 0, paid DECIMAL(14,2) DEFAULT 0, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS purchase_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, purchase_id BIGINT UNSIGNED NOT NULL, material_id BIGINT UNSIGNED NOT NULL, quantity DECIMAL(14,3), unit_price DECIMAL(14,2), total DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED, FOREIGN KEY(purchase_id) REFERENCES purchases(id) ON DELETE CASCADE, FOREIGN KEY(material_id) REFERENCES materials(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS collections (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT UNSIGNED NOT NULL, job_id BIGINT UNSIGNED NULL, collection_date DATE NOT NULL, amount DECIMAL(14,2) NOT NULL, payment_method VARCHAR(30), cash_account VARCHAR(100), description VARCHAR(255), created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(customer_id) REFERENCES customers(id), FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, supplier_id BIGINT UNSIGNED NULL, employee_id BIGINT UNSIGNED NULL, job_id BIGINT UNSIGNED NULL, payment_date DATE NOT NULL, amount DECIMAL(14,2) NOT NULL, payment_method VARCHAR(30), cash_account VARCHAR(100), description VARCHAR(255), created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS files (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_id BIGINT UNSIGNED NULL, customer_id BIGINT UNSIGNED NULL, category VARCHAR(50), original_name VARCHAR(255), stored_name VARCHAR(255), mime_type VARCHAR(100), file_size BIGINT UNSIGNED, description TEXT, uploaded_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(job_id) REFERENCES jobs(id) ON DELETE CASCADE, FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, type VARCHAR(50), title VARCHAR(190), message TEXT, link VARCHAR(255), read_at DATETIME, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS activity_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(60), entity_type VARCHAR(80), entity_id BIGINT UNSIGNED NULL, old_values JSON NULL, new_values JSON NULL, ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(entity_type,entity_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO roles(name,label,permissions) VALUES
('admin','Yönetici',JSON_ARRAY('*')),('accounting','Muhasebe',JSON_ARRAY('finance','suppliers','reports')),('sales','Satış',JSON_ARRAY('customers','jobs','quotes')),('measurement','Ölçü Personeli',JSON_ARRAY('assigned_jobs','measurements','files')),('production','Üretim Personeli',JSON_ARRAY('assigned_jobs','production','files')),('installation','Montaj Personeli',JSON_ARRAY('assigned_jobs','installation','files')),('employee','Personel',JSON_ARRAY('assigned_jobs','attendance'));

INSERT IGNORE INTO material_categories(name) VALUES ('Baskı Malzemeleri'),('Levha ve Plaka'),('Metal ve Profil'),('Aydınlatma'),('Hizmet');
INSERT IGNORE INTO materials(category_id,name,unit,purchase_price,sale_price,default_margin,price_updated_at)
SELECT c.id,x.name,x.unit,x.buy,x.sell,x.margin,NOW() FROM material_categories c JOIN (
 SELECT 'Baskı Malzemeleri' cat,'Vinil' name,'m²' unit,120 buy,250 sell,45 margin UNION ALL
 SELECT 'Baskı Malzemeleri','Folyo','m²',140,300,50 UNION ALL SELECT 'Levha ve Plaka','Kompozit','m²',520,850,38.82 UNION ALL
 SELECT 'Levha ve Plaka','Pleksi','m²',700,1100,36.36 UNION ALL SELECT 'Metal ve Profil','Profil','metre',110,180,38.89 UNION ALL
 SELECT 'Aydınlatma','Modül LED','adet',22,35,37.14 UNION ALL SELECT 'Aydınlatma','Trafo','adet',400,600,33.33 UNION ALL
 SELECT 'Hizmet','Dijital Baskı','m²',120,250,52 UNION ALL SELECT 'Hizmet','Montaj','hizmet',0,2500,0 UNION ALL SELECT 'Hizmet','Nakliye','hizmet',0,1000,0
) x ON x.cat=c.name;

SET FOREIGN_KEY_CHECKS=1;