29 lines
1.4 KiB
SQL
Executable File
29 lines
1.4 KiB
SQL
Executable File
-- 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 'Фиксации количества подписчиков по дате для истории';
|