CREATE DATABASE IF NOT EXISTS tw_bridge_test_sql;
USE tw_bridge_test_sql;
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;

CREATE TABLE assertions (name VARCHAR(190), passed INT CHECK (passed=1));
INSERT INTO sources (name,sku_prefix,channel_id) VALUES ('SQL test','SQLTEST','-1009');
INSERT INTO products (source_id,external_product_id,sku,raw_text,parsed_json,final_price,status) VALUES (1,'1','SQLTEST-1','متن','{}',NULL,'draft');
INSERT INTO assertions SELECT 'nullable_price_draft', final_price IS NULL AND status='draft' FROM products WHERE id=1;
INSERT IGNORE INTO products (source_id,external_product_id,sku,raw_text,parsed_json) VALUES (1,'1','OTHER-1','text','{}');
INSERT INTO assertions SELECT 'composite_unique', COUNT(*)=1 FROM products WHERE source_id=1 AND external_product_id='1';
INSERT INTO queue_jobs (job_key,type,payload,available_at) VALUES ('collect:test','collect','{}',UTC_TIMESTAMP());
UPDATE queue_jobs SET status='running' WHERE id=1;
INSERT INTO queue_jobs (job_key,type,payload,available_at) VALUES ('collect:test','collect','{"next":1}',UTC_TIMESTAMP()) ON DUPLICATE KEY UPDATE payload=VALUES(payload),generation=generation+1,status=IF(status='running','running','pending');
UPDATE queue_jobs SET status=IF(generation=1,'done','pending') WHERE id=1;
INSERT INTO assertions SELECT 'generation_survives_completion', generation=2 AND status='pending' FROM queue_jobs WHERE id=1;
INSERT INTO nonces (nonce_hash,expires_at) VALUES (REPEAT('a',64),DATE_ADD(UTC_TIMESTAMP(),INTERVAL 10 MINUTE));
INSERT IGNORE INTO nonces (nonce_hash,expires_at) VALUES (REPEAT('a',64),UTC_TIMESTAMP());
INSERT INTO assertions SELECT 'nonce_unique_atomic', COUNT(*)=1 FROM nonces;
INSERT INTO assertions SELECT 'queue_advisory_lock', GET_LOCK('tw_bridge_test_lock',0)=1;
INSERT INTO assertions SELECT 'queue_advisory_release', RELEASE_LOCK('tw_bridge_test_lock')=1;
CREATE TABLE wp_tw_bridge_nonces (nonce_hash CHAR(64) PRIMARY KEY,expires_at DATETIME NOT NULL);
INSERT IGNORE INTO wp_tw_bridge_nonces VALUES (REPEAT('b',64),UTC_TIMESTAMP());
INSERT INTO assertions VALUES ('first_nonce_insert',ROW_COUNT()=1);
INSERT IGNORE INTO wp_tw_bridge_nonces VALUES (REPEAT('b',64),UTC_TIMESTAMP());
INSERT INTO assertions VALUES ('second_nonce_replay',ROW_COUNT()=0);
INSERT INTO assertions SELECT 'all_13_tables',COUNT(*)=13 FROM information_schema.tables WHERE table_schema='tw_bridge_test_sql' AND table_name IN ('admins','settings','telegram_updates','telegram_media','sources','pricing_rules','products','product_attributes','product_media','imports','queue_jobs','logs','nonces');
DROP DATABASE tw_bridge_test_sql;