-- Temel tablo seti (idempotency, kuyruk, webhook, medya, mesajlar)
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_conv_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS messages (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
conversation_id BIGINT NULL,
direction ENUM('in','out') NOT NULL,
type VARCHAR(32) NOT NULL,
payload JSON NOT NULL,
meta_id VARCHAR(64) NULL,
idempotency_key VARCHAR(64) NULL,
status VARCHAR(20) DEFAULT 'queued',
error_code VARCHAR(32) NULL,
error_title VARCHAR(255) NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uq_idem (idempotency_key),
KEY k_meta (meta_id),
CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS message_status (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
message_id BIGINT NOT NULL,
event ENUM('sent','delivered','read','failed') NOT NULL,
error_code VARCHAR(32) NULL,
error_msg VARCHAR(255) NULL,
ts DATETIME NOT NULL,
CONSTRAINT fk_status_msg FOREIGN KEY (message_id) REFERENCES messages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS media (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
meta_media_id VARCHAR(64) UNIQUE,
mime VARCHAR(64), size INT,
sha256 CHAR(64),
local_path VARCHAR(255) NULL,
origin ENUM('upload','download') DEFAULT 'download',
expires_at DATETIME NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS webhook_events (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
event_type VARCHAR(64),
phone_number_id VARCHAR(64) NULL,
meta_object_id VARCHAR(64) NULL,
raw LONGTEXT NOT NULL,
processed_flag TINYINT DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS wa_queue (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
request_type VARCHAR(32) NOT NULL, -- text|template|media|interactive...
phone_number_id VARCHAR(64) NOT NULL,
to_wa VARCHAR(32) NOT NULL,
body JSON NOT NULL,
idempotency_key VARCHAR(64) NOT NULL,
try_count TINYINT DEFAULT 0,
next_try_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status ENUM('pending','sent','failed') DEFAULT 'pending',
UNIQUE KEY uq_idem (idempotency_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS templates (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
lang VARCHAR(10) NOT NULL,
category VARCHAR(32) NULL,
status VARCHAR(32) NULL,
version INT DEFAULT 1,
raw JSON,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_tpl (name, lang, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;