patchcenter/schema.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 16.3
-- Dumped by pg_dump version 16.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: citext; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;


--
-- Name: EXTENSION citext; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION citext IS 'data type for case-insensitive character strings';


--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


--
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';


--
-- Name: qms_set_updated_at(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.qms_set_updated_at() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;


--
-- Name: update_domenv_count(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.update_domenv_count() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        UPDATE domain_environments SET nb_servers = (
            SELECT COUNT(*) FROM servers WHERE domain_env_id = NEW.domain_env_id
        ) WHERE id = NEW.domain_env_id;
    END IF;
    IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
        UPDATE domain_environments SET nb_servers = (
            SELECT COUNT(*) FROM servers WHERE domain_env_id = OLD.domain_env_id
        ) WHERE id = OLD.domain_env_id;
    END IF;
    RETURN NULL;
END;
$$;


--
-- Name: update_timestamp(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.update_timestamp() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: allowed_networks; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.allowed_networks (
    id integer NOT NULL,
    cidr character varying(50) NOT NULL,
    description character varying(200),
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: allowed_networks_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.allowed_networks_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: allowed_networks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.allowed_networks_id_seq OWNED BY public.allowed_networks.id;


--
-- Name: app_map; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.app_map (
    id integer NOT NULL,
    app_name character varying(200) NOT NULL,
    app_type character varying(50),
    description text,
    servers jsonb DEFAULT '[]'::jsonb,
    ports jsonb DEFAULT '[]'::jsonb,
    flows jsonb DEFAULT '[]'::jsonb,
    last_scan timestamp with time zone,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);


--
-- Name: app_map_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.app_map_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: app_map_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.app_map_id_seq OWNED BY public.app_map.id;


--
-- Name: app_secrets; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.app_secrets (
    key character varying(100) NOT NULL,
    value text NOT NULL,
    description character varying(255),
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: applications; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.applications (
    id integer NOT NULL,
    nom_court character varying(50) NOT NULL,
    nom_complet character varying(200),
    description text,
    editeur character varying(100),
    criticite character varying(10) DEFAULT 'standard'::character varying,
    need_pre_stop boolean DEFAULT false NOT NULL,
    pre_stop_cmd text,
    post_start_cmd text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    itop_id integer,
    status character varying(30),
    responsable_dsi_nom character varying(200),
    responsable_dsi_email character varying(255),
    admin_tech_nom character varying(200),
    admin_tech_email character varying(255),
    etat character varying(30),
    ioda_libelle character varying(200),
    ioda_lib_court character varying(50),
    ioda_code_pos character varying(20),
    ioda_type character varying(50),
    ioda_statut character varying(50),
    ioda_alias text,
    ioda_perimetre character varying(100),
    ioda_dept_domaine character varying(200),
    ioda_resp_metier character varying(100),
    ioda_resp_dsi character varying(100),
    ioda_nb_components integer,
    ioda_commentaire text,
    ioda_imported_at timestamp with time zone,
    CONSTRAINT applications_criticite_check CHECK (((criticite)::text = ANY (ARRAY[('critique'::character varying)::text, ('haute'::character varying)::text, ('standard'::character varying)::text, ('basse'::character varying)::text])))
);


--
-- Name: TABLE applications; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.applications IS 'Catalogue applications — permet pre/post scripts par app';


--
-- Name: COLUMN applications.ioda_libelle; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.applications.ioda_libelle IS 'Libellé service métier IODA (clé d''import)';


--
-- Name: COLUMN applications.ioda_code_pos; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.applications.ioda_code_pos IS 'Code zone POS IODA (TRA, ADV, …)';


--
-- Name: COLUMN applications.ioda_resp_metier; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.applications.ioda_resp_metier IS 'Responsable Service Métier (à notifier patching)';


--
-- Name: COLUMN applications.ioda_resp_dsi; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.applications.ioda_resp_dsi IS 'Responsable Service DSI (à notifier patching)';


--
-- Name: applications_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.applications_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: applications_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.applications_id_seq OWNED BY public.applications.id;


--
-- Name: audit_log; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.audit_log (
    id bigint NOT NULL,
    "timestamp" timestamp with time zone DEFAULT now() NOT NULL,
    user_id integer,
    username character varying(50),
    action character varying(50) NOT NULL,
    entity_type character varying(30),
    entity_id integer,
    details jsonb,
    ip_address inet
);


--
-- Name: audit_log_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.audit_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: audit_log_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.audit_log_id_seq OWNED BY public.audit_log.id;


--
-- Name: campaign_accord_details; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.campaign_accord_details (
    id integer NOT NULL,
    accord_id integer NOT NULL,
    session_id integer NOT NULL,
    decision character varying(20) DEFAULT 'approved'::character varying NOT NULL,
    reason text,
    CONSTRAINT campaign_accord_details_decision_check CHECK (((decision)::text = ANY (ARRAY[('approved'::character varying)::text, ('rejected'::character varying)::text, ('reported'::character varying)::text])))
);


--
-- Name: campaign_accord_details_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.campaign_accord_details_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: campaign_accord_details_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.campaign_accord_details_id_seq OWNED BY public.campaign_accord_details.id;


--
-- Name: campaign_accords; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.campaign_accords (
    id integer NOT NULL,
    campaign_id integer NOT NULL,
    contact_id integer NOT NULL,
    status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
    response_date timestamp with time zone,
    comment text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT campaign_accords_status_check CHECK (((status)::text = ANY (ARRAY[('pending'::character varying)::text, ('approved'::character varying)::text, ('partial'::character varying)::text, ('rejected'::character varying)::text, ('reported'::character varying)::text])))
);


--
-- Name: campaign_accords_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.campaign_accords_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: campaign_accords_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.campaign_accords_id_seq OWNED BY public.campaign_accords.id;


--
-- Name: campaign_operator_limits; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.campaign_operator_limits (
    id integer NOT NULL,
    campaign_id integer NOT NULL,
    user_id integer NOT NULL,
    max_servers integer DEFAULT 0 NOT NULL,
    note text
);


--
-- Name: campaign_operator_limits_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.campaign_operator_limits_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: campaign_operator_limits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.campaign_operator_limits_id_seq OWNED BY public.campaign_operator_limits.id;


--
-- Name: campaigns; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.campaigns (
    id integer NOT NULL,
    week_code character varying(10) NOT NULL,
    year integer DEFAULT EXTRACT(year FROM now()) NOT NULL,
    label character varying(100),
    status character varying(20) DEFAULT 'draft'::character varying NOT NULL,
    date_start date,
    date_end date,
    total_servers integer DEFAULT 0,
    patched_ok integer DEFAULT 0,
    patched_ko integer DEFAULT 0,
    pending integer DEFAULT 0,
    created_by integer,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    campaign_type character varying(20) DEFAULT 'standard'::character varying NOT NULL,
    CONSTRAINT campaigns_campaign_type_check CHECK (((campaign_type)::text = ANY (ARRAY[('standard'::character varying)::text, ('quickwin'::character varying)::text, ('safe_patching'::character varying)::text]))),
    CONSTRAINT campaigns_status_check CHECK (((status)::text = ANY (ARRAY[('draft'::character varying)::text, ('pending_validation'::character varying)::text, ('planned'::character varying)::text, ('in_progress'::character varying)::text, ('completed'::character varying)::text, ('cancelled'::character varying)::text])))
);


--
-- Name: campaigns_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.campaigns_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: campaigns_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.campaigns_id_seq OWNED BY public.campaigns.id;


--
-- Name: chassis; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.chassis (
    id integer NOT NULL,
    hostname public.citext NOT NULL,
    fqdn character varying(255),
    site character varying(100),
    domain_ltd character varying(50),
    description text,
    responsable_nom text,
    moved_from_server_id integer,
    created_at timestamp with time zone DEFAULT now()
);


--
-- Name: chassis_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.chassis_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: chassis_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.chassis_id_seq OWNED BY public.chassis.id;


--
-- Name: cluster_members; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.cluster_members (
    id integer NOT NULL,
    cluster_id integer NOT NULL,
    server_id integer NOT NULL,
    patch_order integer DEFAULT 1 NOT NULL,
    is_active boolean DEFAULT true NOT NULL
);


--
-- Name: cluster_members_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.cluster_members_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: cluster_members_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.cluster_members_id_seq OWNED BY public.cluster_members.id;


--
-- Name: clusters; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.clusters (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    description text,
    patch_strategy character varying(20) DEFAULT 'sequential'::character varying NOT NULL,
    max_parallel integer DEFAULT 1 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT clusters_patch_strategy_check CHECK (((patch_strategy)::text = ANY (ARRAY[('sequential'::character varying)::text, ('rolling'::character varying)::text, ('parallel'::character varying)::text])))
);


--
-- Name: TABLE clusters; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.clusters IS 'Groupes de serveurs avec ordre de patching (ex: Satellite SAT1 avant SAT2)';


--
-- Name: clusters_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.clusters_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: clusters_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.clusters_id_seq OWNED BY public.clusters.id;


--
-- Name: contact_scopes; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.contact_scopes (
    id integer NOT NULL,
    contact_id integer NOT NULL,
    scope_type character varying(20) NOT NULL,
    scope_value character varying(100) NOT NULL,
    env_scope character varying(20) DEFAULT 'all'::character varying,
    CONSTRAINT contact_scopes_scope_type_check CHECK (((scope_type)::text = ANY (ARRAY[('domain'::character varying)::text, ('application'::character varying)::text, ('server'::character varying)::text, ('app_group'::character varying)::text, ('zone'::character varying)::text])))
);


--
-- Name: contact_scopes_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.contact_scopes_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: contact_scopes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.contact_scopes_id_seq OWNED BY public.contact_scopes.id;


--
-- Name: contacts; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.contacts (
    id integer NOT NULL,
    name character varying(150) NOT NULL,
    email character varying(255) NOT NULL,
    role character varying(30) DEFAULT 'responsable'::character varying NOT NULL,
    is_active boolean DEFAULT false NOT NULL,
    is_verified boolean DEFAULT false NOT NULL,
    portal_token character varying(100),
    portal_token_expires timestamp with time zone,
    last_portal_access timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    itop_id integer,
    telephone character varying(50),
    team character varying(100),
    function character varying(200),
    CONSTRAINT contacts_role_check CHECK (((role)::text = ANY (ARRAY[('responsable_domaine'::character varying)::text, ('responsable_prod'::character varying)::text, ('responsable_applicatif'::character varying)::text, ('referent_technique'::character varying)::text, ('chef_projet'::character varying)::text, ('editeur'::character varying)::text, ('ra_prod'::character varying)::text, ('ra_recette'::character varying)::text, ('ra_preprod'::character varying)::text, ('ra_test'::character varying)::text, ('ra_dev'::character varying)::text, ('contact_technique'::character varying)::text, ('autre'::character varying)::text])))
);


--
-- Name: contacts_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.contacts_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: contacts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.contacts_id_seq OWNED BY public.contacts.id;


--
-- Name: default_assignments; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.default_assignments (
    id integer NOT NULL,
    rule_type character varying(20) NOT NULL,
    rule_value character varying(100) NOT NULL,
    user_id integer NOT NULL,
    priority integer DEFAULT 10 NOT NULL,
    note text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT default_assignments_rule_type_check CHECK (((rule_type)::text = ANY (ARRAY[('server'::character varying)::text, ('app_type'::character varying)::text, ('app_group'::character varying)::text, ('domain'::character varying)::text, ('zone'::character varying)::text])))
);


--
-- Name: default_assignments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.default_assignments_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: default_assignments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.default_assignments_id_seq OWNED BY public.default_assignments.id;


--
-- Name: domain_environments; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.domain_environments (
    id integer NOT NULL,
    domain_id integer NOT NULL,
    environment_id integer NOT NULL,
    responsable_nom character varying(100),
    responsable_email character varying(255),
    referent_nom character varying(100),
    referent_email character varying(255),
    patch_window character varying(100),
    patch_excludes text,
    nb_servers integer DEFAULT 0,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: TABLE domain_environments; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.domain_environments IS 'Combinaison domaine+env avec responsables spécifiques';


--
-- Name: domain_environments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.domain_environments_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: domain_environments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.domain_environments_id_seq OWNED BY public.domain_environments.id;


--
-- Name: domain_ltd_list; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.domain_ltd_list (
    id integer NOT NULL,
    name character varying NOT NULL,
    description text,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: domain_ltd_list_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.domain_ltd_list_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: domain_ltd_list_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.domain_ltd_list_id_seq OWNED BY public.domain_ltd_list.id;


--
-- Name: domain_responsables; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.domain_responsables (
    id integer NOT NULL,
    domain_id integer NOT NULL,
    nom character varying(100) NOT NULL,
    email character varying(255),
    telephone character varying(20),
    role_resp character varying(50) DEFAULT 'responsable'::character varying,
    is_primary boolean DEFAULT false NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT domain_responsables_role_resp_check CHECK (((role_resp)::text = ANY (ARRAY[('responsable'::character varying)::text, ('adjoint'::character varying)::text, ('referent'::character varying)::text])))
);


--
-- Name: domain_responsables_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.domain_responsables_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: domain_responsables_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.domain_responsables_id_seq OWNED BY public.domain_responsables.id;


--
-- Name: domains; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.domains (
    id integer NOT NULL,
    name character varying(50) NOT NULL,
    code character varying(10) NOT NULL,
    description text,
    default_excludes text,
    default_patch_window character varying(100),
    default_patch_frequency character varying(20) DEFAULT 'monthly'::character varying,
    teams_webhook_url character varying(500),
    teams_sharepoint_folder character varying(100),
    display_order integer DEFAULT 0 NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: domains_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.domains_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: domains_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.domains_id_seq OWNED BY public.domains.id;


--
-- Name: environments; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.environments (
    id integer NOT NULL,
    name character varying(50) NOT NULL,
    code character varying(10) NOT NULL,
    ssh_method character varying(20) DEFAULT 'ssh_key'::character varying NOT NULL,
    requires_approval boolean DEFAULT false NOT NULL,
    approval_level character varying(20) DEFAULT 'operator'::character varying,
    display_order integer DEFAULT 0 NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT environments_approval_level_check CHECK (((approval_level)::text = ANY (ARRAY[('operator'::character varying)::text, ('secops'::character varying)::text, ('dsi'::character varying)::text]))),
    CONSTRAINT environments_ssh_method_check CHECK (((ssh_method)::text = ANY (ARRAY[('ssh_key'::character varying)::text, ('ssh_pwd'::character varying)::text, ('ssh_psmp'::character varying)::text, ('rdp_psm'::character varying)::text, ('rdp_pwd'::character varying)::text])))
);


--
-- Name: environments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.environments_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: environments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.environments_id_seq OWNED BY public.environments.id;


--
-- Name: eol_references; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.eol_references (
    id integer NOT NULL,
    os_pattern character varying(100) NOT NULL,
    eol_date date NOT NULL,
    els_date date,
    status character varying(10) NOT NULL,
    notes text,
    CONSTRAINT eol_references_status_check CHECK (((status)::text = ANY (ARRAY[('eol'::character varying)::text, ('els'::character varying)::text, ('active'::character varying)::text])))
);


--
-- Name: eol_references_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.eol_references_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: eol_references_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.eol_references_id_seq OWNED BY public.eol_references.id;


--
-- Name: hypervisors; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.hypervisors (
    id integer NOT NULL,
    hostname public.citext NOT NULL,
    fqdn character varying(255),
    cluster_name character varying(100),
    underlying_server character varying(100),
    site character varying(100),
    domain_ltd character varying(50),
    description text,
    responsable_nom text,
    moved_from_server_id integer,
    created_at timestamp with time zone DEFAULT now(),
    kind character varying(20) DEFAULT 'hypervisor'::character varying
);


--
-- Name: hypervisors_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.hypervisors_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: hypervisors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.hypervisors_id_seq OWNED BY public.hypervisors.id;


--
-- Name: import_history; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.import_history (
    id integer NOT NULL,
    source character varying(50) NOT NULL,
    filename character varying(255),
    imported_by integer,
    imported_at timestamp with time zone DEFAULT now() NOT NULL,
    total_rows integer DEFAULT 0,
    created_count integer DEFAULT 0,
    updated_count integer DEFAULT 0,
    skipped_count integer DEFAULT 0,
    error_count integer DEFAULT 0,
    errors jsonb,
    status character varying(20) DEFAULT 'completed'::character varying
);


--
-- Name: import_history_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.import_history_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: import_history_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.import_history_id_seq OWNED BY public.import_history.id;


--
-- Name: network_flow_map; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.network_flow_map (
    id integer NOT NULL,
    audit_id integer,
    source_server_id integer,
    source_hostname character varying(200),
    source_ip character varying(50),
    dest_ip character varying(50),
    dest_port integer,
    dest_hostname character varying(200),
    dest_server_id integer,
    process_name character varying(150),
    process_user character varying(50),
    service_name character varying(50),
    direction character varying(10),
    connection_count integer DEFAULT 1,
    state character varying(20),
    audit_date timestamp with time zone DEFAULT now()
);


--
-- Name: network_flow_map_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.network_flow_map_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: network_flow_map_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.network_flow_map_id_seq OWNED BY public.network_flow_map.id;


--
-- Name: patch_history; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.patch_history (
    id integer NOT NULL,
    server_id integer NOT NULL,
    campaign_id integer,
    intervenant_id integer,
    date_patch timestamp with time zone NOT NULL,
    status character varying(20) NOT NULL,
    command text,
    packages_updated integer DEFAULT 0,
    reboot_done boolean DEFAULT false,
    snapshot_name character varying(200),
    notes text,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: TABLE patch_history; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.patch_history IS 'Historique résumé — alimenté automatiquement après chaque campagne';


--
-- Name: patch_history_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.patch_history_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: patch_history_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.patch_history_id_seq OWNED BY public.patch_history.id;


--
-- Name: patch_planning; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.patch_planning (
    id integer NOT NULL,
    year integer NOT NULL,
    week_number integer NOT NULL,
    week_code character varying(5) NOT NULL,
    week_start date NOT NULL,
    week_end date NOT NULL,
    cycle integer,
    domain_code character varying(20),
    env_scope character varying(20) DEFAULT 'all'::character varying NOT NULL,
    status character varying(20) DEFAULT 'open'::character varying NOT NULL,
    note text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT patch_planning_env_scope_check CHECK (((env_scope)::text = ANY (ARRAY[('prod'::character varying)::text, ('hprod'::character varying)::text, ('all'::character varying)::text, ('pilot'::character varying)::text, ('prod_pilot'::character varying)::text]))),
    CONSTRAINT patch_planning_status_check CHECK (((status)::text = ANY (ARRAY[('open'::character varying)::text, ('freeze'::character varying)::text, ('holiday'::character varying)::text, ('empty'::character varying)::text])))
);


--
-- Name: patch_planning_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.patch_planning_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: patch_planning_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.patch_planning_id_seq OWNED BY public.patch_planning.id;


--
-- Name: patch_sessions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.patch_sessions (
    id integer NOT NULL,
    campaign_id integer NOT NULL,
    server_id integer NOT NULL,
    intervenant_id integer,
    date_prevue timestamp with time zone,
    duree_estimee character varying(20),
    accord_responsable boolean DEFAULT false,
    date_report timestamp with time zone,
    motif_report text,
    prereq_ssh character varying(10) DEFAULT 'pending'::character varying,
    prereq_disk_root integer,
    prereq_disk_log integer,
    prereq_satellite character varying(10) DEFAULT 'pending'::character varying,
    prereq_date timestamp with time zone,
    snapshot_name character varying(200),
    snapshot_created boolean DEFAULT false,
    snapshot_forced boolean DEFAULT false,
    snapshot_justif text,
    snapshot_date timestamp with time zone,
    patch_command text,
    patch_output text,
    pb_espace_disque boolean DEFAULT false,
    status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
    date_realise timestamp with time zone,
    reboot_required boolean DEFAULT false,
    reboot_date timestamp with time zone,
    packages_before text,
    packages_after text,
    packages_updated integer DEFAULT 0,
    postcheck_services character varying(10) DEFAULT 'pending'::character varying,
    postcheck_ports character varying(10) DEFAULT 'pending'::character varying,
    postcheck_date timestamp with time zone,
    notif_debut_sent boolean DEFAULT false,
    notif_reboot_sent boolean DEFAULT false,
    notif_fin_sent boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    exclusion_reason character varying(30),
    exclusion_detail text,
    excluded_by character varying(100),
    excluded_at timestamp with time zone,
    rollback_method character varying(20),
    rollback_justif text,
    prereq_validated boolean DEFAULT false NOT NULL,
    prereq_validated_by character varying(100),
    prereq_validated_at timestamp with time zone,
    prereq_disk_root_mb integer,
    prereq_disk_var_mb integer,
    prereq_disk_ok boolean,
    heure_prevue character varying(20),
    assigned_at timestamp with time zone,
    forced_assignment boolean DEFAULT false NOT NULL,
    state_before jsonb,
    state_after jsonb,
    state_diff jsonb,
    note text,
    CONSTRAINT patch_sessions_exclusion_check CHECK (((exclusion_reason IS NULL) OR ((exclusion_reason)::text = ANY (ARRAY[('eol'::character varying)::text, ('creneau_inadequat'::character varying)::text, ('intervention_non_secops'::character varying)::text, ('report_cycle'::character varying)::text, ('non_patchable'::character varying)::text, ('autre'::character varying)::text])))),
    CONSTRAINT patch_sessions_postcheck_services_check CHECK (((postcheck_services)::text = ANY (ARRAY[('pending'::character varying)::text, ('ok'::character varying)::text, ('ko'::character varying)::text, ('skip'::character varying)::text]))),
    CONSTRAINT patch_sessions_prereq_satellite_check CHECK (((prereq_satellite)::text = ANY (ARRAY[('pending'::character varying)::text, ('ok'::character varying)::text, ('ko'::character varying)::text, ('na'::character varying)::text, ('skip'::character varying)::text]))),
    CONSTRAINT patch_sessions_prereq_ssh_check CHECK (((prereq_ssh)::text = ANY (ARRAY[('pending'::character varying)::text, ('ok'::character varying)::text, ('ko'::character varying)::text, ('skip'::character varying)::text]))),
    CONSTRAINT patch_sessions_rollback_check CHECK (((rollback_method IS NULL) OR ((rollback_method)::text = ANY (ARRAY[('snapshot'::character varying)::text, ('commvault'::character varying)::text, ('commcell'::character varying)::text, ('force'::character varying)::text, ('na'::character varying)::text])))),
    CONSTRAINT patch_sessions_status_check CHECK (((status)::text = ANY (ARRAY[('pending'::character varying)::text, ('prereq_ok'::character varying)::text, ('in_progress'::character varying)::text, ('patched'::character varying)::text, ('up_to_date'::character varying)::text, ('failed'::character varying)::text, ('skipped'::character varying)::text, ('cancelled'::character varying)::text, ('reported'::character varying)::text, ('excluded'::character varying)::text])))
);


--
-- Name: TABLE patch_sessions; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.patch_sessions IS 'Données dynamiques de patching par campagne — 1 ligne = 1 serveur dans 1 semaine';


--
-- Name: COLUMN patch_sessions.snapshot_forced; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.patch_sessions.snapshot_forced IS 'True si patcheur a forcé le patch sans snapshot — justification obligatoire';


--
-- Name: patch_sessions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.patch_sessions_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: patch_sessions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.patch_sessions_id_seq OWNED BY public.patch_sessions.id;


--
-- Name: patch_validation; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.patch_validation (
    id integer NOT NULL,
    server_id integer,
    campaign_id integer,
    campaign_type character varying(30),
    patch_date timestamp without time zone DEFAULT now(),
    status character varying(20) DEFAULT 'en_attente'::character varying NOT NULL,
    validated_by_contact_id integer,
    validated_by_name character varying(200),
    validated_at timestamp without time zone,
    marked_by_user_id integer,
    forced_reason text,
    notes text,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    CONSTRAINT patch_validation_status_check CHECK (((status)::text = ANY (ARRAY[('en_attente'::character varying)::text, ('validated_ok'::character varying)::text, ('validated_ko'::character varying)::text, ('forced'::character varying)::text])))
);


--
-- Name: patch_validation_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.patch_validation_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: patch_validation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.patch_validation_id_seq OWNED BY public.patch_validation.id;


--
-- Name: qualys_asset_tags; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.qualys_asset_tags (
    id integer NOT NULL,
    qualys_asset_id bigint NOT NULL,
    qualys_tag_id bigint NOT NULL,
    assigned_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: qualys_asset_tags_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.qualys_asset_tags_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: qualys_asset_tags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.qualys_asset_tags_id_seq OWNED BY public.qualys_asset_tags.id;


--
-- Name: qualys_assets; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.qualys_assets (
    id integer NOT NULL,
    qualys_asset_id bigint NOT NULL,
    name character varying(255) NOT NULL,
    hostname public.citext,
    fqdn character varying(255),
    ip_address inet,
    os text,
    os_family character varying(10),
    agent_status character varying(30),
    agent_version character varying(30),
    last_checkin timestamp with time zone,
    server_id integer,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: qualys_assets_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.qualys_assets_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: qualys_assets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.qualys_assets_id_seq OWNED BY public.qualys_assets.id;


--
-- Name: qualys_missing_servers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.qualys_missing_servers (
    id integer NOT NULL,
    hostname character varying(255) NOT NULL,
    hostname_norm character varying(255) GENERATED ALWAYS AS (lower((hostname)::text)) STORED,
    environnement character varying(50),
    sources_present character varying(100),
    in_cyberark boolean DEFAULT false,
    in_sentinel boolean DEFAULT false,
    in_itop boolean DEFAULT false,
    server_id integer,
    reason_category character varying(30),
    reason_detail text,
    status character varying(20) DEFAULT 'a_traiter'::character varying,
    priority smallint DEFAULT 3,
    notes text,
    source_file character varying(100),
    last_seen_at timestamp with time zone DEFAULT now(),
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT qms_reason_check CHECK (((reason_category IS NULL) OR ((reason_category)::text = ANY ((ARRAY['appliance'::character varying, 'ot_scada'::character varying, 'virtualisation'::character varying, 'embedded'::character varying, 'oubli'::character varying, 'decom'::character varying, 'inconnu'::character varying, 'other'::character varying])::text[])))),
    CONSTRAINT qms_status_check CHECK (((status)::text = ANY ((ARRAY['a_traiter'::character varying, 'a_enroler'::character varying, 'exempt'::character varying, 'enrole'::character varying, 'decom'::character varying])::text[])))
);


--
-- Name: TABLE qualys_missing_servers; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.qualys_missing_servers IS 'Serveurs détectés ailleurs (CA/S1/iTop) mais absents de Qualys + raison';


--
-- Name: COLUMN qualys_missing_servers.reason_category; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.qualys_missing_servers.reason_category IS 'appliance, ot_scada, virtualisation (ESXi), embedded, oubli (à enrôler), decom, inconnu, other';


--
-- Name: COLUMN qualys_missing_servers.status; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.qualys_missing_servers.status IS 'a_traiter, a_enroler, exempt (légitimement hors Qualys), enrole (fait), decom';


--
-- Name: COLUMN qualys_missing_servers.priority; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.qualys_missing_servers.priority IS '1 urgent → 5 faible (auto-calc selon sources)';


--
-- Name: qualys_missing_servers_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.qualys_missing_servers_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: qualys_missing_servers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.qualys_missing_servers_id_seq OWNED BY public.qualys_missing_servers.id;


--
-- Name: qualys_tags; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.qualys_tags (
    id integer NOT NULL,
    qualys_tag_id bigint NOT NULL,
    name character varying(200) NOT NULL,
    is_dynamic boolean DEFAULT false NOT NULL,
    rule_type character varying(50),
    rule_text text,
    category character varying(50),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    v3_name character varying(100),
    v3_type character varying(10)
);


--
-- Name: qualys_tags_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.qualys_tags_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: qualys_tags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.qualys_tags_id_seq OWNED BY public.qualys_tags.id;


--
-- Name: quickwin_entries; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.quickwin_entries (
    id integer NOT NULL,
    run_id integer NOT NULL,
    server_id integer NOT NULL,
    branch character varying(10) DEFAULT 'hprod'::character varying NOT NULL,
    status character varying(20) DEFAULT 'pending'::character varying NOT NULL,
    general_excludes text DEFAULT ''::text NOT NULL,
    specific_excludes text DEFAULT ''::text NOT NULL,
    patch_command text,
    patch_output text,
    patch_packages_count integer DEFAULT 0,
    patch_packages text,
    patch_date timestamp with time zone,
    reboot_required boolean DEFAULT false NOT NULL,
    prereq_ok boolean,
    prereq_detail text,
    snap_done boolean DEFAULT false NOT NULL,
    dryrun_output text,
    notes text,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    prereq_date timestamp with time zone,
    snap_date timestamp with time zone,
    prereq_ssh boolean,
    prereq_satellite boolean,
    prereq_disk boolean,
    prod_pair_entry_id integer,
    snap_detail text,
    CONSTRAINT quickwin_entries_branch_check CHECK (((branch)::text = ANY (ARRAY[('hprod'::character varying)::text, ('prod'::character varying)::text]))),
    CONSTRAINT quickwin_entries_status_check CHECK (((status)::text = ANY (ARRAY[('pending'::character varying)::text, ('in_progress'::character varying)::text, ('patched'::character varying)::text, ('failed'::character varying)::text, ('skipped'::character varying)::text, ('excluded'::character varying)::text])))
);


--
-- Name: quickwin_entries_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.quickwin_entries_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: quickwin_entries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.quickwin_entries_id_seq OWNED BY public.quickwin_entries.id;


--
-- Name: quickwin_logs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.quickwin_logs (
    id integer NOT NULL,
    run_id integer NOT NULL,
    entry_id integer,
    hostname character varying(255),
    step character varying(30) NOT NULL,
    level character varying(10) DEFAULT 'info'::character varying NOT NULL,
    message text NOT NULL,
    detail text,
    created_at timestamp without time zone DEFAULT now(),
    created_by character varying(100)
);


--
-- Name: quickwin_logs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.quickwin_logs_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: quickwin_logs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.quickwin_logs_id_seq OWNED BY public.quickwin_logs.id;


--
-- Name: quickwin_runs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.quickwin_runs (
    id integer NOT NULL,
    year integer NOT NULL,
    week_number integer NOT NULL,
    label text DEFAULT ''::text NOT NULL,
    status character varying(20) DEFAULT 'draft'::character varying NOT NULL,
    created_by integer,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    notes text,
    CONSTRAINT quickwin_runs_status_check CHECK (((status)::text = ANY (ARRAY[('draft'::character varying)::text, ('prereq'::character varying)::text, ('snapshot'::character varying)::text, ('patching'::character varying)::text, ('result'::character varying)::text, ('completed'::character varying)::text, ('failed'::character varying)::text])))
);


--
-- Name: quickwin_runs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.quickwin_runs_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: quickwin_runs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.quickwin_runs_id_seq OWNED BY public.quickwin_runs.id;


--
-- Name: quickwin_server_config; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.quickwin_server_config (
    id integer NOT NULL,
    server_id integer NOT NULL,
    general_excludes text DEFAULT ''::text NOT NULL,
    specific_excludes text DEFAULT ''::text NOT NULL,
    last_yum_commands jsonb DEFAULT '[]'::jsonb,
    notes text,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: quickwin_server_config_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.quickwin_server_config_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: quickwin_server_config_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.quickwin_server_config_id_seq OWNED BY public.quickwin_server_config.id;


--
-- Name: server_audit; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.server_audit (
    id integer NOT NULL,
    server_id integer,
    hostname character varying(100) NOT NULL,
    audit_date timestamp with time zone NOT NULL,
    status character varying(30) NOT NULL,
    connection_method text,
    resolved_fqdn character varying(150),
    os_release text,
    kernel character varying(100),
    uptime text,
    selinux character varying(50),
    disk_detail text,
    disk_root_free_mb integer,
    disk_var_free_mb integer,
    disk_applis_pct integer,
    disk_alert boolean DEFAULT false,
    network_mounts text,
    fstab_network text,
    apps_installed text,
    services_running text,
    services_enabled text,
    running_not_enabled text,
    custom_processes text,
    pm2_processes text,
    rc_scripts text,
    applis_scripts text,
    crontab_root text,
    crontab_users text,
    containers text,
    podman_systemd text,
    listening_ports text,
    db_detected text,
    cluster_detected text,
    agents text,
    qualys_active boolean,
    sentinelone_active boolean,
    last_patch text,
    failed_services text,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: server_audit_full; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.server_audit_full (
    id integer NOT NULL,
    server_id integer,
    hostname character varying(200) NOT NULL,
    audit_date timestamp with time zone DEFAULT now(),
    os_release text,
    kernel text,
    uptime text,
    services jsonb DEFAULT '[]'::jsonb,
    processes jsonb DEFAULT '[]'::jsonb,
    services_failed text,
    needs_restarting text,
    reboot_required boolean DEFAULT false,
    disk_usage jsonb DEFAULT '[]'::jsonb,
    interfaces jsonb DEFAULT '[]'::jsonb,
    routes jsonb DEFAULT '[]'::jsonb,
    listen_ports jsonb DEFAULT '[]'::jsonb,
    connections jsonb DEFAULT '[]'::jsonb,
    flux_in jsonb DEFAULT '[]'::jsonb,
    flux_out jsonb DEFAULT '[]'::jsonb,
    conn_wait jsonb DEFAULT '[]'::jsonb,
    net_stats jsonb DEFAULT '{}'::jsonb,
    traffic jsonb DEFAULT '[]'::jsonb,
    firewall jsonb DEFAULT '{}'::jsonb,
    correlation_matrix jsonb DEFAULT '[]'::jsonb,
    outbound_only jsonb DEFAULT '[]'::jsonb,
    raw_output text,
    status character varying(20) DEFAULT 'ok'::character varying,
    error_msg text,
    created_at timestamp with time zone DEFAULT now(),
    last_patch_date character varying(20),
    last_patch_week character varying(10),
    last_patch_year integer,
    patch_count_2026 integer DEFAULT 0,
    patch_weeks_2026 text,
    patch_count_2025 integer DEFAULT 0,
    patch_weeks_2025 text,
    patch_status_2026 character varying(20) DEFAULT NULL::character varying,
    cancelled_weeks_2026 text
);


--
-- Name: server_audit_full_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.server_audit_full_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_audit_full_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.server_audit_full_id_seq OWNED BY public.server_audit_full.id;


--
-- Name: server_audit_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.server_audit_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_audit_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.server_audit_id_seq OWNED BY public.server_audit.id;


--
-- Name: server_correspondance; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.server_correspondance (
    id integer NOT NULL,
    prod_server_id integer,
    nonprod_server_id integer,
    environment_code character varying(50),
    source character varying(20) DEFAULT 'auto'::character varying NOT NULL,
    note text,
    created_by integer,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    CONSTRAINT server_correspondance_source_check CHECK (((source)::text = ANY (ARRAY[('auto'::character varying)::text, ('manual'::character varying)::text, ('exception'::character varying)::text])))
);


--
-- Name: server_correspondance_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.server_correspondance_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_correspondance_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.server_correspondance_id_seq OWNED BY public.server_correspondance.id;


--
-- Name: server_databases; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.server_databases (
    id integer NOT NULL,
    server_id integer,
    hostname public.citext,
    instance_name character varying(100),
    db_type character varying(50),
    db_version character varying(100),
    db_edition character varying(50),
    cluster_name character varying(100),
    environnement character varying(50),
    etat character varying(30),
    description text,
    created_at timestamp with time zone DEFAULT now()
);


--
-- Name: server_databases_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.server_databases_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_databases_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.server_databases_id_seq OWNED BY public.server_databases.id;


--
-- Name: server_ips; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.server_ips (
    id integer NOT NULL,
    server_id integer NOT NULL,
    ip_address inet NOT NULL,
    ip_type character varying(20) DEFAULT 'primary'::character varying NOT NULL,
    is_ssh boolean DEFAULT false NOT NULL,
    vlan_id integer,
    interface character varying(30),
    description character varying(200),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT server_ips_ip_type_check CHECK (((ip_type)::text = ANY (ARRAY[('primary'::character varying)::text, ('secondary'::character varying)::text, ('management'::character varying)::text, ('vlan'::character varying)::text])))
);


--
-- Name: server_ips_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.server_ips_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_ips_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.server_ips_id_seq OWNED BY public.server_ips.id;


--
-- Name: server_pairs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.server_pairs (
    id integer NOT NULL,
    prod_server_id integer NOT NULL,
    hprod_server_id integer NOT NULL,
    relation_type character varying(20) DEFAULT 'same_app'::character varying NOT NULL,
    auto_detected boolean DEFAULT false NOT NULL,
    note text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT server_pairs_relation_type_check CHECK (((relation_type)::text = ANY (ARRAY[('same_app'::character varying)::text, ('cluster_pair'::character varying)::text, ('dr_pair'::character varying)::text, ('manual'::character varying)::text])))
);


--
-- Name: server_pairs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.server_pairs_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_pairs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.server_pairs_id_seq OWNED BY public.server_pairs.id;


--
-- Name: server_specifics; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.server_specifics (
    id integer NOT NULL,
    server_id integer NOT NULL,
    reboot_order integer,
    reboot_order_note text,
    cmd_before_patch text,
    cmd_after_patch text,
    cmd_before_reboot text,
    cmd_after_reboot text,
    is_cluster boolean DEFAULT false NOT NULL,
    cluster_role character varying(20),
    cluster_note text,
    is_db boolean DEFAULT false NOT NULL,
    db_type character varying(50),
    db_note text,
    is_middleware boolean DEFAULT false NOT NULL,
    mw_type character varying(50),
    mw_note text,
    has_agent_special boolean DEFAULT false NOT NULL,
    agent_note text,
    has_service_critical boolean DEFAULT false NOT NULL,
    service_note text,
    needs_manual_step boolean DEFAULT false NOT NULL,
    manual_step_detail text,
    reboot_delay_minutes integer,
    patch_excludes text,
    no_reboot_reason text,
    dependency_server_id integer,
    dependency_note text,
    note text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    app_type character varying(50),
    stop_command text,
    start_command text,
    stop_user character varying(50),
    start_user character varying(50),
    kernel_update_blocked boolean DEFAULT false NOT NULL,
    kernel_block_reason text,
    reboot_min_interval_minutes integer,
    sentinel_disable_required boolean DEFAULT false NOT NULL,
    ip_forwarding_required boolean DEFAULT false NOT NULL,
    rolling_update boolean DEFAULT false NOT NULL,
    rolling_update_note text,
    auto_restart boolean DEFAULT true NOT NULL,
    patch_order_group character varying(50),
    extra_excludes text,
    stop_order integer,
    patch_wave integer,
    patch_wave_group character varying(50),
    patch_wave_delay_days integer DEFAULT 1,
    patch_wave_note text
);


--
-- Name: server_specifics_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.server_specifics_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_specifics_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.server_specifics_id_seq OWNED BY public.server_specifics.id;


--
-- Name: servers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.servers (
    id integer NOT NULL,
    hostname public.citext NOT NULL,
    fqdn character varying(255),
    domain_ltd character varying(50),
    domain_env_id integer,
    zone_id integer,
    vcenter_id integer,
    application_id integer,
    cluster_id integer,
    os_family character varying(10),
    os_version character varying(200),
    machine_type character varying(10) DEFAULT 'vm'::character varying NOT NULL,
    vcenter_vm_name character varying(100),
    is_bdd boolean DEFAULT false NOT NULL,
    is_flux_libre boolean DEFAULT false NOT NULL,
    is_emv boolean DEFAULT false NOT NULL,
    is_podman boolean DEFAULT false NOT NULL,
    need_pct boolean DEFAULT false NOT NULL,
    tier character varying(10) DEFAULT 'a_definir'::character varying NOT NULL,
    etat character varying(30) DEFAULT 'production'::character varying,
    licence_support character varying(10) DEFAULT 'active'::character varying NOT NULL,
    eol_date date,
    site character varying(50),
    ssh_method character varying(20) DEFAULT 'ssh_key'::character varying,
    ssh_port integer DEFAULT 22 NOT NULL,
    ssh_user character varying(50),
    cyberark_safe character varying(100),
    patch_os_owner character varying(20) DEFAULT 'secops'::character varying NOT NULL,
    patch_mw_owner character varying(20) DEFAULT 'a_definir'::character varying NOT NULL,
    patch_app_owner character varying(20) DEFAULT 'a_definir'::character varying NOT NULL,
    patch_owner_details text,
    patch_frequency character varying(20) DEFAULT 'monthly'::character varying,
    patch_window character varying(100),
    patch_excludes text,
    patch_custom_cmd text,
    reboot_allowed boolean DEFAULT true NOT NULL,
    snapshot_required boolean DEFAULT true NOT NULL,
    pre_patch_script text,
    post_patch_script text,
    responsable_nom text,
    responsable_email character varying(255),
    referent_nom text,
    referent_email character varying(255),
    default_intervenant_id integer,
    qualys_asset_id bigint,
    centreon_host_id integer,
    cve_critical_count integer DEFAULT 0,
    last_qualys_scan timestamp with time zone,
    mode_operatoire text,
    commentaire text,
    notes text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    created_by integer,
    satellite_host character varying(100),
    pref_patch_jour character varying(20) DEFAULT 'indifferent'::character varying,
    pref_patch_heure character varying(50) DEFAULT 'indifferent'::character varying,
    application_name character varying(200),
    app_group character varying(50),
    use_proxy boolean DEFAULT true NOT NULL,
    environnement character varying(20),
    domaine character varying(100),
    CONSTRAINT servers_environnement_check CHECK ((((environnement)::text = ANY ((ARRAY['Développement'::character varying, 'Intégration'::character varying, 'Pré-Prod'::character varying, 'Production'::character varying, 'Recette'::character varying, 'Test'::character varying, 'Formation'::character varying])::text[])) OR (environnement IS NULL))),
    CONSTRAINT servers_etat_check CHECK ((((etat)::text = ANY ((ARRAY['Production'::character varying, 'Implémentation'::character varying, 'Stock'::character varying, 'Obsolète'::character varying, 'prêt'::character varying, 'tests'::character varying])::text[])) OR (etat IS NULL))),
    CONSTRAINT servers_licence_support_check CHECK ((((licence_support)::text = ANY ((ARRAY['active'::character varying, 'obsolete'::character varying, 'els'::character varying, 'missing'::character varying])::text[])) OR (licence_support IS NULL))),
    CONSTRAINT servers_machine_type_check CHECK (((machine_type)::text = ANY (ARRAY[('vm'::character varying)::text, ('physical'::character varying)::text]))),
    CONSTRAINT servers_os_family_check CHECK (((os_family)::text = ANY (ARRAY[('linux'::character varying)::text, ('windows'::character varying)::text]))),
    CONSTRAINT servers_patch_app_owner_check CHECK (((patch_app_owner)::text = ANY (ARRAY[('secops'::character varying)::text, ('ipop'::character varying)::text, ('editeur'::character varying)::text, ('tiers'::character varying)::text, ('na'::character varying)::text, ('a_definir'::character varying)::text]))),
    CONSTRAINT servers_patch_mw_owner_check CHECK (((patch_mw_owner)::text = ANY (ARRAY[('secops'::character varying)::text, ('ipop'::character varying)::text, ('editeur'::character varying)::text, ('tiers'::character varying)::text, ('na'::character varying)::text, ('a_definir'::character varying)::text]))),
    CONSTRAINT servers_patch_os_owner_check CHECK (((patch_os_owner)::text = ANY (ARRAY[('secops'::character varying)::text, ('ipop'::character varying)::text, ('editeur'::character varying)::text, ('tiers'::character varying)::text, ('na'::character varying)::text, ('a_definir'::character varying)::text]))),
    CONSTRAINT servers_ssh_method_check CHECK ((((ssh_method)::text = ANY ((ARRAY['ssh_key'::character varying, 'ssh_psmp'::character varying, 'ssh_password'::character varying, 'rdp_local'::character varying, 'rdp_psmp'::character varying, 'winrm'::character varying, 'a_definir'::character varying])::text[])) OR (ssh_method IS NULL))),
    CONSTRAINT servers_tier_check CHECK (((tier)::text = ANY (ARRAY[('tier0'::character varying)::text, ('tier1'::character varying)::text, ('tier2'::character varying)::text, ('tier3'::character varying)::text, ('a_definir'::character varying)::text])))
);


--
-- Name: TABLE servers; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.servers IS 'Inventaire serveurs SANEF — source de vérité unique';


--
-- Name: COLUMN servers.is_podman; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.servers.is_podman IS 'True si Flux Libre avec Podman (bot/boo/boc) — False pour BST';


--
-- Name: COLUMN servers.need_pct; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.servers.need_pct IS 'True si prévenance PCT nécessaire avant intervention';


--
-- Name: servers_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.servers_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: servers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.servers_id_seq OWNED BY public.servers.id;


--
-- Name: settings; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.settings (
    key character varying(100) NOT NULL,
    value text NOT NULL,
    description text,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_by integer
);


--
-- Name: tier_policies; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.tier_policies (
    id integer NOT NULL,
    tier character varying(10) NOT NULL,
    max_parallel integer DEFAULT 1 NOT NULL,
    approval_level character varying(20) NOT NULL,
    maintenance_window character varying(100),
    pre_notification_hours integer DEFAULT 0 NOT NULL,
    snapshot_mandatory boolean DEFAULT true NOT NULL,
    post_check_level character varying(20) DEFAULT 'basic'::character varying NOT NULL,
    notes text
);


--
-- Name: tier_policies_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.tier_policies_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: tier_policies_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.tier_policies_id_seq OWNED BY public.tier_policies.id;


--
-- Name: user_permissions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.user_permissions (
    id integer NOT NULL,
    user_id integer NOT NULL,
    module character varying(30) NOT NULL,
    level character varying(10) DEFAULT 'view'::character varying NOT NULL,
    CONSTRAINT user_permissions_level_check CHECK (((level)::text = ANY (ARRAY[('view'::character varying)::text, ('edit'::character varying)::text, ('admin'::character varying)::text]))),
    CONSTRAINT user_permissions_module_check CHECK (((module)::text = ANY (ARRAY[('servers'::character varying)::text, ('campaigns'::character varying)::text, ('qualys'::character varying)::text, ('audit'::character varying)::text, ('settings'::character varying)::text, ('users'::character varying)::text, ('planning'::character varying)::text, ('specifics'::character varying)::text, ('quickwin'::character varying)::text])))
);


--
-- Name: user_permissions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.user_permissions_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: user_permissions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.user_permissions_id_seq OWNED BY public.user_permissions.id;


--
-- Name: users; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.users (
    id integer NOT NULL,
    username public.citext NOT NULL,
    display_name character varying(100) NOT NULL,
    email character varying(255),
    password_hash character varying(255),
    role character varying(20) DEFAULT 'operator'::character varying NOT NULL,
    auth_type character varying(10) DEFAULT 'local'::character varying NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    last_login timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    itop_person_id integer,
    last_itop_sync timestamp without time zone,
    force_password_change boolean DEFAULT false,
    CONSTRAINT users_auth_type_check CHECK (((auth_type)::text = ANY (ARRAY[('local'::character varying)::text, ('ldap'::character varying)::text]))),
    CONSTRAINT users_role_check CHECK (((role)::text = ANY (ARRAY[('admin'::character varying)::text, ('coordinator'::character varying)::text, ('operator'::character varying)::text, ('viewer'::character varying)::text])))
);


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.users_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;


--
-- Name: v_campaign_dashboard; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_campaign_dashboard AS
 SELECT c.id,
    c.week_code,
    c.year,
    c.status,
    count(ps.id) AS total,
    count(*) FILTER (WHERE ((ps.status)::text = 'patched'::text)) AS patched,
    count(*) FILTER (WHERE ((ps.status)::text = 'failed'::text)) AS failed,
    count(*) FILTER (WHERE ((ps.status)::text = 'pending'::text)) AS pending
   FROM (public.campaigns c
     LEFT JOIN public.patch_sessions ps ON ((c.id = ps.campaign_id)))
  GROUP BY c.id, c.week_code, c.year, c.status;


--
-- Name: v_conformity_todo; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_conformity_todo AS
 SELECT s.hostname,
    d.name AS domaine,
    e.name AS environnement,
    s.tier,
    s.patch_os_owner,
    (s.responsable_nom IS NULL) AS resp_missing
   FROM (((public.servers s
     LEFT JOIN public.domain_environments de ON ((s.domain_env_id = de.id)))
     LEFT JOIN public.domains d ON ((de.domain_id = d.id)))
     LEFT JOIN public.environments e ON ((de.environment_id = e.id)))
  WHERE (((s.etat)::text = 'en_production'::text) AND (((s.patch_os_owner)::text = 'a_definir'::text) OR (s.responsable_nom IS NULL)));


--
-- Name: zones; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.zones (
    id integer NOT NULL,
    name character varying(30) NOT NULL,
    description text,
    is_dmz boolean DEFAULT false NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: v_servers; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_servers AS
 SELECT s.id,
    s.hostname,
    s.fqdn,
    s.domain_ltd,
    d.name AS domaine,
    d.code AS domaine_code,
    e.name AS environnement,
    e.code AS env_code,
    z.name AS zone_reseau,
    s.os_family,
    s.os_version,
    s.machine_type,
    s.tier,
    s.etat,
    s.licence_support,
    s.is_bdd,
    s.is_flux_libre,
    s.is_emv,
    s.is_podman,
    s.need_pct,
    s.patch_os_owner,
    COALESCE(s.patch_excludes, de.patch_excludes, d.default_excludes) AS effective_excludes,
    s.responsable_nom,
    s.referent_nom,
    s.ssh_method,
    s.ssh_user,
    s.mode_operatoire,
    s.commentaire
   FROM ((((public.servers s
     LEFT JOIN public.domain_environments de ON ((s.domain_env_id = de.id)))
     LEFT JOIN public.domains d ON ((de.domain_id = d.id)))
     LEFT JOIN public.environments e ON ((de.environment_id = e.id)))
     LEFT JOIN public.zones z ON ((s.zone_id = z.id)));


--
-- Name: v_patchable; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.v_patchable AS
 SELECT id,
    hostname,
    fqdn,
    domain_ltd,
    domaine,
    domaine_code,
    environnement,
    env_code,
    zone_reseau,
    os_family,
    os_version,
    machine_type,
    tier,
    etat,
    licence_support,
    is_bdd,
    is_flux_libre,
    is_emv,
    is_podman,
    need_pct,
    patch_os_owner,
    effective_excludes,
    responsable_nom,
    referent_nom,
    ssh_method,
    ssh_user,
    mode_operatoire,
    commentaire
   FROM public.v_servers
  WHERE (((patch_os_owner)::text = 'secops'::text) AND ((etat)::text = 'en_production'::text) AND ((licence_support)::text = ANY (ARRAY[('active'::character varying)::text, ('els'::character varying)::text])));


--
-- Name: vcenters; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.vcenters (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    endpoint character varying(255) NOT NULL,
    datacenter character varying(100),
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    description character varying(255),
    responsable character varying(100)
);


--
-- Name: vcenters_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.vcenters_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: vcenters_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.vcenters_id_seq OWNED BY public.vcenters.id;


--
-- Name: zones_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.zones_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: zones_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.zones_id_seq OWNED BY public.zones.id;


--
-- Name: allowed_networks id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.allowed_networks ALTER COLUMN id SET DEFAULT nextval('public.allowed_networks_id_seq'::regclass);


--
-- Name: app_map id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.app_map ALTER COLUMN id SET DEFAULT nextval('public.app_map_id_seq'::regclass);


--
-- Name: applications id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.applications ALTER COLUMN id SET DEFAULT nextval('public.applications_id_seq'::regclass);


--
-- Name: audit_log id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.audit_log ALTER COLUMN id SET DEFAULT nextval('public.audit_log_id_seq'::regclass);


--
-- Name: campaign_accord_details id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accord_details ALTER COLUMN id SET DEFAULT nextval('public.campaign_accord_details_id_seq'::regclass);


--
-- Name: campaign_accords id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accords ALTER COLUMN id SET DEFAULT nextval('public.campaign_accords_id_seq'::regclass);


--
-- Name: campaign_operator_limits id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_operator_limits ALTER COLUMN id SET DEFAULT nextval('public.campaign_operator_limits_id_seq'::regclass);


--
-- Name: campaigns id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaigns ALTER COLUMN id SET DEFAULT nextval('public.campaigns_id_seq'::regclass);


--
-- Name: chassis id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.chassis ALTER COLUMN id SET DEFAULT nextval('public.chassis_id_seq'::regclass);


--
-- Name: cluster_members id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.cluster_members ALTER COLUMN id SET DEFAULT nextval('public.cluster_members_id_seq'::regclass);


--
-- Name: clusters id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.clusters ALTER COLUMN id SET DEFAULT nextval('public.clusters_id_seq'::regclass);


--
-- Name: contact_scopes id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contact_scopes ALTER COLUMN id SET DEFAULT nextval('public.contact_scopes_id_seq'::regclass);


--
-- Name: contacts id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contacts ALTER COLUMN id SET DEFAULT nextval('public.contacts_id_seq'::regclass);


--
-- Name: default_assignments id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.default_assignments ALTER COLUMN id SET DEFAULT nextval('public.default_assignments_id_seq'::regclass);


--
-- Name: domain_environments id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_environments ALTER COLUMN id SET DEFAULT nextval('public.domain_environments_id_seq'::regclass);


--
-- Name: domain_ltd_list id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_ltd_list ALTER COLUMN id SET DEFAULT nextval('public.domain_ltd_list_id_seq'::regclass);


--
-- Name: domain_responsables id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_responsables ALTER COLUMN id SET DEFAULT nextval('public.domain_responsables_id_seq'::regclass);


--
-- Name: domains id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domains ALTER COLUMN id SET DEFAULT nextval('public.domains_id_seq'::regclass);


--
-- Name: environments id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.environments ALTER COLUMN id SET DEFAULT nextval('public.environments_id_seq'::regclass);


--
-- Name: eol_references id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.eol_references ALTER COLUMN id SET DEFAULT nextval('public.eol_references_id_seq'::regclass);


--
-- Name: hypervisors id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.hypervisors ALTER COLUMN id SET DEFAULT nextval('public.hypervisors_id_seq'::regclass);


--
-- Name: import_history id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.import_history ALTER COLUMN id SET DEFAULT nextval('public.import_history_id_seq'::regclass);


--
-- Name: network_flow_map id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.network_flow_map ALTER COLUMN id SET DEFAULT nextval('public.network_flow_map_id_seq'::regclass);


--
-- Name: patch_history id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_history ALTER COLUMN id SET DEFAULT nextval('public.patch_history_id_seq'::regclass);


--
-- Name: patch_planning id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_planning ALTER COLUMN id SET DEFAULT nextval('public.patch_planning_id_seq'::regclass);


--
-- Name: patch_sessions id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_sessions ALTER COLUMN id SET DEFAULT nextval('public.patch_sessions_id_seq'::regclass);


--
-- Name: patch_validation id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_validation ALTER COLUMN id SET DEFAULT nextval('public.patch_validation_id_seq'::regclass);


--
-- Name: qualys_asset_tags id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_asset_tags ALTER COLUMN id SET DEFAULT nextval('public.qualys_asset_tags_id_seq'::regclass);


--
-- Name: qualys_assets id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_assets ALTER COLUMN id SET DEFAULT nextval('public.qualys_assets_id_seq'::regclass);


--
-- Name: qualys_missing_servers id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_missing_servers ALTER COLUMN id SET DEFAULT nextval('public.qualys_missing_servers_id_seq'::regclass);


--
-- Name: qualys_tags id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_tags ALTER COLUMN id SET DEFAULT nextval('public.qualys_tags_id_seq'::regclass);


--
-- Name: quickwin_entries id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_entries ALTER COLUMN id SET DEFAULT nextval('public.quickwin_entries_id_seq'::regclass);


--
-- Name: quickwin_logs id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_logs ALTER COLUMN id SET DEFAULT nextval('public.quickwin_logs_id_seq'::regclass);


--
-- Name: quickwin_runs id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_runs ALTER COLUMN id SET DEFAULT nextval('public.quickwin_runs_id_seq'::regclass);


--
-- Name: quickwin_server_config id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_server_config ALTER COLUMN id SET DEFAULT nextval('public.quickwin_server_config_id_seq'::regclass);


--
-- Name: server_audit id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_audit ALTER COLUMN id SET DEFAULT nextval('public.server_audit_id_seq'::regclass);


--
-- Name: server_audit_full id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_audit_full ALTER COLUMN id SET DEFAULT nextval('public.server_audit_full_id_seq'::regclass);


--
-- Name: server_correspondance id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_correspondance ALTER COLUMN id SET DEFAULT nextval('public.server_correspondance_id_seq'::regclass);


--
-- Name: server_databases id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_databases ALTER COLUMN id SET DEFAULT nextval('public.server_databases_id_seq'::regclass);


--
-- Name: server_ips id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_ips ALTER COLUMN id SET DEFAULT nextval('public.server_ips_id_seq'::regclass);


--
-- Name: server_pairs id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_pairs ALTER COLUMN id SET DEFAULT nextval('public.server_pairs_id_seq'::regclass);


--
-- Name: server_specifics id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_specifics ALTER COLUMN id SET DEFAULT nextval('public.server_specifics_id_seq'::regclass);


--
-- Name: servers id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers ALTER COLUMN id SET DEFAULT nextval('public.servers_id_seq'::regclass);


--
-- Name: tier_policies id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tier_policies ALTER COLUMN id SET DEFAULT nextval('public.tier_policies_id_seq'::regclass);


--
-- Name: user_permissions id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_permissions ALTER COLUMN id SET DEFAULT nextval('public.user_permissions_id_seq'::regclass);


--
-- Name: users id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);


--
-- Name: vcenters id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.vcenters ALTER COLUMN id SET DEFAULT nextval('public.vcenters_id_seq'::regclass);


--
-- Name: zones id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.zones ALTER COLUMN id SET DEFAULT nextval('public.zones_id_seq'::regclass);


--
-- Name: allowed_networks allowed_networks_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.allowed_networks
    ADD CONSTRAINT allowed_networks_pkey PRIMARY KEY (id);


--
-- Name: app_map app_map_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.app_map
    ADD CONSTRAINT app_map_pkey PRIMARY KEY (id);


--
-- Name: app_secrets app_secrets_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.app_secrets
    ADD CONSTRAINT app_secrets_pkey PRIMARY KEY (key);


--
-- Name: applications applications_itop_id_uniq; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.applications
    ADD CONSTRAINT applications_itop_id_uniq UNIQUE (itop_id);


--
-- Name: applications applications_nom_court_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.applications
    ADD CONSTRAINT applications_nom_court_key UNIQUE (nom_court);


--
-- Name: applications applications_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.applications
    ADD CONSTRAINT applications_pkey PRIMARY KEY (id);


--
-- Name: audit_log audit_log_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.audit_log
    ADD CONSTRAINT audit_log_pkey PRIMARY KEY (id);


--
-- Name: campaign_accord_details campaign_accord_details_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accord_details
    ADD CONSTRAINT campaign_accord_details_pkey PRIMARY KEY (id);


--
-- Name: campaign_accords campaign_accords_campaign_id_contact_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accords
    ADD CONSTRAINT campaign_accords_campaign_id_contact_id_key UNIQUE (campaign_id, contact_id);


--
-- Name: campaign_accords campaign_accords_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accords
    ADD CONSTRAINT campaign_accords_pkey PRIMARY KEY (id);


--
-- Name: campaign_operator_limits campaign_operator_limits_campaign_id_user_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_operator_limits
    ADD CONSTRAINT campaign_operator_limits_campaign_id_user_id_key UNIQUE (campaign_id, user_id);


--
-- Name: campaign_operator_limits campaign_operator_limits_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_operator_limits
    ADD CONSTRAINT campaign_operator_limits_pkey PRIMARY KEY (id);


--
-- Name: campaigns campaigns_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaigns
    ADD CONSTRAINT campaigns_pkey PRIMARY KEY (id);


--
-- Name: campaigns campaigns_week_code_year_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaigns
    ADD CONSTRAINT campaigns_week_code_year_key UNIQUE (week_code, year);


--
-- Name: chassis chassis_hostname_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.chassis
    ADD CONSTRAINT chassis_hostname_key UNIQUE (hostname);


--
-- Name: chassis chassis_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.chassis
    ADD CONSTRAINT chassis_pkey PRIMARY KEY (id);


--
-- Name: cluster_members cluster_members_cluster_id_patch_order_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.cluster_members
    ADD CONSTRAINT cluster_members_cluster_id_patch_order_key UNIQUE (cluster_id, patch_order);


--
-- Name: cluster_members cluster_members_cluster_id_server_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.cluster_members
    ADD CONSTRAINT cluster_members_cluster_id_server_id_key UNIQUE (cluster_id, server_id);


--
-- Name: cluster_members cluster_members_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.cluster_members
    ADD CONSTRAINT cluster_members_pkey PRIMARY KEY (id);


--
-- Name: clusters clusters_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.clusters
    ADD CONSTRAINT clusters_name_key UNIQUE (name);


--
-- Name: clusters clusters_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.clusters
    ADD CONSTRAINT clusters_pkey PRIMARY KEY (id);


--
-- Name: contact_scopes contact_scopes_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contact_scopes
    ADD CONSTRAINT contact_scopes_pkey PRIMARY KEY (id);


--
-- Name: contact_scopes contact_scopes_unique; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contact_scopes
    ADD CONSTRAINT contact_scopes_unique UNIQUE (contact_id, scope_type, scope_value, env_scope);


--
-- Name: contacts contacts_email_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contacts
    ADD CONSTRAINT contacts_email_key UNIQUE (email);


--
-- Name: contacts contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contacts
    ADD CONSTRAINT contacts_pkey PRIMARY KEY (id);


--
-- Name: contacts contacts_portal_token_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contacts
    ADD CONSTRAINT contacts_portal_token_key UNIQUE (portal_token);


--
-- Name: default_assignments default_assignments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.default_assignments
    ADD CONSTRAINT default_assignments_pkey PRIMARY KEY (id);


--
-- Name: default_assignments default_assignments_rule_type_rule_value_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.default_assignments
    ADD CONSTRAINT default_assignments_rule_type_rule_value_key UNIQUE (rule_type, rule_value);


--
-- Name: domain_environments domain_environments_domain_id_environment_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_environments
    ADD CONSTRAINT domain_environments_domain_id_environment_id_key UNIQUE (domain_id, environment_id);


--
-- Name: domain_environments domain_environments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_environments
    ADD CONSTRAINT domain_environments_pkey PRIMARY KEY (id);


--
-- Name: domain_ltd_list domain_ltd_list_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_ltd_list
    ADD CONSTRAINT domain_ltd_list_name_key UNIQUE (name);


--
-- Name: domain_ltd_list domain_ltd_list_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_ltd_list
    ADD CONSTRAINT domain_ltd_list_pkey PRIMARY KEY (id);


--
-- Name: domain_responsables domain_responsables_domain_id_nom_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_responsables
    ADD CONSTRAINT domain_responsables_domain_id_nom_key UNIQUE (domain_id, nom);


--
-- Name: domain_responsables domain_responsables_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_responsables
    ADD CONSTRAINT domain_responsables_pkey PRIMARY KEY (id);


--
-- Name: domains domains_code_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domains
    ADD CONSTRAINT domains_code_key UNIQUE (code);


--
-- Name: domains domains_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domains
    ADD CONSTRAINT domains_name_key UNIQUE (name);


--
-- Name: domains domains_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domains
    ADD CONSTRAINT domains_pkey PRIMARY KEY (id);


--
-- Name: environments environments_code_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.environments
    ADD CONSTRAINT environments_code_key UNIQUE (code);


--
-- Name: environments environments_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.environments
    ADD CONSTRAINT environments_name_key UNIQUE (name);


--
-- Name: environments environments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.environments
    ADD CONSTRAINT environments_pkey PRIMARY KEY (id);


--
-- Name: eol_references eol_references_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.eol_references
    ADD CONSTRAINT eol_references_pkey PRIMARY KEY (id);


--
-- Name: hypervisors hypervisors_hostname_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.hypervisors
    ADD CONSTRAINT hypervisors_hostname_key UNIQUE (hostname);


--
-- Name: hypervisors hypervisors_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.hypervisors
    ADD CONSTRAINT hypervisors_pkey PRIMARY KEY (id);


--
-- Name: import_history import_history_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.import_history
    ADD CONSTRAINT import_history_pkey PRIMARY KEY (id);


--
-- Name: network_flow_map network_flow_map_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.network_flow_map
    ADD CONSTRAINT network_flow_map_pkey PRIMARY KEY (id);


--
-- Name: patch_history patch_history_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_history
    ADD CONSTRAINT patch_history_pkey PRIMARY KEY (id);


--
-- Name: patch_planning patch_planning_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_planning
    ADD CONSTRAINT patch_planning_pkey PRIMARY KEY (id);


--
-- Name: patch_sessions patch_sessions_campaign_id_server_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_sessions
    ADD CONSTRAINT patch_sessions_campaign_id_server_id_key UNIQUE (campaign_id, server_id);


--
-- Name: patch_sessions patch_sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_sessions
    ADD CONSTRAINT patch_sessions_pkey PRIMARY KEY (id);


--
-- Name: patch_validation patch_validation_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_validation
    ADD CONSTRAINT patch_validation_pkey PRIMARY KEY (id);


--
-- Name: qualys_asset_tags qualys_asset_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_asset_tags
    ADD CONSTRAINT qualys_asset_tags_pkey PRIMARY KEY (id);


--
-- Name: qualys_asset_tags qualys_asset_tags_qualys_asset_id_qualys_tag_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_asset_tags
    ADD CONSTRAINT qualys_asset_tags_qualys_asset_id_qualys_tag_id_key UNIQUE (qualys_asset_id, qualys_tag_id);


--
-- Name: qualys_assets qualys_assets_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_assets
    ADD CONSTRAINT qualys_assets_pkey PRIMARY KEY (id);


--
-- Name: qualys_assets qualys_assets_qualys_asset_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_assets
    ADD CONSTRAINT qualys_assets_qualys_asset_id_key UNIQUE (qualys_asset_id);


--
-- Name: qualys_missing_servers qualys_missing_servers_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_missing_servers
    ADD CONSTRAINT qualys_missing_servers_pkey PRIMARY KEY (id);


--
-- Name: qualys_tags qualys_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_tags
    ADD CONSTRAINT qualys_tags_pkey PRIMARY KEY (id);


--
-- Name: qualys_tags qualys_tags_qualys_tag_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_tags
    ADD CONSTRAINT qualys_tags_qualys_tag_id_key UNIQUE (qualys_tag_id);


--
-- Name: quickwin_entries quickwin_entries_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_entries
    ADD CONSTRAINT quickwin_entries_pkey PRIMARY KEY (id);


--
-- Name: quickwin_logs quickwin_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_logs
    ADD CONSTRAINT quickwin_logs_pkey PRIMARY KEY (id);


--
-- Name: quickwin_runs quickwin_runs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_runs
    ADD CONSTRAINT quickwin_runs_pkey PRIMARY KEY (id);


--
-- Name: quickwin_server_config quickwin_server_config_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_server_config
    ADD CONSTRAINT quickwin_server_config_pkey PRIMARY KEY (id);


--
-- Name: quickwin_server_config quickwin_server_config_server_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_server_config
    ADD CONSTRAINT quickwin_server_config_server_id_key UNIQUE (server_id);


--
-- Name: server_audit_full server_audit_full_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_audit_full
    ADD CONSTRAINT server_audit_full_pkey PRIMARY KEY (id);


--
-- Name: server_audit server_audit_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_audit
    ADD CONSTRAINT server_audit_pkey PRIMARY KEY (id);


--
-- Name: server_audit server_audit_server_id_audit_date_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_audit
    ADD CONSTRAINT server_audit_server_id_audit_date_key UNIQUE (server_id, audit_date);


--
-- Name: server_correspondance server_correspondance_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_correspondance
    ADD CONSTRAINT server_correspondance_pkey PRIMARY KEY (id);


--
-- Name: server_correspondance server_correspondance_uniq; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_correspondance
    ADD CONSTRAINT server_correspondance_uniq UNIQUE (prod_server_id, nonprod_server_id);


--
-- Name: server_databases server_databases_hostname_instance_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_databases
    ADD CONSTRAINT server_databases_hostname_instance_name_key UNIQUE (hostname, instance_name);


--
-- Name: server_databases server_databases_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_databases
    ADD CONSTRAINT server_databases_pkey PRIMARY KEY (id);


--
-- Name: server_ips server_ips_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_ips
    ADD CONSTRAINT server_ips_pkey PRIMARY KEY (id);


--
-- Name: server_ips server_ips_server_id_ip_address_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_ips
    ADD CONSTRAINT server_ips_server_id_ip_address_key UNIQUE (server_id, ip_address);


--
-- Name: server_pairs server_pairs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_pairs
    ADD CONSTRAINT server_pairs_pkey PRIMARY KEY (id);


--
-- Name: server_pairs server_pairs_prod_server_id_hprod_server_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_pairs
    ADD CONSTRAINT server_pairs_prod_server_id_hprod_server_id_key UNIQUE (prod_server_id, hprod_server_id);


--
-- Name: server_specifics server_specifics_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_specifics
    ADD CONSTRAINT server_specifics_pkey PRIMARY KEY (id);


--
-- Name: server_specifics server_specifics_server_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_specifics
    ADD CONSTRAINT server_specifics_server_id_key UNIQUE (server_id);


--
-- Name: servers servers_hostname_env_unique; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_hostname_env_unique UNIQUE (hostname, domain_env_id);


--
-- Name: servers servers_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_pkey PRIMARY KEY (id);


--
-- Name: settings settings_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.settings
    ADD CONSTRAINT settings_pkey PRIMARY KEY (key);


--
-- Name: tier_policies tier_policies_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tier_policies
    ADD CONSTRAINT tier_policies_pkey PRIMARY KEY (id);


--
-- Name: tier_policies tier_policies_tier_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.tier_policies
    ADD CONSTRAINT tier_policies_tier_key UNIQUE (tier);


--
-- Name: user_permissions user_permissions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_permissions
    ADD CONSTRAINT user_permissions_pkey PRIMARY KEY (id);


--
-- Name: user_permissions user_permissions_user_id_module_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_permissions
    ADD CONSTRAINT user_permissions_user_id_module_key UNIQUE (user_id, module);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: users users_username_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_username_key UNIQUE (username);


--
-- Name: vcenters vcenters_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.vcenters
    ADD CONSTRAINT vcenters_pkey PRIMARY KEY (id);


--
-- Name: zones zones_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.zones
    ADD CONSTRAINT zones_name_key UNIQUE (name);


--
-- Name: zones zones_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.zones
    ADD CONSTRAINT zones_pkey PRIMARY KEY (id);


--
-- Name: applications_ioda_libelle_uniq; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX applications_ioda_libelle_uniq ON public.applications USING btree (ioda_libelle) WHERE (ioda_libelle IS NOT NULL);


--
-- Name: idx_accord_campaign; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_accord_campaign ON public.campaign_accords USING btree (campaign_id);


--
-- Name: idx_accord_contact; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_accord_contact ON public.campaign_accords USING btree (contact_id);


--
-- Name: idx_app_map_name; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX idx_app_map_name ON public.app_map USING btree (app_name);


--
-- Name: idx_app_nom; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_app_nom ON public.applications USING btree (nom_court);


--
-- Name: idx_audit_action; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_action ON public.audit_log USING btree (action);


--
-- Name: idx_audit_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_date ON public.server_audit USING btree (audit_date DESC);


--
-- Name: idx_audit_details; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_details ON public.audit_log USING gin (details);


--
-- Name: idx_audit_entity; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_entity ON public.audit_log USING btree (entity_type, entity_id);


--
-- Name: idx_audit_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_server ON public.server_audit USING btree (server_id);


--
-- Name: idx_audit_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_status ON public.server_audit USING btree (status);


--
-- Name: idx_audit_ts; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_ts ON public.audit_log USING btree ("timestamp" DESC);


--
-- Name: idx_audit_user; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_audit_user ON public.audit_log USING btree (user_id);


--
-- Name: idx_campaign_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_campaign_status ON public.campaigns USING btree (status);


--
-- Name: idx_campaign_week; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_campaign_week ON public.campaigns USING btree (week_code, year);


--
-- Name: idx_clm_cluster; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_clm_cluster ON public.cluster_members USING btree (cluster_id);


--
-- Name: idx_clm_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_clm_server ON public.cluster_members USING btree (server_id);


--
-- Name: idx_contacts_email; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_contacts_email ON public.contacts USING btree (email);


--
-- Name: idx_contacts_token; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_contacts_token ON public.contacts USING btree (portal_token);


--
-- Name: idx_corr_nonprod; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_corr_nonprod ON public.server_correspondance USING btree (nonprod_server_id);


--
-- Name: idx_corr_prod; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_corr_prod ON public.server_correspondance USING btree (prod_server_id);


--
-- Name: idx_cscope_contact; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_cscope_contact ON public.contact_scopes USING btree (contact_id);


--
-- Name: idx_cscope_type; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_cscope_type ON public.contact_scopes USING btree (scope_type, scope_value);


--
-- Name: idx_da_user; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_da_user ON public.default_assignments USING btree (user_id);


--
-- Name: idx_domain_resp_domain; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_domain_resp_domain ON public.domain_responsables USING btree (domain_id);


--
-- Name: idx_domenv_domain; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_domenv_domain ON public.domain_environments USING btree (domain_id);


--
-- Name: idx_domenv_env; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_domenv_env ON public.domain_environments USING btree (environment_id);


--
-- Name: idx_nfm_audit; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_nfm_audit ON public.network_flow_map USING btree (audit_id);


--
-- Name: idx_nfm_dest; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_nfm_dest ON public.network_flow_map USING btree (dest_hostname);


--
-- Name: idx_nfm_source; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_nfm_source ON public.network_flow_map USING btree (source_hostname);


--
-- Name: idx_ph_campaign; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ph_campaign ON public.patch_history USING btree (campaign_id);


--
-- Name: idx_ph_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ph_date ON public.patch_history USING btree (date_patch DESC);


--
-- Name: idx_ph_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ph_server ON public.patch_history USING btree (server_id);


--
-- Name: idx_pp_year_week; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_pp_year_week ON public.patch_planning USING btree (year, week_number);


--
-- Name: idx_ps_campaign; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ps_campaign ON public.patch_sessions USING btree (campaign_id);


--
-- Name: idx_ps_date_prevue; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ps_date_prevue ON public.patch_sessions USING btree (date_prevue);


--
-- Name: idx_ps_date_realise; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ps_date_realise ON public.patch_sessions USING btree (date_realise);


--
-- Name: idx_ps_intervenant; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ps_intervenant ON public.patch_sessions USING btree (intervenant_id);


--
-- Name: idx_ps_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ps_server ON public.patch_sessions USING btree (server_id);


--
-- Name: idx_ps_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_ps_status ON public.patch_sessions USING btree (status);


--
-- Name: idx_pv_campaign; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_pv_campaign ON public.patch_validation USING btree (campaign_id, campaign_type);


--
-- Name: idx_pv_patch_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_pv_patch_date ON public.patch_validation USING btree (patch_date DESC);


--
-- Name: idx_pv_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_pv_server ON public.patch_validation USING btree (server_id);


--
-- Name: idx_pv_status; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_pv_status ON public.patch_validation USING btree (status);


--
-- Name: idx_qasset_hostname; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qasset_hostname ON public.qualys_assets USING btree (hostname);


--
-- Name: idx_qasset_qualys_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qasset_qualys_id ON public.qualys_assets USING btree (qualys_asset_id);


--
-- Name: idx_qasset_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qasset_server ON public.qualys_assets USING btree (server_id);


--
-- Name: idx_qat_asset; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qat_asset ON public.qualys_asset_tags USING btree (qualys_asset_id);


--
-- Name: idx_qat_tag; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qat_tag ON public.qualys_asset_tags USING btree (qualys_tag_id);


--
-- Name: idx_qe_prod_pair; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qe_prod_pair ON public.quickwin_entries USING btree (prod_pair_entry_id) WHERE (prod_pair_entry_id IS NOT NULL);


--
-- Name: idx_qtag_name; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qtag_name ON public.qualys_tags USING btree (name);


--
-- Name: idx_qtag_qualys_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qtag_qualys_id ON public.qualys_tags USING btree (qualys_tag_id);


--
-- Name: idx_qw_logs_level; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qw_logs_level ON public.quickwin_logs USING btree (run_id, level);


--
-- Name: idx_qw_logs_run; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qw_logs_run ON public.quickwin_logs USING btree (run_id, created_at DESC);


--
-- Name: idx_qwe_branch; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qwe_branch ON public.quickwin_entries USING btree (branch);


--
-- Name: idx_qwe_run; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qwe_run ON public.quickwin_entries USING btree (run_id);


--
-- Name: idx_qwe_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qwe_server ON public.quickwin_entries USING btree (server_id);


--
-- Name: idx_qwsc_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_qwsc_server ON public.quickwin_server_config USING btree (server_id);


--
-- Name: idx_saf_date; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_saf_date ON public.server_audit_full USING btree (audit_date DESC);


--
-- Name: idx_saf_hostname; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_saf_hostname ON public.server_audit_full USING btree (hostname);


--
-- Name: idx_saf_server_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_saf_server_id ON public.server_audit_full USING btree (server_id);


--
-- Name: idx_sp_hprod; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sp_hprod ON public.server_pairs USING btree (hprod_server_id);


--
-- Name: idx_sp_prod; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sp_prod ON public.server_pairs USING btree (prod_server_id);


--
-- Name: idx_srv_app; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_app ON public.servers USING btree (application_id);


--
-- Name: idx_srv_cluster; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_cluster ON public.servers USING btree (cluster_id);


--
-- Name: idx_srv_db_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_db_server ON public.server_databases USING btree (server_id);


--
-- Name: idx_srv_db_type; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_db_type ON public.server_databases USING btree (db_type);


--
-- Name: idx_srv_domenv; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_domenv ON public.servers USING btree (domain_env_id);


--
-- Name: idx_srv_etat; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_etat ON public.servers USING btree (etat);


--
-- Name: idx_srv_hostname; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_hostname ON public.servers USING gin (hostname public.gin_trgm_ops);


--
-- Name: idx_srv_intervenant; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_intervenant ON public.servers USING btree (default_intervenant_id);


--
-- Name: idx_srv_licence; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_licence ON public.servers USING btree (licence_support);


--
-- Name: idx_srv_machine; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_machine ON public.servers USING btree (machine_type);


--
-- Name: idx_srv_os; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_os ON public.servers USING btree (os_family);


--
-- Name: idx_srv_patch_owner; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_patch_owner ON public.servers USING btree (patch_os_owner);


--
-- Name: idx_srv_qualys; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_qualys ON public.servers USING btree (qualys_asset_id) WHERE (qualys_asset_id IS NOT NULL);


--
-- Name: idx_srv_tier; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_tier ON public.servers USING btree (tier);


--
-- Name: idx_srv_zone; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srv_zone ON public.servers USING btree (zone_id);


--
-- Name: idx_srvip_ip; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srvip_ip ON public.server_ips USING gist (ip_address inet_ops);


--
-- Name: idx_srvip_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_srvip_server ON public.server_ips USING btree (server_id);


--
-- Name: idx_sspec_server; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_sspec_server ON public.server_specifics USING btree (server_id);


--
-- Name: idx_userperm_user; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_userperm_user ON public.user_permissions USING btree (user_id);


--
-- Name: idx_users_role; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_users_role ON public.users USING btree (role);


--
-- Name: idx_users_username; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX idx_users_username ON public.users USING btree (username);


--
-- Name: qms_hostname_norm_uniq; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX qms_hostname_norm_uniq ON public.qualys_missing_servers USING btree (hostname_norm);


--
-- Name: qms_reason_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX qms_reason_idx ON public.qualys_missing_servers USING btree (reason_category);


--
-- Name: qms_server_id_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX qms_server_id_idx ON public.qualys_missing_servers USING btree (server_id);


--
-- Name: qms_status_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX qms_status_idx ON public.qualys_missing_servers USING btree (status);


--
-- Name: users_email_unique_active; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX users_email_unique_active ON public.users USING btree (lower((email)::text)) WHERE ((email IS NOT NULL) AND ((email)::text <> ''::text));


--
-- Name: qualys_missing_servers qms_updated_at_trg; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER qms_updated_at_trg BEFORE UPDATE ON public.qualys_missing_servers FOR EACH ROW EXECUTE FUNCTION public.qms_set_updated_at();


--
-- Name: applications trg_applications_ts; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_applications_ts BEFORE UPDATE ON public.applications FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: campaigns trg_campaigns_ts; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_campaigns_ts BEFORE UPDATE ON public.campaigns FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: domains trg_domains_ts; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_domains_ts BEFORE UPDATE ON public.domains FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: patch_sessions trg_patch_sessions_ts; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_patch_sessions_ts BEFORE UPDATE ON public.patch_sessions FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: servers trg_servers_ts; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_servers_ts BEFORE UPDATE ON public.servers FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: servers trg_srv_domenv_count; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_srv_domenv_count AFTER INSERT OR DELETE OR UPDATE OF domain_env_id ON public.servers FOR EACH ROW EXECUTE FUNCTION public.update_domenv_count();


--
-- Name: server_specifics trg_sspec_ts; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_sspec_ts BEFORE UPDATE ON public.server_specifics FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: users trg_users_ts; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER trg_users_ts BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: audit_log audit_log_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.audit_log
    ADD CONSTRAINT audit_log_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: campaign_accord_details campaign_accord_details_accord_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accord_details
    ADD CONSTRAINT campaign_accord_details_accord_id_fkey FOREIGN KEY (accord_id) REFERENCES public.campaign_accords(id) ON DELETE CASCADE;


--
-- Name: campaign_accord_details campaign_accord_details_session_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accord_details
    ADD CONSTRAINT campaign_accord_details_session_id_fkey FOREIGN KEY (session_id) REFERENCES public.patch_sessions(id);


--
-- Name: campaign_accords campaign_accords_campaign_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accords
    ADD CONSTRAINT campaign_accords_campaign_id_fkey FOREIGN KEY (campaign_id) REFERENCES public.campaigns(id) ON DELETE CASCADE;


--
-- Name: campaign_accords campaign_accords_contact_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_accords
    ADD CONSTRAINT campaign_accords_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES public.contacts(id);


--
-- Name: campaign_operator_limits campaign_operator_limits_campaign_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_operator_limits
    ADD CONSTRAINT campaign_operator_limits_campaign_id_fkey FOREIGN KEY (campaign_id) REFERENCES public.campaigns(id) ON DELETE CASCADE;


--
-- Name: campaign_operator_limits campaign_operator_limits_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaign_operator_limits
    ADD CONSTRAINT campaign_operator_limits_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: campaigns campaigns_created_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.campaigns
    ADD CONSTRAINT campaigns_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);


--
-- Name: cluster_members cluster_members_cluster_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.cluster_members
    ADD CONSTRAINT cluster_members_cluster_id_fkey FOREIGN KEY (cluster_id) REFERENCES public.clusters(id) ON DELETE CASCADE;


--
-- Name: cluster_members cluster_members_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.cluster_members
    ADD CONSTRAINT cluster_members_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: contact_scopes contact_scopes_contact_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.contact_scopes
    ADD CONSTRAINT contact_scopes_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES public.contacts(id) ON DELETE CASCADE;


--
-- Name: default_assignments default_assignments_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.default_assignments
    ADD CONSTRAINT default_assignments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: domain_environments domain_environments_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_environments
    ADD CONSTRAINT domain_environments_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE;


--
-- Name: domain_environments domain_environments_environment_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_environments
    ADD CONSTRAINT domain_environments_environment_id_fkey FOREIGN KEY (environment_id) REFERENCES public.environments(id) ON DELETE CASCADE;


--
-- Name: domain_responsables domain_responsables_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.domain_responsables
    ADD CONSTRAINT domain_responsables_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE;


--
-- Name: import_history import_history_imported_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.import_history
    ADD CONSTRAINT import_history_imported_by_fkey FOREIGN KEY (imported_by) REFERENCES public.users(id);


--
-- Name: network_flow_map network_flow_map_audit_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.network_flow_map
    ADD CONSTRAINT network_flow_map_audit_id_fkey FOREIGN KEY (audit_id) REFERENCES public.server_audit_full(id) ON DELETE CASCADE;


--
-- Name: patch_history patch_history_campaign_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_history
    ADD CONSTRAINT patch_history_campaign_id_fkey FOREIGN KEY (campaign_id) REFERENCES public.campaigns(id);


--
-- Name: patch_history patch_history_intervenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_history
    ADD CONSTRAINT patch_history_intervenant_id_fkey FOREIGN KEY (intervenant_id) REFERENCES public.users(id);


--
-- Name: patch_history patch_history_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_history
    ADD CONSTRAINT patch_history_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id);


--
-- Name: patch_planning patch_planning_domain_code_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_planning
    ADD CONSTRAINT patch_planning_domain_code_fkey FOREIGN KEY (domain_code) REFERENCES public.domains(code);


--
-- Name: patch_sessions patch_sessions_campaign_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_sessions
    ADD CONSTRAINT patch_sessions_campaign_id_fkey FOREIGN KEY (campaign_id) REFERENCES public.campaigns(id) ON DELETE CASCADE;


--
-- Name: patch_sessions patch_sessions_intervenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_sessions
    ADD CONSTRAINT patch_sessions_intervenant_id_fkey FOREIGN KEY (intervenant_id) REFERENCES public.users(id);


--
-- Name: patch_sessions patch_sessions_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_sessions
    ADD CONSTRAINT patch_sessions_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id);


--
-- Name: patch_validation patch_validation_marked_by_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_validation
    ADD CONSTRAINT patch_validation_marked_by_user_id_fkey FOREIGN KEY (marked_by_user_id) REFERENCES public.users(id);


--
-- Name: patch_validation patch_validation_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_validation
    ADD CONSTRAINT patch_validation_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: patch_validation patch_validation_validated_by_contact_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.patch_validation
    ADD CONSTRAINT patch_validation_validated_by_contact_id_fkey FOREIGN KEY (validated_by_contact_id) REFERENCES public.contacts(id);


--
-- Name: qualys_assets qualys_assets_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_assets
    ADD CONSTRAINT qualys_assets_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id);


--
-- Name: qualys_missing_servers qualys_missing_servers_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.qualys_missing_servers
    ADD CONSTRAINT qualys_missing_servers_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE SET NULL;


--
-- Name: quickwin_entries quickwin_entries_prod_pair_entry_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_entries
    ADD CONSTRAINT quickwin_entries_prod_pair_entry_id_fkey FOREIGN KEY (prod_pair_entry_id) REFERENCES public.quickwin_entries(id) ON DELETE SET NULL;


--
-- Name: quickwin_entries quickwin_entries_run_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_entries
    ADD CONSTRAINT quickwin_entries_run_id_fkey FOREIGN KEY (run_id) REFERENCES public.quickwin_runs(id) ON DELETE CASCADE;


--
-- Name: quickwin_entries quickwin_entries_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_entries
    ADD CONSTRAINT quickwin_entries_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id);


--
-- Name: quickwin_logs quickwin_logs_entry_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_logs
    ADD CONSTRAINT quickwin_logs_entry_id_fkey FOREIGN KEY (entry_id) REFERENCES public.quickwin_entries(id) ON DELETE SET NULL;


--
-- Name: quickwin_logs quickwin_logs_run_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_logs
    ADD CONSTRAINT quickwin_logs_run_id_fkey FOREIGN KEY (run_id) REFERENCES public.quickwin_runs(id) ON DELETE CASCADE;


--
-- Name: quickwin_runs quickwin_runs_created_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_runs
    ADD CONSTRAINT quickwin_runs_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);


--
-- Name: quickwin_server_config quickwin_server_config_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.quickwin_server_config
    ADD CONSTRAINT quickwin_server_config_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_audit_full server_audit_full_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_audit_full
    ADD CONSTRAINT server_audit_full_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id);


--
-- Name: server_audit server_audit_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_audit
    ADD CONSTRAINT server_audit_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_correspondance server_correspondance_created_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_correspondance
    ADD CONSTRAINT server_correspondance_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);


--
-- Name: server_correspondance server_correspondance_nonprod_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_correspondance
    ADD CONSTRAINT server_correspondance_nonprod_server_id_fkey FOREIGN KEY (nonprod_server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_correspondance server_correspondance_prod_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_correspondance
    ADD CONSTRAINT server_correspondance_prod_server_id_fkey FOREIGN KEY (prod_server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_databases server_databases_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_databases
    ADD CONSTRAINT server_databases_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_ips server_ips_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_ips
    ADD CONSTRAINT server_ips_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_pairs server_pairs_hprod_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_pairs
    ADD CONSTRAINT server_pairs_hprod_server_id_fkey FOREIGN KEY (hprod_server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_pairs server_pairs_prod_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_pairs
    ADD CONSTRAINT server_pairs_prod_server_id_fkey FOREIGN KEY (prod_server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: server_specifics server_specifics_dependency_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_specifics
    ADD CONSTRAINT server_specifics_dependency_server_id_fkey FOREIGN KEY (dependency_server_id) REFERENCES public.servers(id);


--
-- Name: server_specifics server_specifics_server_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.server_specifics
    ADD CONSTRAINT server_specifics_server_id_fkey FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: servers servers_application_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_application_id_fkey FOREIGN KEY (application_id) REFERENCES public.applications(id);


--
-- Name: servers servers_cluster_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_cluster_id_fkey FOREIGN KEY (cluster_id) REFERENCES public.clusters(id);


--
-- Name: servers servers_created_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id);


--
-- Name: servers servers_default_intervenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_default_intervenant_id_fkey FOREIGN KEY (default_intervenant_id) REFERENCES public.users(id);


--
-- Name: servers servers_domain_env_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_domain_env_id_fkey FOREIGN KEY (domain_env_id) REFERENCES public.domain_environments(id);


--
-- Name: servers servers_vcenter_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_vcenter_id_fkey FOREIGN KEY (vcenter_id) REFERENCES public.vcenters(id);


--
-- Name: servers servers_zone_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_zone_id_fkey FOREIGN KEY (zone_id) REFERENCES public.zones(id);


--
-- Name: settings settings_updated_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.settings
    ADD CONSTRAINT settings_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.users(id);


--
-- Name: user_permissions user_permissions_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_permissions
    ADD CONSTRAINT user_permissions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--