-- ============================================
-- IHARS DERNEK YÖNETİM SİSTEMİ - VERİTABANI ŞEMASI
-- Multi-Tenant SaaS Yapısı
-- ============================================


-- ============================================
-- 1. TENANT (DERNEK) YÖNETİMİ
-- ============================================

CREATE TABLE IF NOT EXISTS `tenants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'Dernek adı',
  `unvan` varchar(255) DEFAULT NULL COMMENT 'Dernek ünvanı',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Logo dosya yolu',
  `sms_header` varchar(11) DEFAULT NULL COMMENT 'SMS başlığı',
  `package` enum('trial','basic','premium','enterprise') DEFAULT 'trial' COMMENT 'Paket türü',
  `member_limit` int(11) DEFAULT 100 COMMENT 'Üye limiti',
  `sms_limit` int(11) DEFAULT 1000 COMMENT 'SMS limiti',
  `user_limit` int(11) DEFAULT 5 COMMENT 'Kullanıcı sayısı limiti',
  `trial_ends_at` datetime DEFAULT NULL COMMENT 'Deneme süresi bitiş',
  `status` enum('active','inactive','suspended') DEFAULT 'active' COMMENT 'Durum',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. YETKİ YÖNETİMİ
-- ============================================

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `module` varchar(50) NOT NULL COMMENT 'Modül adı (member, aidat, sms, vb.)',
  `action` varchar(50) NOT NULL COMMENT 'İşlem (read, create, update, delete)',
  `name` varchar(255) NOT NULL COMMENT 'Yetki adı',
  `description` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `module_action` (`module`,`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role` enum('super_admin','tenant_admin','manager','staff','viewer') NOT NULL,
  `permission_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL COMMENT 'Tenant bazlı özel yetki',
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permission_tenant` (`role`,`permission_id`,`tenant_id`),
  KEY `fk_permission` (`permission_id`),
  CONSTRAINT `fk_role_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. ÜYE YÖNETİMİ
-- ============================================

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `tc_no` varchar(11) DEFAULT NULL COMMENT 'TC Kimlik No',
  `name` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `membership_type` enum('regular','honorary','founder','other') DEFAULT 'regular' COMMENT 'Üyelik türü',
  `membership_date` date DEFAULT NULL COMMENT 'Üyelik tarihi',
  `status` enum('active','passive','suspended','candidate') DEFAULT 'candidate' COMMENT 'Durum (candidate = aday)',
  `custom_fields` json DEFAULT NULL COMMENT 'Özel alanlar (JSON)',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_status` (`status`),
  KEY `idx_tc_no` (`tc_no`),
  KEY `idx_phone` (`phone`),
  CONSTRAINT `fk_members_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `application_forms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL COMMENT 'Form adı',
  `fields` json NOT NULL COMMENT 'Form alanları (JSON)',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  CONSTRAINT `fk_forms_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `link_id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `member_id` int(11) DEFAULT NULL COMMENT 'Onaylandıktan sonra üye ID',
  `form_data` json NOT NULL COMMENT 'Form verileri',
  `signature` text NOT NULL COMMENT 'İmza (base64)',
  `signature_image` varchar(255) DEFAULT NULL COMMENT 'İmza görseli',
  `pdf_path` varchar(255) DEFAULT NULL COMMENT 'Oluşturulan PDF yolu',
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `kvkk_accepted` tinyint(1) DEFAULT 0 COMMENT 'KVKK onayı',
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL COMMENT 'Onaylayan kullanıcı',
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_link` (`link_id`),
  KEY `idx_member` (`member_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_applications_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_applications_link` FOREIGN KEY (`link_id`) REFERENCES `application_links` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_applications_form` FOREIGN KEY (`form_id`) REFERENCES `application_forms` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_applications_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. AİDAT & BORÇ TAKİBİ
-- ============================================

CREATE TABLE IF NOT EXISTS `fee_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL COMMENT 'Aidat adı',
  `amount` decimal(10,2) NOT NULL COMMENT 'Tutar',
  `period` enum('monthly','yearly') DEFAULT 'monthly' COMMENT 'Dönem',
  `membership_type` varchar(50) DEFAULT NULL COMMENT 'Üyelik türü (NULL = tümü)',
  `start_date` date DEFAULT NULL COMMENT 'Başlangıç tarihi',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  CONSTRAINT `fk_fee_types_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `member_fees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `fee_type_id` int(11) NOT NULL,
  `period` varchar(20) NOT NULL COMMENT 'Dönem (2024-01, 2024, vb.)',
  `amount` decimal(10,2) NOT NULL COMMENT 'Tutar',
  `paid_amount` decimal(10,2) DEFAULT 0.00 COMMENT 'Ödenen tutar',
  `status` enum('pending','paid','overdue','partial') DEFAULT 'pending',
  `due_date` date DEFAULT NULL COMMENT 'Vade tarihi',
  `paid_at` datetime DEFAULT NULL COMMENT 'Ödeme tarihi',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_member` (`member_id`),
  KEY `idx_status` (`status`),
  KEY `idx_period` (`period`),
  CONSTRAINT `fk_fees_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fees_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fees_type` FOREIGN KEY (`fee_type_id`) REFERENCES `fee_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `member_fee_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL COMMENT 'Ödeme yöntemi',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_fee` (`member_fee_id`),
  CONSTRAINT `fk_payments_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payments_fee` FOREIGN KEY (`member_fee_id`) REFERENCES `member_fees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. SMS MODÜLÜ (VATANSMS)
-- ============================================

CREATE TABLE IF NOT EXISTS `sms_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `type` enum('single','bulk','auto') DEFAULT 'single',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  CONSTRAINT `fk_sms_templates_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sms_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `template_id` int(11) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `message` text NOT NULL,
  `status` enum('pending','sent','failed') DEFAULT 'pending',
  `response` text DEFAULT NULL COMMENT 'API yanıtı',
  `sent_at` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_status` (`status`),
  KEY `idx_phone` (`phone`),
  CONSTRAINT `fk_sms_logs_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sms_logs_template` FOREIGN KEY (`template_id`) REFERENCES `sms_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sms_balance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `balance` int(11) DEFAULT 0 COMMENT 'Kontör bakiyesi',
  `last_check` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tenant_id` (`tenant_id`),
  CONSTRAINT `fk_sms_balance_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- AYARLAR TABLOSU
-- ============================================

CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_tenant` (`setting_key`, `tenant_id`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 8. ETKİNLİK & YOKLAMA
-- ============================================

CREATE TABLE IF NOT EXISTS `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `event_date` datetime NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `status` enum('upcoming','ongoing','completed','cancelled') DEFAULT 'upcoming',
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_date` (`event_date`),
  CONSTRAINT `fk_events_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `event_attendances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `status` enum('attended','absent','excused') DEFAULT 'absent',
  `notes` text DEFAULT NULL,
  `checked_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `event_member` (`event_id`,`member_id`),
  KEY `idx_member` (`member_id`),
  CONSTRAINT `fk_attendances_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_attendances_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 9. DOKÜMAN YÖNETİMİ
-- ============================================

CREATE TABLE IF NOT EXISTS `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `member_id` int(11) DEFAULT NULL COMMENT 'NULL ise genel doküman',
  `name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` varchar(50) DEFAULT NULL,
  `file_size` int(11) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL COMMENT 'Kategori',
  `description` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_member` (`member_id`),
  CONSTRAINT `fk_documents_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_documents_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 10. LOG & GÜVENLİK
-- ============================================

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL COMMENT 'İşlem',
  `module` varchar(50) DEFAULT NULL COMMENT 'Modül',
  `record_id` int(11) DEFAULT NULL COMMENT 'İlgili kayıt ID',
  `old_data` json DEFAULT NULL COMMENT 'Eski veri',
  `new_data` json DEFAULT NULL COMMENT 'Yeni veri',
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_module` (`module`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- VARSayılan VERİLER
-- ============================================

-- Varsayılan yetkiler
INSERT INTO `permissions` (`module`, `action`, `name`, `description`) VALUES
('member', 'read', 'Üye Görüntüleme', 'Üyeleri görüntüleme yetkisi'),
('member', 'create', 'Üye Ekleme', 'Yeni üye ekleme yetkisi'),
('member', 'update', 'Üye Güncelleme', 'Üye bilgilerini güncelleme yetkisi'),
('member', 'delete', 'Üye Silme', 'Üye silme yetkisi'),
('aidat', 'read', 'Aidat Görüntüleme', 'Aidat bilgilerini görüntüleme'),
('aidat', 'create', 'Aidat Oluşturma', 'Yeni aidat oluşturma'),
('aidat', 'update', 'Aidat Güncelleme', 'Aidat bilgilerini güncelleme'),
('aidat', 'delete', 'Aidat Silme', 'Aidat silme'),
('sms', 'read', 'SMS Görüntüleme', 'SMS loglarını görüntüleme'),
('sms', 'send', 'SMS Gönderme', 'SMS gönderme yetkisi'),
('event', 'read', 'Etkinlik Görüntüleme', 'Etkinlikleri görüntüleme'),
('event', 'create', 'Etkinlik Oluşturma', 'Yeni etkinlik oluşturma'),
('event', 'update', 'Etkinlik Güncelleme', 'Etkinlik bilgilerini güncelleme'),
('report', 'read', 'Rapor Görüntüleme', 'Raporları görüntüleme');

