CREATE TABLE IF NOT EXISTS admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL,
 role ENUM('owner','operator') NOT NULL DEFAULT 'operator', active TINYINT NOT NULL DEFAULT 1, failed_attempts INT NOT NULL DEFAULT 0, locked_until DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(100) PRIMARY KEY, value LONGTEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS pricing_rules (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, config LONGTEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sources (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,sku_prefix VARCHAR(60) NOT NULL UNIQUE, telegram_id VARCHAR(60) NULL UNIQUE,channel_id VARCHAR(60) NULL UNIQUE,username VARCHAR(100) NULL UNIQUE,
 category VARCHAR(190) NOT NULL DEFAULT '',price_base ENUM('retail','wholesale') NOT NULL DEFAULT 'retail', pricing_rule_id BIGINT UNSIGNED NULL,
 round_rule ENUM('none','nearest','up','down') NOT NULL DEFAULT 'none',round_step BIGINT NOT NULL DEFAULT 1,
 duplicate_policy ENUM('update','skip','review') NOT NULL DEFAULT 'update',variable_strategy ENUM('auto','simple','size','color','both') NOT NULL DEFAULT 'auto',auto_import TINYINT NOT NULL DEFAULT 0,active TINYINT NOT NULL DEFAULT 1,
 FOREIGN KEY(pricing_rule_id) REFERENCES pricing_rules(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS telegram_updates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,update_id BIGINT NOT NULL UNIQUE,chat_id VARCHAR(60) NOT NULL,message_id BIGINT NOT NULL,source_id BIGINT UNSIGNED NULL,group_key VARCHAR(190) NOT NULL,raw_text LONGTEXT NOT NULL,corrected_text LONGTEXT NULL,payload LONGTEXT NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'received',received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY group_idx(group_key),FOREIGN KEY(source_id) REFERENCES sources(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS telegram_media (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,update_id BIGINT UNSIGNED NOT NULL,file_id VARCHAR(255) NOT NULL,file_unique_id VARCHAR(190) NOT NULL,local_path VARCHAR(255) NULL,mime VARCHAR(100) NULL,sha256 CHAR(64) NULL,position INT NOT NULL DEFAULT 0,
 UNIQUE KEY unique_media(update_id,file_unique_id),FOREIGN KEY(update_id) REFERENCES telegram_updates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,source_id BIGINT UNSIGNED NOT NULL,external_product_id VARCHAR(100) NOT NULL,sku VARCHAR(190) NOT NULL UNIQUE,
 raw_text LONGTEXT NOT NULL,parsed_json LONGTEXT NOT NULL,final_price BIGINT NULL,status ENUM('draft','pending','ready','imported','failed') NOT NULL DEFAULT 'draft',wp_product_id BIGINT NULL,review_reason VARCHAR(190) NULL,revision INT NOT NULL DEFAULT 1,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY source_external(source_id,external_product_id),FOREIGN KEY(source_id) REFERENCES sources(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_attributes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,name VARCHAR(100) NOT NULL,value LONGTEXT NOT NULL,is_variation TINYINT NOT NULL DEFAULT 0,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_media (product_id BIGINT UNSIGNED NOT NULL,media_id BIGINT UNSIGNED NOT NULL,position INT NOT NULL,PRIMARY KEY(product_id,media_id),FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(media_id) REFERENCES telegram_media(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS imports (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,revision INT NOT NULL,payload_hash CHAR(64) NOT NULL,status VARCHAR(30) NOT NULL,response LONGTEXT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS queue_jobs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_key VARCHAR(190) NOT NULL UNIQUE,type ENUM('collect','push') NOT NULL,payload LONGTEXT NOT NULL,status ENUM('pending','running','done','failed') NOT NULL DEFAULT 'pending',generation INT NOT NULL DEFAULT 1,attempts INT NOT NULL DEFAULT 0,available_at DATETIME NOT NULL,locked_at DATETIME NULL,last_error TEXT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,KEY due(status,available_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,level VARCHAR(20) NOT NULL,event VARCHAR(100) NOT NULL,context LONGTEXT NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,KEY age(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS nonces (nonce_hash CHAR(64) PRIMARY KEY,expires_at DATETIME NOT NULL,KEY expiry(expires_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;