Jacek Pyziak
e9a3602576
- Added optional SEMSTORM domain input field in site creation and editing forms. - Introduced SEO panel links in site dashboard and edit pages. - Created a new cron job for SEMSTORM data synchronization. - Implemented database migrations for cron logs and site SEO metrics. - Developed SiteSeoSyncService to handle SEMSTORM data fetching and storage. - Added logging functionality for cron events. - Created a new LogController to display cron logs with filtering options. - Added SEO statistics dashboard with visual representation of metrics. - Implemented site SEO metrics model for data retrieval and manipulation.
21 lines
828 B
SQL
21 lines
828 B
SQL
-- BackPRO SEMSTORM metrics history
|
|
ALTER TABLE sites
|
|
ADD COLUMN semstorm_domain VARCHAR(255) NULL AFTER url;
|
|
|
|
CREATE TABLE IF NOT EXISTS site_seo_metrics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
site_id INT NOT NULL,
|
|
metric_month DATE NOT NULL,
|
|
top3 INT NOT NULL DEFAULT 0,
|
|
top10 INT NOT NULL DEFAULT 0,
|
|
top20 INT NOT NULL DEFAULT 0,
|
|
top50 INT NOT NULL DEFAULT 0,
|
|
traffic INT NOT NULL DEFAULT 0,
|
|
source_payload TEXT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uniq_site_month (site_id, metric_month),
|
|
INDEX idx_site_month (site_id, metric_month)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|