-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- Sales module v2:
--  - Employee registry (name + employee ID, reusable dropdown)
--  - Inventory item unit price (auto-fills sale price, e.g. Milk)
--  - Delivery status lock (non-admins can't edit after "Delivered")
-- Run this AFTER schema_sales.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(50) NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    contact VARCHAR(50) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

ALTER TABLE sales ADD COLUMN employee_id INT NULL AFTER buyer_contact;
ALTER TABLE sales ADD CONSTRAINT fk_sales_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL;

-- Selling price per unit for an inventory item (e.g. Milk = 70/liter) — auto-fills the Sales form
ALTER TABLE inventory_items ADD COLUMN unit_price DECIMAL(10,2) NULL AFTER quantity;