Muzibu Corporate Spots - Final Plan v8

Veritabani Yapisi (2 Tablo)

Yeni Tablo

Kolon (accounts)

Detayli

Istatistik

1 muzibu_corporate_spots (Spot bilgileri)

Kolon	Tip	Aciklama
id	BIGINT PK	Auto increment
corporate_account_id	BIGINT FK	Hangi corporate'a ait
title	VARCHAR(255)	Spot basligi
audio_path	VARCHAR(500)	Dosya yolu
duration	INT	Sure (saniye)
file_size	INT	Boyut (bytes)
starts_at	TIMESTAMP NULL	Baslangic tarihi (NULL = her zaman)
ends_at	TIMESTAMP NULL	Bitis tarihi (NULL = suresiz)
position	INT DEFAULT 0	Siralama (kucuk = once)
is_enabled	BOOLEAN DEFAULT TRUE	Aktif mi?
is_archived	BOOLEAN DEFAULT FALSE	Arsivlendi mi?
timestamps	TIMESTAMP	created_at, updated_at

INDEX (corporate_account_id, is_enabled, is_archived)
INDEX (corporate_account_id, position)

2 muzibu_corporate_spot_plays (Dinlenme loglari)

Kolon	Tip	Aciklama
id	BIGINT PK	Auto increment
spot_id	BIGINT FK	Hangi spot
corporate_account_id	BIGINT FK	Hangi sube
user_id	BIGINT FK NULL	Kim dinledi (NULL = anonim)
was_skipped	BOOLEAN DEFAULT FALSE	Atlandi mi?
played_at	TIMESTAMP	Ne zaman dinlendi

INDEX (spot_id, played_at)
INDEX (corporate_account_id, played_at)
INDEX (user_id)

Detayli istatistik: Kim, ne zaman, hangi sube, atlandi mi? Hepsi kayit altinda.

GUNCELLEME: muzibu_corporate_accounts (+4 kolon)

spot_enabled BOOLEAN DEFAULT TRUE       -- Ana sube: Sistem acik mi?
spot_songs_between INT DEFAULT 10       -- Ana sube: Kac sarkida bir?
spot_current_index INT DEFAULT 0        -- Rotation index (siradaki spot)
spot_is_paused BOOLEAN DEFAULT FALSE    -- Her sube: Durduruldu mu?

GELECEK: Admin Onayi Gerekirse

-- spots tablosuna eklenebilir:
is_approved BOOLEAN DEFAULT FALSE       -- Admin onayladi mi?
approved_at TIMESTAMP NULL              -- Ne zaman onaylandi?
approved_by BIGINT FK NULL              -- Kim onayladi?

Tum Kararlar

Spot Atlanabilir

Skip serbest, zorlama yok.

Corporate Yukler

Kendi spotunu kendi yukler.

Arsivleme

Silmek yok, arsivle.

localStorage Sayac

Performans oncelikli.

Max 30 MB

Dosya boyutu limiti.

30 Sn Kurali

30 sn dinlenince 1 sarki.

Detayli Log

Kim, ne zaman, atlandi mi?

2 Tablo

spots + plays

Sistem Akisi

Corporate Spot Yukler

Ses + baslik + tarih + sira → spots

Sube Durdurabilir

Durdur/devam → spot_is_paused

Player Calar

10 sarkida bir spot → plays tablosuna log

Spot Calmasi Icin Kosullar

SELECT * FROM muzibu_corporate_spots
WHERE corporate_account_id = :main_corporate_id
  AND is_enabled = TRUE
  AND is_archived = FALSE
  AND (starts_at IS NULL OR starts_at <= NOW())
  AND (ends_at IS NULL OR ends_at >= NOW())
ORDER BY position ASC

-- + corporate_accounts kontrolu:
-- spot_enabled = TRUE
-- spot_is_paused = FALSE

Kurumsal Panel

Kurumsal Spotlar

Kendi spotlarinizi yonetin

Spot Sistemi

Tum subelerde

Kac sarkida bir? 10

Yilbasi Kampanyasi

0:45 • 1.2 MB

25 Ara - 05 Oca

152 dinlenme

Genel Duyuru

0:30 • 0.8 MB

Suresiz

89 dinlenme

Sube Widget

Spotlar

3 sarki sonra

Acik

Spotlar

Durduruldu

Durdu

Player

Spot Calarken

Normal Sarki

Hayal Kahvesi

Sezen Aksu

Spot Calarken

SPOT

Yilbasi Kampanyasi

0:27

30 Saniye Kurali & Loglama

// Sarki 30 sn dinlenince sayac artar
if (currentTime >= 30 && !songCounted) {
    songCounted = true;
    let count = localStorage.getItem('spot_song_count') || 0;
    count++;

    if (count >= spotSongsBetween) {
        playNextSpot();
        localStorage.setItem('spot_song_count', 0);
    } else {
        localStorage.setItem('spot_song_count', count);
    }
}

// Spot bitince/atlaninca → plays tablosuna log
fetch('/api/spot/played', {
    body: JSON.stringify({
        spot_id: currentSpot.id,
        was_skipped: wasSkipped
    })
});

Istatistikler

plays Tablosu ile Elde Edilenler

Toplam Dinlenme

COUNT(*) WHERE spot_id = X

Atlama Orani

COUNT(was_skipped=true) / total

Sube Bazli

GROUP BY corporate_account_id

Tarih Bazli

GROUP BY DATE(played_at)

Saat Bazli

GROUP BY HOUR(played_at)

Kullanici Bazli

GROUP BY user_id

Trend Analizi

Gunluk/haftalik grafik

En Cok Dinlenen

ORDER BY count DESC

Ornek SQL Sorgulari

-- Spot bazli toplam dinlenme

SELECT spot_id, COUNT(*) as total,
       SUM(was_skipped) as skipped
FROM plays GROUP BY spot_id;

-- Gunluk dinlenme grafigi

SELECT DATE(played_at) as date, COUNT(*) as total
FROM plays WHERE spot_id = 1
GROUP BY DATE(played_at) ORDER BY date;

-- Sube karsilastirmasi

SELECT corporate_account_id, COUNT(*) as total
FROM plays GROUP BY corporate_account_id
ORDER BY total DESC;

API Endpoints

Corporate

GET /api/corporate/spot-settings

PUT /api/corporate/spot-settings

GET /api/corporate/spots

POST /api/corporate/spots

PUT /api/corporate/spots/{id}

POST /api/corporate/spots/reorder

POST /api/corporate/spots/{id}/archive

POST /api/corporate/spots/{id}/unarchive

GET /api/corporate/spot-stats

GET /api/corporate/spots/{id}/stats

Sube

POST /api/branch/spots/pause

POST /api/branch/spots/resume

Player

GET /api/spot/init

GET /api/spot/next

POST /api/spot/played ← plays tablosuna log

Gelistirme Sirasi

1 Veritabani

2 migration + 2 model

2 Kurumsal Panel

CRUD + yukleme + istatistik

3 Sube Widget

Durdur/devam

4 Player

JS + 30 sn kurali + loglama

5 Test & Deploy

Migration + test