-- ===================================================================== -- NexusJetSmsWp | Aşama 1: Veritabanı şeması (MySQL 8 / utf8mb4) -- Kurallar: -- * Kayıt ekranı yok: tüm hesaplar admin (veya bayi) tarafından açılır. -- * Her gönderim talebi 'pending' olarak başlar, admin onayı olmadan gitmez. -- * Denetim kaydı hash zinciriyle tutulur (sonradan değiştirilirse bozulur). -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- Sektörler (beyan listesi). is_blocked = 1 olanlar seçilemez ve -- moderasyon taramasında otomatik reddedilir. -- --------------------------------------------------------------------- CREATE TABLE sectors ( code VARCHAR(40) PRIMARY KEY, label VARCHAR(120) NOT NULL, is_blocked TINYINT(1) NOT NULL DEFAULT 0 ) ENGINE=InnoDB; INSERT INTO sectors (code, label, is_blocked) VALUES ('cafe_restaurant', 'Kafe / Restoran', 0), ('retail', 'Perakende / Mağaza', 0), ('ecommerce', 'E-ticaret', 0), ('education', 'Eğitim / Kurs', 0), ('health', 'Sağlık / Klinik / Güzellik', 0), ('automotive', 'Otomotiv', 0), ('real_estate', 'Emlak / İnşaat', 0), ('tourism', 'Turizm / Otel', 0), ('events', 'Etkinlik / Organizasyon', 0), ('services', 'Hizmet / Danışmanlık', 0), ('other', 'Diğer (not zorunlu)', 0), ('gambling', 'Bahis / Casino / Şans oyunları', 1), ('adult', 'Yetişkin içerik', 1), ('crypto_scam', 'Kaldıraç / Riskli yatırım vaatleri', 1); -- --------------------------------------------------------------------- -- Firmalar (müşteri işletmeleri). reseller_id doluysa bayiye bağlıdır. -- --------------------------------------------------------------------- CREATE TABLE companies ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reseller_id BIGINT UNSIGNED NULL, -- users.id (role=reseller) name VARCHAR(160) NOT NULL, sector_code VARCHAR(40) NOT NULL, sector_note VARCHAR(500) NULL, -- 'other' için zorunlu not admin_note TEXT NULL, -- sadece admin görür status ENUM('active','suspended') NOT NULL DEFAULT 'active', sms_credit BIGINT NOT NULL DEFAULT 0, wa_credit BIGINT NOT NULL DEFAULT 0, auto_approve TINYINT(1) NOT NULL DEFAULT 0, -- KULLANILMAZ: her talep admin onayı ister daily_limit INT UNSIGNED NOT NULL DEFAULT 5000, -- günlük azami alıcı sayısı created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_reseller (reseller_id), CONSTRAINT fk_company_sector FOREIGN KEY (sector_code) REFERENCES sectors(code) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Kullanıcılar: admin, reseller (bayi), customer (müşteri) -- --------------------------------------------------------------------- CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role ENUM('admin','reseller','customer') NOT NULL, company_id BIGINT UNSIGNED NULL, -- customer için zorunlu parent_id BIGINT UNSIGNED NULL, -- hesabı açan kullanıcı username VARCHAR(60) NOT NULL UNIQUE, email VARCHAR(190) NULL, password_hash VARCHAR(255) NOT NULL, -- Argon2id must_change_password TINYINT(1) NOT NULL DEFAULT 1, totp_secret VARCHAR(255) NULL, -- şifreli saklanır totp_enabled TINYINT(1) NOT NULL DEFAULT 0, status ENUM('active','suspended') NOT NULL DEFAULT 'active', failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME NULL, last_login_at DATETIME NULL, last_login_ip VARBINARY(16) NULL, sms_pool BIGINT NOT NULL DEFAULT 0, -- bayi kredi havuzu wa_pool BIGINT NOT NULL DEFAULT 0, commission_pct DECIMAL(5,2) NOT NULL DEFAULT 0, -- bayi komisyon oranı (%) created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_company (company_id), KEY idx_parent (parent_id), CONSTRAINT fk_user_company FOREIGN KEY (company_id) REFERENCES companies(id) ) ENGINE=InnoDB; ALTER TABLE companies ADD CONSTRAINT fk_company_reseller FOREIGN KEY (reseller_id) REFERENCES users(id), ADD CONSTRAINT fk_company_creator FOREIGN KEY (created_by) REFERENCES users(id); -- Giriş oturumları ve cihazlar (güvenlik izleme) CREATE TABLE login_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, username VARCHAR(60) NOT NULL, success TINYINT(1) NOT NULL, ip VARBINARY(16) NOT NULL, user_agent VARCHAR(255) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_time (user_id, created_at) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Kredi hareketleri (değiştirilemez defter mantığı) -- --------------------------------------------------------------------- CREATE TABLE credit_ledger ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, channel ENUM('sms','whatsapp') NOT NULL, amount BIGINT NOT NULL, -- + yükleme, - harcama balance_after BIGINT NOT NULL, reason VARCHAR(190) NOT NULL, kind ENUM('admin','reseller_transfer','spend','refund') NOT NULL DEFAULT 'admin', request_id BIGINT UNSIGNED NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_company_time (company_id, created_at), CONSTRAINT fk_ledger_company FOREIGN KEY (company_id) REFERENCES companies(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Mesaj şablonları (WhatsApp için Meta onayı gerekir) -- --------------------------------------------------------------------- CREATE TABLE message_templates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, channel ENUM('sms','whatsapp') NOT NULL, name VARCHAR(120) NOT NULL, body TEXT NOT NULL, wa_template_name VARCHAR(120) NULL, wa_status ENUM('none','submitted','approved','rejected') NOT NULL DEFAULT 'none', admin_approved TINYINT(1) NOT NULL DEFAULT 0, -- otomatik onay için created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_company (company_id), CONSTRAINT fk_tpl_company FOREIGN KEY (company_id) REFERENCES companies(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Gönderim talepleri: panel veya API'den gelir, admin onay kuyruğuna düşer -- --------------------------------------------------------------------- CREATE TABLE send_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, requested_by BIGINT UNSIGNED NULL, -- API ile gelirse NULL api_key_id BIGINT UNSIGNED NULL, channel ENUM('sms','whatsapp') NOT NULL, title VARCHAR(160) NOT NULL, message_body TEXT NOT NULL, template_id BIGINT UNSIGNED NULL, media_path VARCHAR(255) NULL, recipient_count INT UNSIGNED NOT NULL DEFAULT 0, estimated_credit INT UNSIGNED NOT NULL DEFAULT 0, consent_declared TINYINT(1) NOT NULL DEFAULT 0, -- alıcı izin beyanı consent_declared_at DATETIME NULL, moderation_flags JSON NULL, -- yasaklı kelime/kategori eşleşmeleri status ENUM('draft','pending','approved','rejected','queued', 'sending','completed','failed','cancelled') NOT NULL DEFAULT 'pending', reviewed_by BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, reject_reason VARCHAR(500) NULL, source ENUM('panel','api') NOT NULL DEFAULT 'panel', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_status_time (status, created_at), KEY idx_company (company_id), CONSTRAINT fk_req_company FOREIGN KEY (company_id) REFERENCES companies(id) ) ENGINE=InnoDB; -- Talebe eklenen numaralar (admin onay ekranında görüntülenir) CREATE TABLE request_recipients ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, request_id BIGINT UNSIGNED NOT NULL, phone_e164 VARCHAR(20) NOT NULL, -- +905xxxxxxxxx status ENUM('pending','sent','delivered','failed','skipped_optout') NOT NULL DEFAULT 'pending', provider_message_id VARCHAR(120) NULL, error_code VARCHAR(60) NULL, sent_at DATETIME NULL, delivered_at DATETIME NULL, UNIQUE KEY uq_req_phone (request_id, phone_e164), KEY idx_status (request_id, status), KEY idx_provider (provider_message_id), CONSTRAINT fk_rcpt_req FOREIGN KEY (request_id) REFERENCES send_requests(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Çıkış (opt-out) / kara liste: company_id NULL ise tüm sistemde geçerli CREATE TABLE optouts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NULL, phone_e164 VARCHAR(20) NOT NULL, source ENUM('reply','admin','api','import') NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_company_phone (company_id, phone_e164) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Moderasyon: yasaklı kelime/ifadeler -- --------------------------------------------------------------------- CREATE TABLE banned_terms ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, term VARCHAR(120) NOT NULL, category VARCHAR(40) NOT NULL, severity ENUM('flag','block') NOT NULL DEFAULT 'flag', UNIQUE KEY uq_term (term) ) ENGINE=InnoDB; INSERT INTO banned_terms (term, category, severity) VALUES ('casino', 'gambling', 'block'), ('bahis', 'gambling', 'block'), ('bet', 'gambling', 'flag'), ('slot', 'gambling', 'flag'), ('freespin', 'gambling', 'block'), ('bonus', 'gambling', 'flag'), ('kumar', 'gambling', 'block'), ('rulet', 'gambling', 'block'), ('canlı bahis', 'gambling', 'block'); -- --------------------------------------------------------------------- -- API anahtarları (anahtarın kendisi saklanmaz, yalnızca hash'i) -- --------------------------------------------------------------------- CREATE TABLE api_keys ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, key_prefix CHAR(8) NOT NULL, -- listede göstermek için key_hash CHAR(64) NOT NULL UNIQUE, -- SHA-256 scopes JSON NOT NULL, allowed_ips JSON NULL, rate_limit_per_min INT UNSIGNED NOT NULL DEFAULT 60, daily_quota INT UNSIGNED NOT NULL DEFAULT 1000, last_used_at DATETIME NULL, revoked_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_company (company_id), CONSTRAINT fk_key_company FOREIGN KEY (company_id) REFERENCES companies(id) ) ENGINE=InnoDB; CREATE TABLE webhook_endpoints ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, url VARCHAR(500) NOT NULL, secret_enc VARCHAR(255) NOT NULL, -- imza anahtarı (şifreli) events JSON NOT NULL, -- ['delivery','request.approved',...] active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_wh_company FOREIGN KEY (company_id) REFERENCES companies(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Denetim kaydı: hash zinciri. Uygulama DB kullanıcısına sadece INSERT/SELECT -- yetkisi verilir (UPDATE/DELETE yok). -- hash = SHA256(prev_hash || actor || action || entity || meta || created_at) -- --------------------------------------------------------------------- CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_user_id BIGINT UNSIGNED NULL, actor_ip VARBINARY(16) NULL, action VARCHAR(80) NOT NULL, -- 'request.approve', 'user.create', ... entity_type VARCHAR(60) NULL, entity_id BIGINT UNSIGNED NULL, meta JSON NULL, prev_hash CHAR(64) NOT NULL, hash CHAR(64) NOT NULL, created_at DATETIME(3) NOT NULL, KEY idx_actor (actor_user_id, created_at), KEY idx_entity (entity_type, entity_id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Sistem ayarları (ör. sending_paused = 1 → tüm gönderimler duraklar) -- --------------------------------------------------------------------- CREATE TABLE settings ( `key` VARCHAR(60) PRIMARY KEY, `value` VARCHAR(255) NOT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO settings (`key`, `value`) VALUES ('sending_paused', '0'); SET FOREIGN_KEY_CHECKS = 1;