-- Place your SQL code here
-- vn.stockBought definition

CREATE TABLE IF NOT EXISTS vn.stockBought (
	id INT UNSIGNED auto_increment NOT NULL,
	workerFk int(10) unsigned NOT NULL,
	bought decimal(10,2) NOT NULL COMMENT 'purchase volume in m3 for the day',
	reserve decimal(10,2) NULL COMMENT 'reserved volume in m3 for the day',
	dated DATE NOT NULL DEFAULT current_timestamp(),
	CONSTRAINT stockBought_pk PRIMARY KEY (id),
	CONSTRAINT stockBought_unique UNIQUE KEY (workerFk,dated),
	CONSTRAINT stockBought_worker_FK FOREIGN KEY (workerFk) REFERENCES vn.worker(id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb3
COLLATE=utf8mb3_unicode_ci;


INSERT IGNORE vn.stockBought (workerFk, bought, reserve, dated)
    SELECT userFk, SUM(buyed), SUM(IFNULL(reserved,0)), dated
        FROM vn.stockBuyed
        WHERE userFk IS NOT NULL
            AND buyed IS NOT NULL
        GROUP BY userFk, dated;

INSERT INTO salix.ACL (model,property,accessType,permission,principalType,principalId)
	VALUES ('StockBought','*','READ','ALLOW','ROLE','buyer'),
		('StockBought','*','WRITE','ALLOW','ROLE','buyer'),
		('Buyer','*','READ','ALLOW','ROLE','buyer');