-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- Sales / Supply module
-- Flow: Milk collected -> transported to factory -> supplied to employees/buyers
--       -> payment collected -> tracked as income for profit reporting
-- Run this AFTER schema_phase6.sql + schema_inventory_sync.sql + schema_rbac.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE NOT NULL,
    item_id INT NOT NULL,
    buyer_name VARCHAR(150) NOT NULL,
    buyer_contact VARCHAR(50) NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_status ENUM('Unpaid','Partial','Paid') NOT NULL DEFAULT 'Unpaid',
    delivery_status ENUM('Pending','Delivered') NOT NULL DEFAULT 'Delivered',
    remarks VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

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

CREATE INDEX idx_sales_date ON sales(sale_date);
CREATE INDEX idx_sales_item ON sales(item_id);
CREATE INDEX idx_sales_payment ON sales(payment_status);

-- ---------------------------
-- RBAC: sales.view / add / edit / delete
-- ---------------------------
INSERT IGNORE INTO permissions (name, description) VALUES ('sales.view', 'View sales');
INSERT IGNORE INTO permissions (name, description) VALUES ('sales.add', 'Add sales');
INSERT IGNORE INTO permissions (name, description) VALUES ('sales.edit', 'Edit sales');
INSERT IGNORE INTO permissions (name, description) VALUES ('sales.delete', 'Delete sales');

-- Grant to Admin (all)
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE name LIKE 'sales.%';

-- Manager: everything except delete
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE name LIKE 'sales.%' AND name != 'sales.delete';

-- Staff: view + add only
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE name IN ('sales.view','sales.add');