-- CEDP V014 - High Availability, Cluster and Disaster Recovery
CREATE TABLE IF NOT EXISTS cluster_nodes_v014 (id varchar(36) PRIMARY KEY,node_name varchar(160) NOT NULL,node_type varchar(40) NOT NULL DEFAULT 'api',region varchar(80) NOT NULL DEFAULT 'primary',zone varchar(80) NOT NULL DEFAULT 'zone-a',address varchar(500) NOT NULL DEFAULT '',version varchar(40) NOT NULL DEFAULT '0.14.0',status varchar(30) NOT NULL DEFAULT 'starting',role varchar(30) NOT NULL DEFAULT 'follower',capabilities json NOT NULL DEFAULT '[]',metadata_json json NOT NULL DEFAULT '{}',last_heartbeat_at timestamptz NOT NULL,started_at timestamptz NOT NULL,updated_at timestamptz NOT NULL,CONSTRAINT uq_cluster_node_name_type UNIQUE(node_name,node_type));
CREATE TABLE IF NOT EXISTS cluster_leases_v014 (name varchar(160) PRIMARY KEY,holder_node_id varchar(36) REFERENCES cluster_nodes_v014(id) ON DELETE SET NULL,fencing_token integer NOT NULL DEFAULT 0,acquired_at timestamptz,expires_at timestamptz,updated_at timestamptz NOT NULL);
CREATE TABLE IF NOT EXISTS failover_events_v014 (id varchar(36) PRIMARY KEY,component varchar(100) NOT NULL,event_type varchar(60) NOT NULL,from_node_id varchar(36) REFERENCES cluster_nodes_v014(id) ON DELETE SET NULL,to_node_id varchar(36) REFERENCES cluster_nodes_v014(id) ON DELETE SET NULL,reason text NOT NULL DEFAULT '',status varchar(30) NOT NULL DEFAULT 'completed',evidence json NOT NULL DEFAULT '{}',initiated_by varchar(36) REFERENCES users(id),created_at timestamptz NOT NULL);
CREATE TABLE IF NOT EXISTS backup_policies_v014 (id varchar(36) PRIMARY KEY,name varchar(180) UNIQUE NOT NULL,resource_type varchar(50) NOT NULL,schedule_cron varchar(120) NOT NULL,retention_days integer NOT NULL,backup_type varchar(30) NOT NULL,destination_prefix varchar(500) NOT NULL,encryption_required boolean NOT NULL,enabled boolean NOT NULL,last_run_at timestamptz,next_run_at timestamptz,created_by varchar(36) REFERENCES users(id) NOT NULL,created_at timestamptz NOT NULL);
CREATE TABLE IF NOT EXISTS backup_runs_v014 (id varchar(36) PRIMARY KEY,policy_id varchar(36) REFERENCES backup_policies_v014(id) ON DELETE CASCADE NOT NULL,status varchar(30) NOT NULL,object_key varchar(1000) NOT NULL DEFAULT '',checksum varchar(128) NOT NULL DEFAULT '',size_bytes integer NOT NULL DEFAULT 0,encryption varchar(80) NOT NULL DEFAULT 'fernet',error text NOT NULL DEFAULT '',triggered_by varchar(36) REFERENCES users(id),started_at timestamptz,finished_at timestamptz,created_at timestamptz NOT NULL);
CREATE TABLE IF NOT EXISTS disaster_recovery_plans_v014 (id varchar(36) PRIMARY KEY,name varchar(180) UNIQUE NOT NULL,scope varchar(100) NOT NULL,rpo_minutes integer NOT NULL,rto_minutes integer NOT NULL,primary_region varchar(80) NOT NULL,recovery_region varchar(80) NOT NULL,runbook json NOT NULL DEFAULT '[]',validation_checks json NOT NULL DEFAULT '[]',owner_id varchar(36) REFERENCES users(id) NOT NULL,status varchar(30) NOT NULL,approved_by varchar(36) REFERENCES users(id),approved_at timestamptz,created_at timestamptz NOT NULL,updated_at timestamptz NOT NULL);
CREATE TABLE IF NOT EXISTS disaster_recovery_drills_v014 (id varchar(36) PRIMARY KEY,plan_id varchar(36) REFERENCES disaster_recovery_plans_v014(id) ON DELETE CASCADE NOT NULL,status varchar(30) NOT NULL,scenario text NOT NULL DEFAULT '',results json NOT NULL DEFAULT '{}',measured_rpo_minutes integer,measured_rto_minutes integer,passed boolean,executed_by varchar(36) REFERENCES users(id),scheduled_at timestamptz,started_at timestamptz,finished_at timestamptz,created_at timestamptz NOT NULL);