-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- Sales module v3: Factory field (4 factories buy milk)
-- Run this AFTER schema_sales_v2.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS factories;

CREATE TABLE factories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    location VARCHAR(150) 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 factory_id INT NULL AFTER employee_id;
ALTER TABLE sales ADD CONSTRAINT fk_sales_factory FOREIGN KEY (factory_id) REFERENCES factories(id) ON DELETE SET NULL;
ALTER TABLE sales ADD COLUMN factory_name VARCHAR(150) NULL AFTER factory_id;