-- Distributors Migration
-- Run this file against the dms_db database

CREATE TABLE IF NOT EXISTS distributors (
    intId INT AUTO_INCREMENT PRIMARY KEY,
    strName VARCHAR(200) NOT NULL,
    strCode VARCHAR(50),
    txtAddress TEXT,
    strPhone VARCHAR(50),
    strEmail VARCHAR(150),
    dblCredit DECIMAL(18,2) DEFAULT 0,
    dblBalance DECIMAL(18,2) DEFAULT 0,
    intStatus TINYINT DEFAULT 1,
    intCreatedBy INT,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(intCreatedBy) REFERENCES users(intId)
);

ALTER TABLE users ADD COLUMN intDistributorId INT DEFAULT NULL AFTER intRoleId;
ALTER TABLE users ADD FOREIGN KEY (intDistributorId) REFERENCES distributors(intId);

ALTER TABLE warehouses ADD COLUMN intDistributorId INT DEFAULT NULL AFTER intCreatedBy;
ALTER TABLE warehouses ADD FOREIGN KEY (intDistributorId) REFERENCES distributors(intId);

ALTER TABLE customers ADD COLUMN intDistributorId INT DEFAULT NULL AFTER intCreatedBy;
ALTER TABLE customers ADD FOREIGN KEY (intDistributorId) REFERENCES distributors(intId);

ALTER TABLE purchase_orders ADD COLUMN intDistributorId INT DEFAULT NULL AFTER intSupplierId;
ALTER TABLE purchase_orders ADD FOREIGN KEY (intDistributorId) REFERENCES distributors(intId);

ALTER TABLE sales_orders ADD COLUMN intDistributorId INT DEFAULT NULL AFTER intApprovedBy;
ALTER TABLE sales_orders ADD FOREIGN KEY (intDistributorId) REFERENCES distributors(intId);

ALTER TABLE customer_returns ADD COLUMN intDistributorId INT DEFAULT NULL AFTER intCustomerId;
ALTER TABLE customer_returns ADD FOREIGN KEY (intDistributorId) REFERENCES distributors(intId);
ALTER TABLE customer_returns ADD COLUMN strRefundType ENUM('CREDIT','CASH') NOT NULL DEFAULT 'CREDIT' AFTER dblRefundAmount;

ALTER TABLE products ADD COLUMN dblDistributorPrice DECIMAL(18,2) DEFAULT 0 AFTER dblPrice;

-- Add distributor permissions
INSERT INTO permissions (strKey, strName, strGroup) VALUES
('distributors.view', 'View Distributors', 'Master Data'),
('distributors.create', 'Create Distributors', 'Master Data'),
('distributors.edit', 'Edit Distributors', 'Master Data'),
('distributors.delete', 'Delete Distributors', 'Master Data');

-- Assign distributor permissions to Admin role (id=1)
INSERT INTO role_permissions (intRoleId, intPermissionId)
SELECT 1, intId FROM permissions WHERE strKey LIKE 'distributors.%';