-- Anomaly Detection Tables Migration
-- IC³ Platform - AI Analytics Engine

-- Main Anomalies Table
CREATE TABLE IF NOT EXISTS ic3_anomalies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  asset_id VARCHAR(100) NOT NULL,
  asset_name VARCHAR(255),
  asset_type VARCHAR(50),

  -- Location
  location_gps POINT,
  location_zone VARCHAR(100),
  location_dma VARCHAR(100),

  -- Timing
  detected_at TIMESTAMP NOT NULL DEFAULT NOW(),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  -- Severity & Confidence
  severity DECIMAL(3,1) NOT NULL,
  confidence DECIMAL(5,2) NOT NULL,

  -- Measurement Data
  measurement_current DECIMAL(10,3),
  measurement_baseline DECIMAL(10,3),
  measurement_deviation DECIMAL(5,2),
  measurement_unit VARCHAR(20),

  -- Probable Causes (JSON array)
  probable_causes JSONB,

  -- Status
  status VARCHAR(50) NOT NULL DEFAULT 'active',

  -- Integration
  work_order_id UUID,

  -- Indexing
  FOREIGN KEY (work_order_id) REFERENCES ic3_work_orders(id) ON DELETE SET NULL
);

-- Create indexes on anomalies table
CREATE INDEX idx_anomalies_asset_id ON ic3_anomalies(asset_id);
CREATE INDEX idx_anomalies_status ON ic3_anomalies(status);
CREATE INDEX idx_anomalies_severity ON ic3_anomalies(severity DESC);
CREATE INDEX idx_anomalies_detected_at ON ic3_anomalies(detected_at DESC);
CREATE INDEX idx_anomalies_confidence ON ic3_anomalies(confidence DESC);
CREATE INDEX idx_anomalies_asset_type ON ic3_anomalies(asset_type);

-- Audit Trail Table
CREATE TABLE IF NOT EXISTS ic3_anomaly_audit_trail (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  anomaly_id UUID NOT NULL,
  action VARCHAR(255) NOT NULL,
  actor_user_id UUID,
  actor_name VARCHAR(255),
  actor_role VARCHAR(50),
  details TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),

  FOREIGN KEY (anomaly_id) REFERENCES ic3_anomalies(id) ON DELETE CASCADE
);

CREATE INDEX idx_audit_trail_anomaly_id ON ic3_anomaly_audit_trail(anomaly_id);
CREATE INDEX idx_audit_trail_created_at ON ic3_anomaly_audit_trail(created_at DESC);

-- Alert Rules Table
CREATE TABLE IF NOT EXISTS ic3_alert_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sensor_type VARCHAR(100) NOT NULL UNIQUE,

  -- Threshold Configuration (JSON)
  threshold_upper DECIMAL(10,3),
  threshold_lower DECIMAL(10,3),
  percentage_deviation DECIMAL(5,2),

  -- Severity Level
  severity VARCHAR(50) NOT NULL,

  -- Status & Metadata
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  updated_by UUID,

  FOREIGN KEY (updated_by) REFERENCES ic3_users(user_id) ON DELETE SET NULL
);

CREATE INDEX idx_alert_rules_sensor_type ON ic3_alert_rules(sensor_type);
CREATE INDEX idx_alert_rules_severity ON ic3_alert_rules(severity);

-- Sensor Health Scores Table
CREATE TABLE IF NOT EXISTS ic3_sensor_health (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sensor_id VARCHAR(100) NOT NULL UNIQUE,
  sensor_name VARCHAR(255),

  -- Trust Scoring
  trust_score DECIMAL(5,2) NOT NULL DEFAULT 100,
  status VARCHAR(50) NOT NULL DEFAULT 'healthy',

  -- Maintenance
  last_calibration TIMESTAMP,

  -- Metrics
  anomaly_count_7d INT DEFAULT 0,
  false_positive_rate DECIMAL(5,2) DEFAULT 0,

  -- Tracking
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  FOREIGN KEY (sensor_id) REFERENCES ic3_assets(asset_id) ON DELETE CASCADE
);

CREATE INDEX idx_sensor_health_trust_score ON ic3_sensor_health(trust_score);
CREATE INDEX idx_sensor_health_status ON ic3_sensor_health(status);

-- Anomaly Statistics Table (for fast dashboard queries)
CREATE TABLE IF NOT EXISTS ic3_anomaly_stats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  stat_date DATE NOT NULL,

  -- Counts
  total_anomalies INT DEFAULT 0,
  active_anomalies INT DEFAULT 0,
  resolved_anomalies INT DEFAULT 0,

  -- Distribution
  critical_count INT DEFAULT 0,
  high_count INT DEFAULT 0,
  medium_count INT DEFAULT 0,
  low_count INT DEFAULT 0,

  -- Metrics
  avg_confidence DECIMAL(5,2),
  mttr_minutes DECIMAL(8,2),
  false_pos_rate DECIMAL(5,2),

  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(stat_date)
);

CREATE INDEX idx_anomaly_stats_date ON ic3_anomaly_stats(stat_date DESC);

-- Materialized View for Quick Dashboard Queries
CREATE OR REPLACE VIEW v_anomaly_summary AS
SELECT
  COUNT(*) as total_count,
  COUNT(CASE WHEN status = 'active' THEN 1 END) as active_count,
  COUNT(CASE WHEN severity >= 9.0 THEN 1 END) as critical_count,
  COUNT(CASE WHEN severity >= 7.0 AND severity < 9.0 THEN 1 END) as high_count,
  AVG(confidence) as avg_confidence,
  CURRENT_TIMESTAMP as last_updated
FROM ic3_anomalies
WHERE detected_at >= NOW() - INTERVAL '7 days';

-- View for Top Assets by Anomaly Count
CREATE OR REPLACE VIEW v_top_anomalous_assets AS
SELECT
  asset_id,
  asset_name,
  asset_type,
  COUNT(*) as anomaly_count,
  AVG(severity) as avg_severity,
  COUNT(CASE WHEN status = 'active' THEN 1 END) as active_count
FROM ic3_anomalies
WHERE detected_at >= NOW() - INTERVAL '30 days'
GROUP BY asset_id, asset_name, asset_type
ORDER BY anomaly_count DESC
LIMIT 20;

-- Grants (adjust to your role structure)
GRANT SELECT ON ic3_anomalies TO ic3_api_role;
GRANT SELECT, INSERT, UPDATE ON ic3_anomalies TO ic3_api_admin_role;
GRANT SELECT ON ic3_anomaly_audit_trail TO ic3_api_role;
GRANT SELECT, INSERT ON ic3_anomaly_audit_trail TO ic3_api_admin_role;
GRANT SELECT ON ic3_alert_rules TO ic3_api_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ic3_alert_rules TO ic3_api_admin_role;
GRANT SELECT ON ic3_sensor_health TO ic3_api_role;
GRANT SELECT, INSERT, UPDATE ON ic3_sensor_health TO ic3_api_admin_role;
GRANT SELECT ON ic3_anomaly_stats TO ic3_api_role;
