29 lines
1.1 KiB
SQL
29 lines
1.1 KiB
SQL
CREATE TABLE IF NOT EXISTS transactions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
cliente VARCHAR(50) NOT NULL,
|
|
sco_number VARCHAR(50) NOT NULL,
|
|
sucursal VARCHAR(100) NOT NULL,
|
|
pos_transaction_id INT NOT NULL,
|
|
started_at DATETIME NOT NULL,
|
|
end_transaction_at DATETIME NULL,
|
|
closed_at DATETIME NOT NULL,
|
|
status ENUM('completed', 'canceled') NOT NULL,
|
|
item_count INT NOT NULL DEFAULT 0,
|
|
total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
payment_type VARCHAR(50) NULL,
|
|
payment_amount DECIMAL(10,2) NULL,
|
|
split_payment BOOLEAN NOT NULL DEFAULT FALSE,
|
|
age_verification_requested BOOLEAN NOT NULL DEFAULT FALSE,
|
|
age_verification_value INT NULL,
|
|
bag_item_count INT NOT NULL DEFAULT 0,
|
|
|
|
-- restrict lower case
|
|
CONSTRAINT chk_cliente_lower CHECK (cliente = LOWER(cliente)),
|
|
|
|
-- anti-duplicados para el UPSERT
|
|
UNIQUE KEY uq_cliente_sucursal_sco_pos (cliente, sucursal, sco_number, pos_transaction_id, (DATE(started_at))),
|
|
|
|
-- index para acelerar Grafana
|
|
KEY idx_cliente_sucursal_sco_started (cliente, sucursal, sco_number, started_at)
|
|
);
|