29 lines
1.4 KiB
MySQL
29 lines
1.4 KiB
MySQL
|
-- PR SMM: каналы и снимки подписчиков
|
|||
|
|
-- Каналы (Telegram, VK, WhatsApp и др.)
|
|||
|
|
CREATE TABLE IF NOT EXISTS pr_smm_channels (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
name TEXT NOT NULL,
|
|||
|
|
type VARCHAR(20) NOT NULL CHECK (type IN ('tg', 'vk', 'wa', 'other')),
|
|||
|
|
url TEXT,
|
|||
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_pr_smm_channels_sort ON pr_smm_channels(sort_order);
|
|||
|
|
|
|||
|
|
-- Снимки количества подписчиков по дате (история фиксаций)
|
|||
|
|
CREATE TABLE IF NOT EXISTS pr_smm_subscriber_snapshots (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
channel_id BIGINT NOT NULL REFERENCES pr_smm_channels(id) ON DELETE CASCADE,
|
|||
|
|
subscribers_count INT NOT NULL,
|
|||
|
|
recorded_at DATE NOT NULL DEFAULT CURRENT_DATE,
|
|||
|
|
note TEXT,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_pr_smm_snapshots_channel ON pr_smm_subscriber_snapshots(channel_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_pr_smm_snapshots_recorded ON pr_smm_subscriber_snapshots(recorded_at DESC);
|
|||
|
|
|
|||
|
|
COMMENT ON TABLE pr_smm_channels IS 'SMM-каналы УК: Telegram, VK, WhatsApp и др.';
|
|||
|
|
COMMENT ON TABLE pr_smm_subscriber_snapshots IS 'Фиксации количества подписчиков по дате для истории';
|