log_id INT(11) unsigned not_null comment(Primary key - unique log entry identifier)
product_id INT(11) unsigned not_null comment(Foreign key - references clic_products.products_id)
language_id INT(11) unsigned not_null comment(Language context for the action)
action_type ENUM('featured','favorites','specials') not_null comment(Type of marketing action applied to the product)
revocation_token VARCHAR(64) default null comment(Token used to cancel/revoke the action if needed)
action_code VARCHAR(50) default null comment(Unique code identifying the specific action instance)
action_subtype ENUM('insert','delete','update') not_null comment(Operation performed: insert=new action, delete=removed, update=modified)
special_price DECIMAL(15,4) default null comment(Promotional price applied to the product (used when action_type=specials))
margin_rate_applied DECIMAL(5,2) default null comment(Margin rate (%) used to calculate the special price)
promotion_step TINYINT(3) unsigned default null comment(Step number in a multi-stage promotion sequence)
status ENUM('executed','skipped','pending_admin','no_action','failed') default 'no_action' comment(Execution status: executed=done, skipped=conditions not met, pending_admin=awaiting approval, no_action=nothing done, failed=error)
triggered_by VARCHAR(50) default 'auto' comment(Who triggered the action: auto=system, or user identifier)
user_id INT(11) unsigned default null comment(Foreign key - admin user who manually triggered the action (NULL if auto))
score_x_at_trigger DECIMAL(5,2) default null comment(X-axis score (e.g. sales performance) at the moment the action was triggered)
score_y_at_trigger DECIMAL(5,2) default null comment(Y-axis score (e.g. margin/visibility) at the moment the action was triggered)
quadrant_at_trigger VARCHAR(20) default null comment(Matrix quadrant position of the product when action was triggered (e.g. Q1, Q2, star, dog))
validation_reason VARCHAR(500) default null comment(Explanation of why the action was validated or skipped)
input_scores LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default null comment(JSON snapshot of all X/Y scores at decision time' CHECK (JSON_VALID(input_scores))
cancel_token VARCHAR(64) default null comment(Token required to cancel a pending or executed action)
cancel_token_expires DATETIME default null comment(Expiry date/time of the cancel_token - after this date cancellation is no longer possible)
date_created DATETIME not_null comment(Timestamp when the log entry was created)
date_cancelled DATETIME default null comment(Timestamp when the action was cancelled (NULL if not cancelled))
score_y_after DECIMAL(5,2) default null comment(Y-axis score measured N days after the action - used to evaluate action effectiveness)
feedback_collected_at DATETIME default null comment(Timestamp when post-action feedback/measurement was collected)
conversion_velocity DECIMAL(8,4) default null comment(Speed of conversion change after the action (sales rate delta per day))
trigger_strategy VARCHAR(50) default 'standard' comment(Strategy used to trigger the action: standard=default rules, or custom strategy name

--
primary log_id
idx_cockpit_ia_action_products product_id
idx_revocation_token revocation_token
idx_action_type_status action_type status

##
engine innodb
character_set utf8mb4
collate utf8mb4_unicode_ci