225 lines
7.2 KiB
PL/PgSQL
Executable File
225 lines
7.2 KiB
PL/PgSQL
Executable File
-- Migration: Create all missing tables
|
|
-- Run this against the database to add missing schema
|
|
|
|
-- Functions
|
|
CREATE OR REPLACE FUNCTION update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Config table
|
|
CREATE TABLE IF NOT EXISTS config (
|
|
id SERIAL PRIMARY KEY,
|
|
key VARCHAR(255) UNIQUE NOT NULL,
|
|
value JSONB NOT NULL,
|
|
description TEXT,
|
|
category VARCHAR(100) DEFAULT 'general',
|
|
is_encrypted BOOLEAN DEFAULT FALSE,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by INTEGER
|
|
);
|
|
|
|
-- Session audit log
|
|
CREATE TABLE IF NOT EXISTS session_audit_log (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
session_id VARCHAR(255),
|
|
event VARCHAR(50) NOT NULL,
|
|
ip_address VARCHAR(45),
|
|
user_agent TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Plesk servers
|
|
CREATE TABLE IF NOT EXISTS plesk_servers (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
host VARCHAR(255) NOT NULL,
|
|
port INTEGER DEFAULT 8443,
|
|
api_key TEXT,
|
|
username VARCHAR(255),
|
|
password_hash TEXT,
|
|
use_https BOOLEAN DEFAULT TRUE,
|
|
verify_ssl BOOLEAN DEFAULT TRUE,
|
|
two_factor_enabled BOOLEAN DEFAULT FALSE,
|
|
two_factor_method VARCHAR(50),
|
|
connection_status VARCHAR(50) DEFAULT 'unknown',
|
|
last_connected TIMESTAMP,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS plesk_servers_updated_at BEFORE UPDATE ON plesk_servers
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- SAP servers
|
|
CREATE TABLE IF NOT EXISTS sap_servers (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
host VARCHAR(255) NOT NULL,
|
|
port INTEGER DEFAULT 50000,
|
|
company_db VARCHAR(255) NOT NULL,
|
|
username VARCHAR(255),
|
|
password_hash TEXT,
|
|
use_ssl BOOLEAN DEFAULT TRUE,
|
|
timeout_seconds INTEGER DEFAULT 30,
|
|
connection_status VARCHAR(50) DEFAULT 'unknown',
|
|
last_connected TIMESTAMP,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS sap_servers_updated_at BEFORE UPDATE ON sap_servers
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- Customers
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id SERIAL PRIMARY KEY,
|
|
sap_customer_id VARCHAR(255),
|
|
plesk_customer_id VARCHAR(255),
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255),
|
|
status VARCHAR(50) DEFAULT 'active',
|
|
sync_status VARCHAR(50) DEFAULT 'pending_sync',
|
|
metadata JSONB DEFAULT '{}',
|
|
last_sync TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Subscriptions
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
id SERIAL PRIMARY KEY,
|
|
customer_id INTEGER NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
start_date DATE,
|
|
end_date DATE,
|
|
billing_cycle VARCHAR(50),
|
|
status VARCHAR(50) DEFAULT 'active',
|
|
sync_status VARCHAR(50) DEFAULT 'pending_sync',
|
|
pricing_data JSONB DEFAULT '{}',
|
|
features JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Sync jobs
|
|
CREATE TABLE IF NOT EXISTS sync_jobs (
|
|
id SERIAL PRIMARY KEY,
|
|
job_type VARCHAR(50) NOT NULL,
|
|
sync_direction VARCHAR(50) NOT NULL,
|
|
status VARCHAR(50) DEFAULT 'pending',
|
|
started_at TIMESTAMP,
|
|
completed_at TIMESTAMP,
|
|
records_processed INTEGER DEFAULT 0,
|
|
records_failed INTEGER DEFAULT 0,
|
|
records_skipped INTEGER DEFAULT 0,
|
|
error_message TEXT,
|
|
config_snapshot JSONB DEFAULT '{}',
|
|
progress_percentage NUMERIC(5,2) DEFAULT 0,
|
|
created_by INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS sync_jobs_updated_at BEFORE UPDATE ON sync_jobs
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- Sync logs
|
|
CREATE TABLE IF NOT EXISTS sync_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
sync_job_id INTEGER NOT NULL,
|
|
entity_type VARCHAR(50) NOT NULL,
|
|
entity_id VARCHAR(255) NOT NULL,
|
|
action VARCHAR(50) NOT NULL,
|
|
status VARCHAR(50) NOT NULL,
|
|
error_message TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
resolution_status VARCHAR(50) DEFAULT 'pending',
|
|
resolution_action VARCHAR(50),
|
|
resolved_by INTEGER,
|
|
resolved_at TIMESTAMP,
|
|
conflict_details JSONB,
|
|
CONSTRAINT fk_sync_log_job FOREIGN KEY (sync_job_id) REFERENCES sync_jobs(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Alert thresholds
|
|
CREATE TABLE IF NOT EXISTS alert_thresholds (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
subscription_id INTEGER,
|
|
metric_type VARCHAR(50) NOT NULL,
|
|
threshold_value NUMERIC(15,4) NOT NULL,
|
|
comparison_operator VARCHAR(10) NOT NULL,
|
|
action VARCHAR(50) NOT NULL,
|
|
notification_channels JSONB DEFAULT '["email"]',
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
last_triggered TIMESTAMP,
|
|
created_by INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Alert history
|
|
CREATE TABLE IF NOT EXISTS alert_history (
|
|
id SERIAL PRIMARY KEY,
|
|
threshold_id INTEGER NOT NULL,
|
|
subscription_id INTEGER,
|
|
actual_value NUMERIC(15,4) NOT NULL,
|
|
triggered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
action_taken VARCHAR(50),
|
|
notification_sent BOOLEAN DEFAULT FALSE,
|
|
notification_error TEXT,
|
|
CONSTRAINT fk_alert_history_threshold FOREIGN KEY (threshold_id) REFERENCES alert_thresholds(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Billing records
|
|
CREATE TABLE IF NOT EXISTS billing_records (
|
|
id SERIAL PRIMARY KEY,
|
|
subscription_id INTEGER NOT NULL,
|
|
customer_id INTEGER NOT NULL,
|
|
period_start DATE NOT NULL,
|
|
period_end DATE NOT NULL,
|
|
usage_data JSONB NOT NULL DEFAULT '{}',
|
|
calculated_amount NUMERIC(12,2) NOT NULL,
|
|
currency VARCHAR(3) DEFAULT 'EUR',
|
|
sap_invoice_id VARCHAR(255),
|
|
invoice_status VARCHAR(50) DEFAULT 'draft',
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Pricing config
|
|
CREATE TABLE IF NOT EXISTS pricing_config (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
metric_type VARCHAR(50) NOT NULL,
|
|
unit VARCHAR(50) NOT NULL,
|
|
rate_per_unit NUMERIC(10,4) NOT NULL,
|
|
currency VARCHAR(3) DEFAULT 'EUR',
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
valid_from DATE DEFAULT CURRENT_DATE,
|
|
valid_to DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Default config data
|
|
INSERT INTO config (key, value, description, category) VALUES
|
|
('sync.default_direction', '"sap_to_plesk"', 'Default sync direction', 'sync'),
|
|
('sync.conflict_resolution', '"timestamp_based"', 'Default conflict resolution strategy', 'sync'),
|
|
('sync.interval_minutes', '60', 'Default sync interval in minutes', 'sync'),
|
|
('auth.session_timeout', '1800', 'Session timeout in seconds', 'auth'),
|
|
('system.initialized', 'false', 'System initialization flag', 'system')
|
|
ON CONFLICT (key) DO NOTHING;
|