Coupon

Akademi Eğitim Platformu Coupon Dökümanı

-- MySQL database export
START TRANSACTION;

CREATE TABLE IF NOT EXISTS `coupons` (
    `couponId` INT,
    `academyId` INT NOT NULL,
    `code` VARCHAR(50) NOT NULL UNIQUE,
    -- Kampanya adı
    `name` VARCHAR(255) COMMENT 'Kampanya adı',
    `description` VARCHAR(500),
    -- trial, monthly, quarterly, yearly, unlimited
    `membershipType` enum_membership_type NOT NULL COMMENT 'trial, monthly, quarterly, yearly, unlimited',
    -- 0 = sınırsız
    `durationMonths` INT DEFAULT 0 COMMENT '0 = sınırsız',
    -- Toplam kullanım limiti (0 = sınırsız)
    `maxUses` INT DEFAULT 1 COMMENT 'Toplam kullanım limiti (0 = sınırsız)',
    -- Kişi başı kullanım limiti
    `usesPerUser` INT DEFAULT 1 COMMENT 'Kişi başı kullanım limiti',
    `currentUses` INT DEFAULT 0,
    `validFrom` INT,
    -- 0 = suresiz
    `validUntil` INT DEFAULT 0 COMMENT '0 = suresiz',
    `isActive` tinyint DEFAULT 1,
    -- {"domains": ["company.com", "tech.com"], "userTypes": ["new", "existing"],  "minAccountAge": 30, "firstPurchaseOnly": true }
    `restrictions` JSON DEFAULT '{}' COMMENT '{"domains": ["company.com", "tech.com"], "userTypes": ["new", "existing"], "minAccountAge": 30, "firstPurchaseOnly": true }',
    `campaignName` VARCHAR(255),
    -- acquisition, retention, reactivation
    `campaignGoal` VARCHAR(100) COMMENT 'acquisition, retention, reactivation',
    `utmSource` VARCHAR(100),
    `utmMedium` VARCHAR(100),
    `utmCampaign` VARCHAR(100),
    `conversionRate` DECIMAL(5, 2) DEFAULT 0,
    `totalRevenue` DECIMAL(18, 2) DEFAULT 0,
    `averageOrderValue` DECIMAL(18, 2) DEFAULT 0,
    `roi` DECIMAL(10, 2) DEFAULT 0,
    `createdAt` INT,
    `createdBy` INT DEFAULT 0,
    `updatedAt` INT,
    `updatedBy` INT DEFAULT 0
);

-- Indexes
CREATE INDEX `idx_coupons_academyId` ON `coupons` (`academyId`);
CREATE UNIQUE INDEX `idx_coupons_code_unique` ON `coupons` (`code`);
CREATE INDEX `idx_coupons_isActive_validFrom_validUntil` ON `coupons` (`isActive`, `validFrom`, `validUntil`);
CREATE INDEX `idx_coupons_campaignGoal` ON `coupons` (`campaignGoal`);


CREATE TABLE IF NOT EXISTS `campaign_performance` (
    `campaignId` INT NOT NULL AUTO_INCREMENT,
    `academyId` INT NOT NULL,
    `couponId` INT,
    `batchId` INT,
    `impressions` INT DEFAULT 0,
    `clicks` INT DEFAULT 0,
    `conversions` INT DEFAULT 0,
    `revenue` DECIMAL(18, 2) DEFAULT 0,
    `cost` DECIMAL(18, 2) DEFAULT 0,
    `roi` DECIMAL(10, 2) DEFAULT 0,
    `date` DATE NOT NULL,
    `hour` tinyint DEFAULT 0,
    `createdAt` INT DEFAULT 0,
    CONSTRAINT `pk_campaign_performance_campaignId` PRIMARY KEY (`campaignId`)
);

-- Indexes
CREATE INDEX `idx_campaign_performance_academyId_date` ON `campaign_performance` (`academyId`, `date`);
CREATE INDEX `idx_campaign_performance_couponId` ON `campaign_performance` (`couponId`);


CREATE TABLE IF NOT EXISTS `coupon_batches` (
    `batchId` INT NOT NULL AUTO_INCREMENT,
    `academyId` INT NOT NULL,
    -- BATCH-20240315-001
    `batchCode` VARCHAR(100) NOT NULL UNIQUE COMMENT 'BATCH-20240315-001',
    `name` VARCHAR(255),
    -- {"membershipType": "yearly","durationMonths": 12,"maxUses": 100,"usesPerUser": 1,"validFrom": 1710460800,"validUntil": 1726272000}
    `template` JSON NOT NULL COMMENT '{"membershipType": "yearly","durationMonths": 12,"maxUses": 100,"usesPerUser": 1,"validFrom": 1710460800,"validUntil": 1726272000}',
    -- Üretilecek kupon sayısı
    `quantity` INT NOT NULL COMMENT 'Üretilecek kupon sayısı',
    `generatedCount` INT DEFAULT 0,
    `usedCount` INT DEFAULT 0,
    -- pending, generating, completed, failed
    `status` enum_batch_status DEFAULT 'pending' COMMENT 'pending, generating, completed, failed',
    `createdAt` INT DEFAULT 0,
    `createdBy` INT DEFAULT 0,
    `completedAt` INT DEFAULT 0,
    CONSTRAINT `pk_coupon_batches_batchId` PRIMARY KEY (`batchId`)
);

-- Indexes
CREATE INDEX `idx_coupon_batches_academyId` ON `coupon_batches` (`academyId`);
CREATE INDEX `idx_coupon_batches_status` ON `coupon_batches` (`status`);


CREATE TABLE IF NOT EXISTS `membership_plans` (
    `planId` INT NOT NULL AUTO_INCREMENT,
    `academyId` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `code` enum_membership_type NOT NULL,
    -- 0 = sınırsız
    `durationMonths` INT NOT NULL COMMENT '0 = sınırsız',
    `price` DECIMAL(18, 2) NOT NULL,
    `discountPrice` DECIMAL(18, 2) DEFAULT 0,
    -- ["premium_courses", "certificates", "mentoring"]
    `features` JSON DEFAULT '[]' COMMENT '["premium_courses", "certificates", "mentoring"]',
    `isActive` tinyint DEFAULT 1,
    `sortOrder` INT DEFAULT 0,
    `createdAt` INT DEFAULT 0,
    `updatedAt` INT DEFAULT 0,
    CONSTRAINT `pk_membership_plans_planId` PRIMARY KEY (`planId`)
);

-- Indexes
CREATE INDEX `idx_membership_plans_academyId` ON `membership_plans` (`academyId`);
CREATE INDEX `idx_membership_plans_code` ON `membership_plans` (`code`);


CREATE TABLE IF NOT EXISTS `coupon_usage` (
    `usageId` BIGINT NOT NULL AUTO_INCREMENT,
    `couponId` INT NOT NULL,
    `memberId` INT NOT NULL,
    `academyId` INT NOT NULL,
    `membershipType` enum_membership_type NOT NULL,
    `durationMonths` INT DEFAULT 0,
    `startDate` INT DEFAULT 0,
    `expiryDate` INT DEFAULT 0,
    `ipAddress` VARCHAR(50),
    `userAgent` VARCHAR(500),
    -- email, sms, social, direct
    `source` VARCHAR(50) COMMENT 'email, sms, social, direct',
    `metadata` JSON DEFAULT '{}',
    `createdAt` INT DEFAULT 0,
    CONSTRAINT `pk_coupon_usage_usageId` PRIMARY KEY (`usageId`)
);

-- Indexes
CREATE INDEX `idx_coupon_usage_couponId` ON `coupon_usage` (`couponId`);
CREATE INDEX `idx_coupon_usage_memberId` ON `coupon_usage` (`memberId`);
CREATE INDEX `idx_coupon_usage_createdAt` ON `coupon_usage` (`createdAt`);
CREATE UNIQUE INDEX `idx_coupon_usage_couponId_memberId_unique` ON `coupon_usage` (`couponId`, `memberId`);

-- Foreign key constraints
ALTER TABLE `campaign_performance` ADD CONSTRAINT `fk_campaign_performance_batchId` FOREIGN KEY(`batchId`) REFERENCES `coupon_batches`(`batchId`);

COMMIT;

// Kupon Kodları Ana Tablosu
Table "coupons" {
  "couponId" int(11) unsigned [pk, not null, increment]
  "academyId" int(11) [not null, ref: > "academy"."academyId"]
  "code" varchar(50) [not null, unique]
  "name" varchar(255) [default: '', note: 'Kampanya adı']
  "description" varchar(500) [default: '']
  
  // Üyelik tipi ve süre
  "membershipType" enum_membership_type [not null, note: 'trial, monthly, quarterly, yearly, unlimited']
  "durationMonths" int(11) [default: 0, note: '0 = sınırsız']
  "maxUses" int(11) [default: 1, note: 'Toplam kullanım limiti (0 = sınırsız)']
  "usesPerUser" int(11) [default: 1, note: 'Kişi başı kullanım limiti']
  "currentUses" int(11) [default: 0]
  
  // Geçerlilik
  "validFrom" int(10) unsigned [default: unix_timestamp()]
  "validUntil" int(10) unsigned [default: 0, note: '0 = süresiz']
  "isActive" tinyint(1) [default: 1]
  
  // Hedef kitle kısıtlamaları
  "restrictions" json [default: '{}', note: '{
    "domains": ["company.com", "tech.com"],
    "userTypes": ["new", "existing"],
    "minAccountAge": 30,
    "firstPurchaseOnly": true
  }']
  
  // Kampanya bilgileri
  "campaignName" varchar(255) [default: '']
  "campaignGoal" varchar(100) [default: '', note: 'acquisition, retention, reactivation']
  "utmSource" varchar(100) [default: '']
  "utmMedium" varchar(100) [default: '']
  "utmCampaign" varchar(100) [default: '']
  
  // İstatistikler (güncellenebilir)
  "conversionRate" decimal(5,2) [default: 0.00]
  "totalRevenue" decimal(18,2) [default: 0.00]
  "averageOrderValue" decimal(18,2) [default: 0.00]
  "roi" decimal(10,2) [default: 0.00]
  
  // Audit
  "createdAt" int(10) unsigned [default: unix_timestamp()]
  "createdBy" int(11) [default: 0]
  "updatedAt" int(10) unsigned [default: unix_timestamp()]
  "updatedBy" int(11) [default: 0]
  
  indexes {
    (academyId) [name: 'idx_coupons_academy']
    (code) [unique, name: 'idx_coupons_code']
    (isActive, validFrom, validUntil) [name: 'idx_coupons_active']
    (campaignGoal) [name: 'idx_coupons_goal']
  }
}

// Kupon Kullanım Logları
Table "coupon_usage" {
  "usageId" bigint(20) unsigned [pk, not null, increment]
  "couponId" int(11) [not null, ref: > "coupons"."couponId"]
  "memberId" int(11) [not null, ref: > "members"."memberId"]
  "academyId" int(11) [not null]
  
  // Kullanım detayları
  "membershipType" enum_membership_type [not null]
  "durationMonths" int(11) [default: 0]
  "startDate" int(10) unsigned [default: unix_timestamp()]
  "expiryDate" int(10) unsigned [default: 0]
  
  // İşlem detayları
  "ipAddress" varchar(50) [default: '']
  "userAgent" varchar(500) [default: '']
  "source" varchar(50) [default: '', note: 'email, sms, social, direct']
  "metadata" json [default: '{}']
  
  "createdAt" int(10) unsigned [default: unix_timestamp()]
  
  indexes {
    (couponId) [name: 'idx_usage_coupon']
    (memberId) [name: 'idx_usage_member']
    (createdAt) [name: 'idx_usage_created']
  }
  
  // Bir kupon bir üye tarafından sadece bir kez kullanılabilir (limitler ayrı kontrol edilir)
  indexes {
    (couponId, memberId) [unique, name: 'uq_coupon_member']
  }
}

// Toplu Kupon Üretimi Tablosu
Table "coupon_batches" {
  "batchId" int(11) unsigned [pk, not null, increment]
  "academyId" int(11) [not null, ref: > "academy"."academyId"]
  "batchCode" varchar(100) [not null, unique, note: 'BATCH-20240315-001']
  "name" varchar(255) [default: '']
  
  // Kupon özellikleri (template)
  "template" json [not null, note: '{
    "membershipType": "yearly",
    "durationMonths": 12,
    "maxUses": 100,
    "usesPerUser": 1,
    "validFrom": 1710460800,
    "validUntil": 1726272000
  }']
  
  "quantity" int(11) [not null, note: 'Üretilecek kupon sayısı']
  "generatedCount" int(11) [default: 0]
  "usedCount" int(11) [default: 0]
  
  "status" enum_batch_status [default: 'pending', note: 'pending, generating, completed, failed']
  
  "createdAt" int(10) unsigned [default: unix_timestamp()]
  "createdBy" int(11) [default: 0]
  "completedAt" int(10) unsigned [default: 0]
  
  indexes {
    (academyId) [name: 'idx_batches_academy']
    (status) [name: 'idx_batches_status']
  }
}

// Kampanya Performans Tablosu
Table "campaign_performance" {
  "campaignId" int(11) unsigned [pk, not null, increment]
  "academyId" int(11) [not null, ref: > "academy"."academyId"]
  "couponId" int(11) [ref: > "coupons"."couponId"]
  "batchId" int(11) [ref: > "coupon_batches"."batchId"]
  
  // Kampanya metrikleri
  "impressions" int(11) [default: 0]
  "clicks" int(11) [default: 0]
  "conversions" int(11) [default: 0]
  "revenue" decimal(18,2) [default: 0.00]
  "cost" decimal(18,2) [default: 0.00]
  "roi" decimal(10,2) [default: 0.00]
  
  // Zaman bazlı
  "date" date [not null]
  "hour" tinyint(4) [default: 0]
  
  "createdAt" int(10) unsigned [default: unix_timestamp()]
  
  indexes {
    (academyId, date) [name: 'idx_performance_date']
    (couponId) [name: 'idx_performance_coupon']
  }
}

// Üyelik Tipleri Tablosu (Opsiyonel - fiyatlandırma için)
Table "membership_plans" {
  "planId" int(11) unsigned [pk, not null, increment]
  "academyId" int(11) [not null, ref: > "academy"."academyId"]
  "name" varchar(100) [not null]
  "code" enum_membership_type [not null]
  "durationMonths" int(11) [not null, note: '0 = sınırsız']
  "price" decimal(18,2) [not null]
  "discountPrice" decimal(18,2) [default: 0.00]
  "features" json [default: '[]', note: '["premium_courses", "certificates", "mentoring"]']
  "isActive" tinyint(1) [default: 1]
  "sortOrder" int(11) [default: 0]
  
  "createdAt" int(10) unsigned [default: unix_timestamp()]
  "updatedAt" int(10) unsigned [default: unix_timestamp()]
  
  indexes {
    (academyId) [name: 'idx_plans_academy']
    (code) [name: 'idx_plans_code']
  }
}

// Enum Tanımları
Enum "enum_membership_type" {
  "trial"
  "monthly"
  "quarterly"
  "yearly"
  "unlimited"
}

Enum "enum_batch_status" {
  "pending"
  "generating"
  "completed"
  "failed"
}

// View: Aktif kampanyalar
View "active_campaigns" as
  select 
    c.*,
    (c.maxUses - c.currentUses) as remainingUses,
    (c.currentUses / c.maxUses * 100) as usagePercentage
  from "coupons" c
  where c.isActive = 1
    and c.validFrom <= unix_timestamp()
    and (c.validUntil = 0 or c.validUntil >= unix_timestamp())
    and (c.maxUses = 0 or c.currentUses < c.maxUses);

// View: Kupon performans özeti
View "coupon_performance_summary" as
  select 
    c.couponId,
    c.code,
    c.campaignName,
    c.maxUses,
    c.currentUses,
    (c.currentUses / c.maxUses * 100) as usageRate,
    count(u.usageId) as actualUses,
    sum(/* revenue calculation */) as totalRevenue
  from "coupons" c
  left join "coupon_usage" u on c.couponId = u.couponId
  group by c.couponId;