--
--


SET transaction_timeout = 0;
SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: calculate_monthly_budget(bigint, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.calculate_monthly_budget(p_article_id bigint, p_target_month date DEFAULT CURRENT_DATE) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
            DECLARE
                v_monthly_budget DECIMAL(30,20);
                v_target_year INTEGER;
                v_target_month_num INTEGER;
            BEGIN
                -- Extract year and month from target date
                v_target_year := EXTRACT(YEAR FROM p_target_month);
                v_target_month_num := EXTRACT(MONTH FROM p_target_month);

                -- Sum all forecast_quantity values for the specified calendar month
                SELECT COALESCE(SUM(forecast_quantity), 0)
                INTO v_monthly_budget
                FROM sales_forecasts
                WHERE article_id = p_article_id
                  AND EXTRACT(YEAR FROM forecast_date) = v_target_year
                  AND EXTRACT(MONTH FROM forecast_date) = v_target_month_num;

                RETURN v_monthly_budget;
            END;
            $$;


ALTER FUNCTION public.calculate_monthly_budget(p_article_id bigint, p_target_month date) OWNER TO postgres;

--
-- Name: calculate_production_data(bigint); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.calculate_production_data(p_article_id bigint) RETURNS void
    LANGUAGE plpgsql
    AS $$
            DECLARE
                v_stock_disponible DECIMAL(30,20);
                v_monthly_budget DECIMAL(30,20);
                v_target_coverage_months INTEGER;
                v_batch_size DECIMAL(30,20);
                v_initial_coverage DECIMAL(30,20);
                v_quantity_to_produce DECIMAL(30,20);
                v_batches_to_produce DECIMAL(30,20);
                v_priority SMALLINT;
                v_month_year RECORD;
            BEGIN
                -- Get stock and article data
                SELECT
                    COALESCE(a.stock, 0),
                    a.target_coverage_months,
                    COALESCE(a.batch_size, 1)
                INTO
                    v_stock_disponible,
                    v_target_coverage_months,
                    v_batch_size
                FROM articles a
                WHERE a.id = p_article_id;

                -- Loop through all months that have forecast data for this article
                FOR v_month_year IN
                    SELECT DISTINCT
                        make_date(EXTRACT(YEAR FROM forecast_date)::INTEGER, EXTRACT(MONTH FROM forecast_date)::INTEGER, 1) as calculation_date
                    FROM sales_forecasts
                    WHERE article_id = p_article_id
                    ORDER BY calculation_date
                LOOP
                    -- Calculate monthly_budget from sales forecasts for this specific month
                    v_monthly_budget := calculate_monthly_budget(p_article_id, v_month_year.calculation_date);

                    -- Calculate initial_coverage
                    IF v_monthly_budget > 0 THEN
                        v_initial_coverage := (v_stock_disponible / v_monthly_budget) * 26;
                    ELSE
                        v_initial_coverage := 0;
                    END IF;

                    -- Calculate quantity_to_produce
                    v_quantity_to_produce := (v_target_coverage_months - (v_initial_coverage / 26)) * v_monthly_budget;

                    IF v_quantity_to_produce < 0 THEN
                        v_quantity_to_produce := 0;
                    END IF;

                    -- Calculate batches_to_produce
                    IF v_batch_size > 0 THEN
                        v_batches_to_produce := CEIL(v_quantity_to_produce / v_batch_size);
                    ELSE
                        v_batches_to_produce := 0;
                    END IF;

                    -- Calculate priority based on business rules
                    -- If initial_coverage (days) is NULL, treat as 0 for the comparison
                    -- If initial_coverage < 26 → priority = 1
                    -- Else if initial_coverage < 78 → priority = 2
                    -- Else (≥ 78) → priority = 3
                    IF COALESCE(v_initial_coverage, 0) < 26 THEN
                        v_priority := 1;
                    ELSIF COALESCE(v_initial_coverage, 0) < 78 THEN
                        v_priority := 2;
                    ELSE
                        v_priority := 3;
                    END IF;

                    -- Insert or update for this specific month
                    INSERT INTO production_calculations (
                        article_id, calculation_date, initial_coverage,
                        monthly_budget, quantity_to_produce, batches_to_produce,
                        priority, created_at, updated_at
                    ) VALUES (
                        p_article_id, v_month_year.calculation_date, v_initial_coverage,
                        v_monthly_budget, v_quantity_to_produce, v_batches_to_produce,
                        v_priority, NOW(), NOW()
                    )
                    ON CONFLICT (article_id, calculation_date)
                    DO UPDATE SET
                        initial_coverage = EXCLUDED.initial_coverage,
                        monthly_budget = EXCLUDED.monthly_budget,
                        quantity_to_produce = EXCLUDED.quantity_to_produce,
                        batches_to_produce = EXCLUDED.batches_to_produce,
                        priority = EXCLUDED.priority,
                        updated_at = NOW();
                END LOOP;
            END;
            $$;


ALTER FUNCTION public.calculate_production_data(p_article_id bigint) OWNER TO postgres;

--
-- Name: calculate_quantity_per_lot(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.calculate_quantity_per_lot() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            DECLARE
                parent_batch_size DECIMAL(30,20);
            BEGIN
                SELECT batch_size INTO parent_batch_size
                FROM articles
                WHERE id = NEW.parent_article_id;

                IF parent_batch_size IS NOT NULL THEN
                    NEW.quantity_per_lot := parent_batch_size * NEW.quantity_per_unit;
                END IF;

                RETURN NEW;
            END;
            $$;


ALTER FUNCTION public.calculate_quantity_per_lot() OWNER TO postgres;

--
-- Name: calculate_sales_forecast_variance(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.calculate_sales_forecast_variance() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
			DECLARE
				week_start DATE;
				week_end DATE;
				actual_qty DECIMAL(30,20);
				forecast_qty DECIMAL(30,20);
				variance_qty DECIMAL(30,20);
				variance_pct DECIMAL(28,20);
			BEGIN
				IF TG_TABLE_NAME = 'stock_movements' THEN
					week_start := DATE_TRUNC('week', COALESCE(NEW.movement_date, OLD.movement_date));
					week_end := week_start + INTERVAL '6 days';

					SELECT COALESCE(SUM(ABS(quantity)), 0) INTO actual_qty
					FROM stock_movements
					WHERE article_id = COALESCE(NEW.article_id, OLD.article_id)
					AND movement_date BETWEEN week_start AND week_end;

					SELECT forecast_quantity INTO forecast_qty
					FROM sales_forecasts
					WHERE article_id = COALESCE(NEW.article_id, OLD.article_id)
					AND forecast_date BETWEEN week_start AND week_end;

					IF forecast_qty IS NOT NULL THEN
						variance_qty := actual_qty - forecast_qty;
						variance_pct := CASE
							WHEN forecast_qty > 0 THEN (variance_qty / forecast_qty) * 100
							ELSE 0
						END;

						UPDATE sales_forecasts
						SET total_actual_quantity = actual_qty,
							variance_quantity = variance_qty,
							variance_percentage = variance_pct,
							updated_at = NOW()
						WHERE article_id = COALESCE(NEW.article_id, OLD.article_id)
						AND forecast_date BETWEEN week_start AND week_end;
					END IF;
				END IF;

				IF TG_TABLE_NAME = 'sales_forecasts' THEN
					week_start := DATE_TRUNC('week', NEW.forecast_date);
					week_end := week_start + INTERVAL '6 days';

					SELECT COALESCE(SUM(ABS(quantity)), 0) INTO actual_qty
					FROM stock_movements
					WHERE article_id = NEW.article_id
					AND movement_date BETWEEN week_start AND week_end;

					variance_qty := actual_qty - NEW.forecast_quantity;
					variance_pct := CASE
						WHEN NEW.forecast_quantity > 0 THEN (variance_qty / NEW.forecast_quantity) * 100
						ELSE 0
					END;

					NEW.total_actual_quantity := actual_qty;
					NEW.variance_quantity := variance_qty;
					NEW.variance_percentage := variance_pct;
				END IF;

				RETURN COALESCE(NEW, OLD);
			END;
			$$;


ALTER FUNCTION public.calculate_sales_forecast_variance() OWNER TO postgres;

--
-- Name: log_table_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.log_table_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            DECLARE
                change_data JSONB;
                change_source TEXT;
                old_coverage DECIMAL(30,20);
                new_coverage DECIMAL(30,20);
            BEGIN
                -- Get the current change source if set
                change_source := current_setting('app.change_source', TRUE);

                -- If no change source is set, default to db_trigger
                IF change_source IS NULL THEN
                    change_source := 'db_trigger';
                END IF;

                -- Special handling for production_calculations to filter by initial_coverage
                IF TG_TABLE_NAME = 'production_calculations' THEN
                    -- Only process if initial_coverage is in the critical range (0 < coverage < 26)
                    IF TG_OP = 'UPDATE' THEN
                        old_coverage := OLD.initial_coverage;
                        new_coverage := NEW.initial_coverage;
                        
                        -- Skip if coverage didn't change or is outside notification range
                        IF old_coverage = new_coverage OR
                           new_coverage <= 0 OR
                           new_coverage >= 26 THEN
                            RETURN NULL;
                        END IF;
                    ELSIF TG_OP = 'INSERT' THEN
                        new_coverage := NEW.initial_coverage;
                        
                        -- Skip if outside notification range
                        IF new_coverage <= 0 OR new_coverage >= 26 THEN
                            RETURN NULL;
                        END IF;
                    ELSIF TG_OP = 'DELETE' THEN
                        -- For deletes, check if it was in the critical range
                        old_coverage := OLD.initial_coverage;
                        
                        -- Only notify if it was in the critical range
                        IF old_coverage <= 0 OR old_coverage >= 26 THEN
                            RETURN NULL;
                        END IF;
                    END IF;
                END IF;


                IF (TG_OP = 'DELETE') THEN
                    change_data = to_jsonb(OLD);
                ELSE
                    change_data = to_jsonb(NEW);
                END IF;

                INSERT INTO db_change_notifications (
                    table_name,
                    operation,
                    record_id,
                    changed_data,
                    source
                )
                VALUES (
                    TG_TABLE_NAME,
                    TG_OP,
                    CASE
                        WHEN TG_OP = 'DELETE' THEN OLD.id
                        ELSE NEW.id
                    END,
                    change_data,
                    change_source
                );

                -- Notify channel with the change (include source in payload)
                PERFORM pg_notify(
                    'db_changes',
                    json_build_object(
                        'table', TG_TABLE_NAME,
                        'operation', TG_OP,
                        'id', CASE WHEN TG_OP = 'DELETE' THEN OLD.id ELSE NEW.id END,
                        'data', change_data,
                        'source', change_source
                    )::text
                );

                RETURN NULL;
            END;
            $$;


ALTER FUNCTION public.log_table_change() OWNER TO postgres;

--
-- Name: set_change_source(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.set_change_source(source_name text) RETURNS void
    LANGUAGE plpgsql
    AS $$
            BEGIN
                PERFORM set_config('app.change_source', source_name, FALSE);
            END;
            $$;


ALTER FUNCTION public.set_change_source(source_name text) OWNER TO postgres;

--
-- Name: trigger_calculate_production(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.trigger_calculate_production() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                -- Handle different table triggers
                IF TG_TABLE_NAME = 'stock_details' THEN
                    PERFORM calculate_production_data(NEW.article_id);
                ELSIF TG_TABLE_NAME = 'articles' THEN
                    PERFORM calculate_production_data(NEW.id);
                END IF;

                RETURN NEW;
            END;
            $$;


ALTER FUNCTION public.trigger_calculate_production() OWNER TO postgres;

--
-- Name: trigger_refresh_priority_on_category_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.trigger_refresh_priority_on_category_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                -- Only refresh if category actually changed
                IF OLD.category IS DISTINCT FROM NEW.category THEN
                    -- Update priority for current month production_calculations
                    UPDATE production_calculations
                    SET
                        article_category = NEW.category,
                        priority = CASE
                            WHEN COALESCE(initial_coverage, 0) < 26 THEN 1
                            WHEN COALESCE(initial_coverage, 0) < 78 THEN 2
                            WHEN NEW.category = 'OFF' THEN 3
                            ELSE 4
                        END,
                        updated_at = NOW()
                    WHERE article_id = NEW.id
                      AND calculation_date >= date_trunc('month', CURRENT_DATE);
                END IF;
                
                RETURN NEW;
            END;
            $$;


ALTER FUNCTION public.trigger_refresh_priority_on_category_change() OWNER TO postgres;

--
-- Name: trigger_update_monthly_budget(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.trigger_update_monthly_budget() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                -- Handle INSERT and UPDATE
                IF TG_OP = 'DELETE' THEN
                    -- For DELETE, use OLD record
                    PERFORM calculate_production_data(OLD.article_id);
                    RETURN OLD;
                ELSE
                    -- For INSERT and UPDATE, use NEW record
                    PERFORM calculate_production_data(NEW.article_id);
                    RETURN NEW;
                END IF;
            END;
            $$;


ALTER FUNCTION public.trigger_update_monthly_budget() OWNER TO postgres;

--
-- Name: trigger_update_priority(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.trigger_update_priority() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                -- Calculate priority based on initial_coverage only
                -- If initial_coverage < 26 → priority = 1
                -- Else if initial_coverage < 78 → priority = 2
                -- Else (≥ 78) → priority = 3
                IF COALESCE(NEW.initial_coverage, 0) < 26 THEN
                    NEW.priority := 1;
                ELSIF COALESCE(NEW.initial_coverage, 0) < 78 THEN
                    NEW.priority := 2;
                ELSE
                    NEW.priority := 3;
                END IF;
                
                RETURN NEW;
            END;
            $$;


ALTER FUNCTION public.trigger_update_priority() OWNER TO postgres;

--
-- Name: update_article_stock(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_article_stock() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
			DECLARE
				article_id_to_update BIGINT;
			BEGIN
				-- Determine which article_id to update based on the operation
				IF TG_OP = 'DELETE' THEN
					article_id_to_update := OLD.article_id;
				ELSE
					article_id_to_update := NEW.article_id;
				END IF;

				-- Update the stock column in articles table (only sum status 'A' items)
				UPDATE articles
				SET stock = COALESCE((
					SELECT SUM(initial_quantity)
					FROM stock_details
					WHERE article_id = article_id_to_update AND status = 'A'
				), 0)
				WHERE id = article_id_to_update;

				-- Handle UPDATE operations where article_id might change
				IF TG_OP = 'UPDATE' AND OLD.article_id != NEW.article_id THEN
					UPDATE articles
					SET stock = COALESCE((
						SELECT SUM(initial_quantity)
						FROM stock_details
						WHERE article_id = OLD.article_id AND status = 'A'
					), 0)
					WHERE id = OLD.article_id;
				END IF;

				RETURN COALESCE(NEW, OLD);
			END;
			$$;


ALTER FUNCTION public.update_article_stock() OWNER TO postgres;

--
-- Name: update_product_compositions_on_article_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_product_compositions_on_article_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                IF OLD.batch_size IS DISTINCT FROM NEW.batch_size THEN
                    UPDATE product_compositions
                    SET quantity_per_lot = NEW.batch_size * quantity_per_unit
                    WHERE parent_article_id = NEW.id;
                END IF;

                RETURN NEW;
            END;
            $$;


ALTER FUNCTION public.update_product_compositions_on_article_change() OWNER TO postgres;



--
-- Name: article_families; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.article_families (
    id bigint NOT NULL,
    family_code character varying(255) NOT NULL,
    family_name character varying(255) NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone,
    CONSTRAINT check_family_code_valid CHECK (((family_code)::text = ANY ((ARRAY['MP'::character varying, 'PF'::character varying, 'AC'::character varying, 'SF'::character varying])::text[])))
);


ALTER TABLE public.article_families OWNER TO postgres;

--
-- Name: COLUMN article_families.family_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.article_families.family_code IS 'c_famille_article';


--
-- Name: COLUMN article_families.family_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.article_families.family_name IS 'libelle_famille_article';


--
-- Name: article_families_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.article_families_id_seq OWNER TO postgres;

--
-- Name: article_families_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.article_families_id_seq OWNED BY public.article_families.id;


--
-- Name: articles; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.articles (
    id bigint NOT NULL,
    article_family_id bigint,
    article_code character varying(255) NOT NULL,
    article_name character varying(255) NOT NULL,
    inn character varying(255),
    category character varying(50),
    material_type character varying(50),
    form character varying(100),
    batch_size numeric(30,20),
    abc_classification character varying(1),
    unit_purchase character varying(20),
    unit_stock character varying(20),
    bulk_code character varying(50),
    is_complex boolean,
    is_sample boolean,
    target_coverage_months integer,
    stock numeric(30,20) DEFAULT '0'::numeric NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.articles OWNER TO postgres;

--
-- Name: COLUMN articles.article_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.article_code IS 'ITMREF_0, HC0099/C12028/V13001...';


--
-- Name: COLUMN articles.article_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.article_name IS 'ITMDES1_0';


--
-- Name: COLUMN articles.inn; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.inn IS 'TSICOD0_0, International Nonproprietary Name, OME';


--
-- Name: COLUMN articles.category; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.category IS 'TSICOD1_0, OFFICINAL or HOSPITALIER (finished_product), OFF/PIL/ETU';


--
-- Name: COLUMN articles.material_type; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.material_type IS 'TSICOD2_0, type primary_material, AC1/AC2/PRA/EXC...';


--
-- Name: COLUMN articles.form; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.form IS 'TSICOD3_0, Article form (tablet, capsule, gel, etc.), GEL/CAP/TAB/...';


--
-- Name: COLUMN articles.batch_size; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.batch_size IS 'REOMINQTY_0, taille_lot, 100/1000/10000...';


--
-- Name: COLUMN articles.abc_classification; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.abc_classification IS 'ABC inventory classification (A/B/C), used for prioritization';


--
-- Name: COLUMN articles.unit_purchase; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.unit_purchase IS 'PUU_0, Purchase unit of measure (primary_material), UN/MIL/KG...';


--
-- Name: COLUMN articles.unit_stock; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.unit_stock IS 'STU_0, Stock unit of measure (primary_material), UN/G...';


--
-- Name: COLUMN articles.bulk_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.bulk_code IS 'code_vrac (finished_product), IM1231/IM1232/...';


--
-- Name: COLUMN articles.is_complex; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.is_complex IS 'Complex articles: 4-6 months coverage (finished_product)';


--
-- Name: COLUMN articles.is_sample; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.is_sample IS 'echantillon (finished_product)';


--
-- Name: COLUMN articles.target_coverage_months; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.target_coverage_months IS 'coverture_cible_mois';


--
-- Name: COLUMN articles.stock; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.articles.stock IS 'Total stock quantity (sum of initial_quantity from stock_details with status A only)';


--
-- Name: articles_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.articles_id_seq OWNER TO postgres;

--
-- Name: articles_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.articles_id_seq OWNED BY public.articles.id;


--
-- Name: cache; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.cache (
    key character varying(255) NOT NULL,
    value text NOT NULL,
    expiration integer NOT NULL
);


ALTER TABLE public.cache OWNER TO postgres;

--
-- Name: cache_locks; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.cache_locks (
    key character varying(255) NOT NULL,
    owner character varying(255) NOT NULL,
    expiration integer NOT NULL
);


ALTER TABLE public.cache_locks OWNER TO postgres;

--
-- Name: db_change_notifications; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.db_change_notifications (
    id integer NOT NULL,
    table_name character varying(255) NOT NULL,
    operation character varying(50) NOT NULL,
    record_id bigint NOT NULL,
    changed_data jsonb NOT NULL,
    source character varying(100) DEFAULT 'db_trigger'::character varying,
    processed boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE public.db_change_notifications OWNER TO postgres;

--
-- Name: db_change_notifications_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.db_change_notifications_id_seq OWNER TO postgres;

--
-- Name: db_change_notifications_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.db_change_notifications_id_seq OWNED BY public.db_change_notifications.id;


--
-- Name: failed_jobs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.failed_jobs (
    id bigint NOT NULL,
    uuid character varying(255) NOT NULL,
    connection text NOT NULL,
    queue text NOT NULL,
    payload text NOT NULL,
    exception text NOT NULL,
    failed_at timestamp(0) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);


ALTER TABLE public.failed_jobs OWNER TO postgres;

--
-- Name: failed_jobs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.failed_jobs_id_seq OWNER TO postgres;

--
-- Name: failed_jobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.failed_jobs_id_seq OWNED BY public.failed_jobs.id;


--
-- Name: job_batches; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.job_batches (
    id character varying(255) NOT NULL,
    name character varying(255) NOT NULL,
    total_jobs integer NOT NULL,
    pending_jobs integer NOT NULL,
    failed_jobs integer NOT NULL,
    failed_job_ids text NOT NULL,
    options text,
    cancelled_at integer,
    created_at integer NOT NULL,
    finished_at integer
);


ALTER TABLE public.job_batches OWNER TO postgres;

--
-- Name: jobs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.jobs (
    id bigint NOT NULL,
    queue character varying(255) NOT NULL,
    payload text NOT NULL,
    attempts smallint NOT NULL,
    reserved_at integer,
    available_at integer NOT NULL,
    created_at integer NOT NULL
);


ALTER TABLE public.jobs OWNER TO postgres;

--
-- Name: jobs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.jobs_id_seq OWNER TO postgres;

--
-- Name: jobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.jobs_id_seq OWNED BY public.jobs.id;


--
-- Name: migrations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.migrations (
    id integer NOT NULL,
    migration character varying(255) NOT NULL,
    batch integer NOT NULL
);


ALTER TABLE public.migrations OWNER TO postgres;

--
-- Name: migrations_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.migrations_id_seq OWNER TO postgres;

--
-- Name: migrations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.migrations_id_seq OWNED BY public.migrations.id;


--
-- Name: notification_user; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.notification_user (
    id bigint NOT NULL,
    notification_id bigint NOT NULL,
    user_id bigint NOT NULL,
    read_at timestamp(0) without time zone,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.notification_user OWNER TO postgres;

--
-- Name: notification_user_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.notification_user_id_seq OWNER TO postgres;

--
-- Name: notification_user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.notification_user_id_seq OWNED BY public.notification_user.id;


--
-- Name: notifications; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.notifications (
    id bigint NOT NULL,
    type character varying(255) NOT NULL,
    content jsonb NOT NULL,
    source character varying(100) DEFAULT 'application'::character varying NOT NULL,
    user_id bigint,
    read_at timestamp(0) without time zone,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.notifications OWNER TO postgres;

--
-- Name: notifications_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.notifications_id_seq OWNER TO postgres;

--
-- Name: notifications_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.notifications_id_seq OWNED BY public.notifications.id;


--
-- Name: password_reset_tokens; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.password_reset_tokens (
    email character varying(255) NOT NULL,
    token character varying(255) NOT NULL,
    created_at timestamp(0) without time zone
);


ALTER TABLE public.password_reset_tokens OWNER TO postgres;

--
-- Name: personal_access_tokens; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.personal_access_tokens (
    id bigint NOT NULL,
    tokenable_type character varying(255) NOT NULL,
    tokenable_id bigint NOT NULL,
    name character varying(255) NOT NULL,
    token character varying(64) NOT NULL,
    abilities text,
    last_used_at timestamp(0) without time zone,
    expires_at timestamp(0) without time zone,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.personal_access_tokens OWNER TO postgres;

--
-- Name: personal_access_tokens_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.personal_access_tokens_id_seq OWNER TO postgres;

--
-- Name: personal_access_tokens_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.personal_access_tokens_id_seq OWNED BY public.personal_access_tokens.id;


--
-- Name: product_compositions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.product_compositions (
    id bigint NOT NULL,
    parent_article_id bigint NOT NULL,
    component_article_id bigint NOT NULL,
    quantity_per_unit numeric(30,20) NOT NULL,
    quantity_per_lot numeric(30,20) NOT NULL,
    operating_unit character varying(20) DEFAULT 'g'::character varying NOT NULL,
    conversion_factor numeric(30,20) DEFAULT '1'::numeric NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.product_compositions OWNER TO postgres;

--
-- Name: COLUMN product_compositions.parent_article_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.product_compositions.parent_article_id IS 'ITMREF_0, References the main article, HC0099/C12028/V13001...';


--
-- Name: COLUMN product_compositions.component_article_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.product_compositions.component_article_id IS 'CPNITMREF_0,References the component article, C15129/IM1231...';


--
-- Name: COLUMN product_compositions.quantity_per_unit; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.product_compositions.quantity_per_unit IS 'BOMQTY_0, Quantity per product unit, 1/14/0.02/...';


--
-- Name: COLUMN product_compositions.quantity_per_lot; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.product_compositions.quantity_per_lot IS 'Total quantity per production lot';


--
-- Name: COLUMN product_compositions.operating_unit; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.product_compositions.operating_unit IS 'BOMUOM_0, UN/G...';


--
-- Name: COLUMN product_compositions.conversion_factor; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.product_compositions.conversion_factor IS 'BOMSTUCOE_0, Conversion coefficient between units, 1/0.1/0.01...';


--
-- Name: product_compositions_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.product_compositions_id_seq OWNER TO postgres;

--
-- Name: product_compositions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.product_compositions_id_seq OWNED BY public.product_compositions.id;


--
-- Name: production_calculations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.production_calculations (
    id bigint NOT NULL,
    article_id bigint NOT NULL,
    initial_coverage numeric(30,20),
    monthly_budget numeric(30,20),
    calculation_date date NOT NULL,
    quantity_to_produce numeric(30,20),
    batches_to_produce numeric(30,20),
    priority smallint DEFAULT '3'::smallint NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.production_calculations OWNER TO postgres;

--
-- Name: COLUMN production_calculations.initial_coverage; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.production_calculations.initial_coverage IS 'Initial coverage in days: (stock_disponible / monthly_budget) * 26';


--
-- Name: COLUMN production_calculations.monthly_budget; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.production_calculations.monthly_budget IS 'Monthly budget calculated from sum of forecast_quantity grouped by calendar month';


--
-- Name: COLUMN production_calculations.calculation_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.production_calculations.calculation_date IS 'First day of the month for which calculations are performed (YYYY-MM-01)';


--
-- Name: COLUMN production_calculations.quantity_to_produce; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.production_calculations.quantity_to_produce IS 'Quantity to produce: (target_coverage_months - initial_coverage_months) * monthly_budget';


--
-- Name: COLUMN production_calculations.batches_to_produce; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.production_calculations.batches_to_produce IS 'Batches to produce: quantity_to_produce / batch_size';


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

COMMENT ON COLUMN public.production_calculations.priority IS 'Priority level: 1 (highest) to 3 (lowest) based on initial_coverage thresholds';


--
-- Name: production_calculations_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.production_calculations_id_seq OWNER TO postgres;

--
-- Name: production_calculations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.production_calculations_id_seq OWNED BY public.production_calculations.id;


--
-- Name: production_orders; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.production_orders (
    id bigint NOT NULL,
    article_id bigint NOT NULL,
    code character varying(100),
    status character varying(255) NOT NULL,
    batch_size_override numeric(30,20),
    batch_sales_quantity numeric(30,20),
    batch_sample_quantity numeric(30,20),
    selected_sf_stock_detail_id bigint,
    notes text,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone,
    deleted_at timestamp(0) without time zone,
    CONSTRAINT production_orders_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'in_progress'::character varying, 'completed'::character varying])::text[])))
);


ALTER TABLE public.production_orders OWNER TO postgres;

--
-- Name: COLUMN production_orders.article_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.production_orders.article_id IS 'References a finished product article';


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

COMMENT ON COLUMN public.production_orders.status IS 'Order status: pending, in_progress, completed';


--
-- Name: COLUMN production_orders.batch_size_override; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.production_orders.batch_size_override IS 'Batch size override for this order only (overrides article batch_size)';


--
-- Name: production_orders_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.production_orders_id_seq OWNER TO postgres;

--
-- Name: production_orders_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.production_orders_id_seq OWNED BY public.production_orders.id;


--
-- Name: sales_forecasts; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.sales_forecasts (
    id bigint NOT NULL,
    article_id bigint NOT NULL,
    warehouse_id bigint,
    forecast_code character varying(100) NOT NULL,
    forecast_date date NOT NULL,
    forecast_quantity numeric(30,20) NOT NULL,
    total_actual_quantity numeric(30,20) DEFAULT '0'::numeric NOT NULL,
    variance_quantity numeric(30,20) DEFAULT '0'::numeric NOT NULL,
    variance_percentage numeric(28,20) DEFAULT '0'::numeric NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.sales_forecasts OWNER TO postgres;

--
-- Name: COLUMN sales_forecasts.forecast_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.sales_forecasts.forecast_code IS 'VCRNUM_0, Unique forecast code, SENDAR14057844/SENDAR14057845/...';


--
-- Name: COLUMN sales_forecasts.forecast_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.sales_forecasts.forecast_date IS 'STRDAT_0, Weekly forecast date, 2025-01-01/2025-01-07...';


--
-- Name: COLUMN sales_forecasts.forecast_quantity; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.sales_forecasts.forecast_quantity IS 'EXTQTY_0, Forecast quantity for the week, 425/1000/10000...';


--
-- Name: COLUMN sales_forecasts.total_actual_quantity; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.sales_forecasts.total_actual_quantity IS 'Total actual quantity from stock movements for the week';


--
-- Name: COLUMN sales_forecasts.variance_quantity; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.sales_forecasts.variance_quantity IS 'Variance between actual and forecast (actual - forecast)';


--
-- Name: COLUMN sales_forecasts.variance_percentage; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.sales_forecasts.variance_percentage IS 'Variance percentage ((actual - forecast) / forecast * 100)';


--
-- Name: sales_forecasts_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.sales_forecasts_id_seq OWNER TO postgres;

--
-- Name: sales_forecasts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.sales_forecasts_id_seq OWNED BY public.sales_forecasts.id;


--
-- Name: sessions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.sessions (
    id character varying(255) NOT NULL,
    user_id bigint,
    ip_address character varying(45),
    user_agent text,
    payload text NOT NULL,
    last_activity integer NOT NULL
);


ALTER TABLE public.sessions OWNER TO postgres;

--
-- Name: stock_details; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.stock_details (
    id bigint NOT NULL,
    article_id bigint NOT NULL,
    article_family_id bigint,
    warehouse_id bigint,
    batch_number_fp character varying(100),
    batch_number_rm character varying(100),
    status character varying(20) DEFAULT 'A'::character varying NOT NULL,
    initial_quantity numeric(30,20) DEFAULT '0'::numeric NOT NULL,
    expiry_date date,
    inspection_date date,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.stock_details OWNER TO postgres;

--
-- Name: COLUMN stock_details.batch_number_fp; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.stock_details.batch_number_fp IS 'LOT_0, Batch number for finished products, DAR250029/DAR220998...';


--
-- Name: COLUMN stock_details.batch_number_rm; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.stock_details.batch_number_rm IS 'Batch number for raw materials';


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

COMMENT ON COLUMN public.stock_details.status IS 'STA_0, Stock status, A/Q, A: accepte, Q: quitable';


--
-- Name: COLUMN stock_details.initial_quantity; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.stock_details.initial_quantity IS 'QTYSTU_0, Initial quantity (available stock), 2518/1/10...';


--
-- Name: COLUMN stock_details.expiry_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.stock_details.expiry_date IS 'SHLDAT_0, Expiry date, 2025-01-01/2025-01-02/...';


--
-- Name: COLUMN stock_details.inspection_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.stock_details.inspection_date IS 'Inspection date for quality control';


--
-- Name: stock_details_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.stock_details_id_seq OWNER TO postgres;

--
-- Name: stock_details_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.stock_details_id_seq OWNED BY public.stock_details.id;


--
-- Name: stock_movements; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.stock_movements (
    id bigint NOT NULL,
    article_id bigint NOT NULL,
    warehouse_id bigint,
    movement_date date NOT NULL,
    quantity numeric(30,20) NOT NULL,
    forecast_code character varying(100),
    batch_number_fp character varying(100),
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.stock_movements OWNER TO postgres;

--
-- Name: COLUMN stock_movements.movement_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.stock_movements.movement_date IS 'IPTDAT_0 - Daily Date of the stock movement';


--
-- Name: COLUMN stock_movements.quantity; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.stock_movements.quantity IS 'QTYSTU_0 - Quantity moved (negative for outgoing), -108/-36';


--
-- Name: stock_movements_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.stock_movements_id_seq OWNER TO postgres;

--
-- Name: stock_movements_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.stock_movements_id_seq OWNED BY public.stock_movements.id;


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

CREATE TABLE public.users (
    id bigint NOT NULL,
    name character varying(255) NOT NULL,
    email character varying(255) NOT NULL,
    email_verified_at timestamp(0) without time zone,
    password character varying(255) NOT NULL,
    remember_token character varying(100),
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.users OWNER TO postgres;

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

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


ALTER SEQUENCE public.users_id_seq OWNER TO postgres;

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

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


--
-- Name: variance_alerts_sent; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.variance_alerts_sent (
    id bigint NOT NULL,
    article_id bigint NOT NULL,
    week_start date NOT NULL,
    created_at timestamp(0) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);


ALTER TABLE public.variance_alerts_sent OWNER TO postgres;

--
-- Name: COLUMN variance_alerts_sent.week_start; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.variance_alerts_sent.week_start IS 'Monday date of the week for which the alert was sent (YYYY-MM-DD)';


--
-- Name: COLUMN variance_alerts_sent.created_at; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.variance_alerts_sent.created_at IS 'When the alert was sent';


--
-- Name: variance_alerts_sent_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.variance_alerts_sent_id_seq OWNER TO postgres;

--
-- Name: variance_alerts_sent_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.variance_alerts_sent_id_seq OWNED BY public.variance_alerts_sent.id;


--
-- Name: warehouses; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.warehouses (
    id bigint NOT NULL,
    warehouse_code character varying(50) NOT NULL,
    warehouse_name character varying(255) NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone
);


ALTER TABLE public.warehouses OWNER TO postgres;

--
-- Name: warehouses_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

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


ALTER SEQUENCE public.warehouses_id_seq OWNER TO postgres;

--
-- Name: warehouses_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.warehouses_id_seq OWNED BY public.warehouses.id;


--
-- Name: article_families id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: articles id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: db_change_notifications id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: failed_jobs id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: jobs id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: migrations id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: notification_user id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: notifications id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: personal_access_tokens id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: product_compositions id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: production_calculations id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: production_orders id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: sales_forecasts id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: stock_details id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: stock_movements id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


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

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


--
-- Name: variance_alerts_sent id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: warehouses id; Type: DEFAULT; Schema: public; Owner: postgres
--

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


--
-- Name: article_families article_families_family_code_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.article_families
    ADD CONSTRAINT article_families_family_code_unique UNIQUE (family_code);


--
-- Name: article_families article_families_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.article_families
    ADD CONSTRAINT article_families_pkey PRIMARY KEY (id);


--
-- Name: articles articles_article_code_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.articles
    ADD CONSTRAINT articles_article_code_unique UNIQUE (article_code);


--
-- Name: articles articles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.articles
    ADD CONSTRAINT articles_pkey PRIMARY KEY (id);


--
-- Name: cache_locks cache_locks_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.cache_locks
    ADD CONSTRAINT cache_locks_pkey PRIMARY KEY (key);


--
-- Name: cache cache_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.cache
    ADD CONSTRAINT cache_pkey PRIMARY KEY (key);


--
-- Name: db_change_notifications db_change_notifications_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.db_change_notifications
    ADD CONSTRAINT db_change_notifications_pkey PRIMARY KEY (id);


--
-- Name: failed_jobs failed_jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.failed_jobs
    ADD CONSTRAINT failed_jobs_pkey PRIMARY KEY (id);


--
-- Name: failed_jobs failed_jobs_uuid_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.failed_jobs
    ADD CONSTRAINT failed_jobs_uuid_unique UNIQUE (uuid);


--
-- Name: job_batches job_batches_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.job_batches
    ADD CONSTRAINT job_batches_pkey PRIMARY KEY (id);


--
-- Name: jobs jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_pkey PRIMARY KEY (id);


--
-- Name: migrations migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.migrations
    ADD CONSTRAINT migrations_pkey PRIMARY KEY (id);


--
-- Name: notification_user notification_user_notification_id_user_id_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.notification_user
    ADD CONSTRAINT notification_user_notification_id_user_id_unique UNIQUE (notification_id, user_id);


--
-- Name: notification_user notification_user_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.notification_user
    ADD CONSTRAINT notification_user_pkey PRIMARY KEY (id);


--
-- Name: notifications notifications_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.notifications
    ADD CONSTRAINT notifications_pkey PRIMARY KEY (id);


--
-- Name: password_reset_tokens password_reset_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.password_reset_tokens
    ADD CONSTRAINT password_reset_tokens_pkey PRIMARY KEY (email);


--
-- Name: personal_access_tokens personal_access_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.personal_access_tokens
    ADD CONSTRAINT personal_access_tokens_pkey PRIMARY KEY (id);


--
-- Name: personal_access_tokens personal_access_tokens_token_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.personal_access_tokens
    ADD CONSTRAINT personal_access_tokens_token_unique UNIQUE (token);


--
-- Name: product_compositions product_compositions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.product_compositions
    ADD CONSTRAINT product_compositions_pkey PRIMARY KEY (id);


--
-- Name: production_calculations production_calculations_article_id_calculation_date_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.production_calculations
    ADD CONSTRAINT production_calculations_article_id_calculation_date_unique UNIQUE (article_id, calculation_date);


--
-- Name: production_calculations production_calculations_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.production_calculations
    ADD CONSTRAINT production_calculations_pkey PRIMARY KEY (id);


--
-- Name: production_orders production_orders_code_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.production_orders
    ADD CONSTRAINT production_orders_code_unique UNIQUE (code);


--
-- Name: production_orders production_orders_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.production_orders
    ADD CONSTRAINT production_orders_pkey PRIMARY KEY (id);


--
-- Name: sales_forecasts sales_forecasts_article_id_forecast_date_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sales_forecasts
    ADD CONSTRAINT sales_forecasts_article_id_forecast_date_unique UNIQUE (article_id, forecast_date);


--
-- Name: sales_forecasts sales_forecasts_forecast_code_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sales_forecasts
    ADD CONSTRAINT sales_forecasts_forecast_code_unique UNIQUE (forecast_code);


--
-- Name: sales_forecasts sales_forecasts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sales_forecasts
    ADD CONSTRAINT sales_forecasts_pkey PRIMARY KEY (id);


--
-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);


--
-- Name: stock_details stock_details_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.stock_details
    ADD CONSTRAINT stock_details_pkey PRIMARY KEY (id);


--
-- Name: stock_movements stock_movements_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.stock_movements
    ADD CONSTRAINT stock_movements_pkey PRIMARY KEY (id);


--
-- Name: users users_email_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_unique UNIQUE (email);


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

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


--
-- Name: variance_alerts_sent variance_alerts_sent_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.variance_alerts_sent
    ADD CONSTRAINT variance_alerts_sent_pkey PRIMARY KEY (id);


--
-- Name: variance_alerts_sent variance_alerts_sent_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.variance_alerts_sent
    ADD CONSTRAINT variance_alerts_sent_unique UNIQUE (article_id, week_start);


--
-- Name: warehouses warehouses_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.warehouses
    ADD CONSTRAINT warehouses_pkey PRIMARY KEY (id);


--
-- Name: warehouses warehouses_warehouse_code_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.warehouses
    ADD CONSTRAINT warehouses_warehouse_code_unique UNIQUE (warehouse_code);


--
-- Name: jobs_queue_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX jobs_queue_index ON public.jobs USING btree (queue);


--
-- Name: notification_user_user_id_read_at_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX notification_user_user_id_read_at_index ON public.notification_user USING btree (user_id, read_at);


--
-- Name: notifications_source_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX notifications_source_index ON public.notifications USING btree (source);


--
-- Name: notifications_type_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX notifications_type_index ON public.notifications USING btree (type);


--
-- Name: notifications_user_id_read_at_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX notifications_user_id_read_at_index ON public.notifications USING btree (user_id, read_at);


--
-- Name: personal_access_tokens_tokenable_type_tokenable_id_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX personal_access_tokens_tokenable_type_tokenable_id_index ON public.personal_access_tokens USING btree (tokenable_type, tokenable_id);


--
-- Name: production_calculations_calculation_date_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX production_calculations_calculation_date_index ON public.production_calculations USING btree (calculation_date);


--
-- Name: sessions_last_activity_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sessions_last_activity_index ON public.sessions USING btree (last_activity);


--
-- Name: sessions_user_id_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sessions_user_id_index ON public.sessions USING btree (user_id);


--
-- Name: variance_alerts_sent_created_at_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX variance_alerts_sent_created_at_index ON public.variance_alerts_sent USING btree (created_at);


--
-- Name: variance_alerts_sent_week_start_index; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX variance_alerts_sent_week_start_index ON public.variance_alerts_sent USING btree (week_start);


--
-- Name: articles articles_batch_size_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER articles_batch_size_trigger AFTER UPDATE ON public.articles FOR EACH ROW EXECUTE FUNCTION public.update_product_compositions_on_article_change();


--
-- Name: articles articles_calculation_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER articles_calculation_trigger AFTER INSERT OR UPDATE OF stock, target_coverage_months, batch_size ON public.articles FOR EACH ROW EXECUTE FUNCTION public.trigger_calculate_production();


--
-- Name: product_compositions product_compositions_quantity_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER product_compositions_quantity_trigger BEFORE INSERT OR UPDATE ON public.product_compositions FOR EACH ROW EXECUTE FUNCTION public.calculate_quantity_per_lot();


--
-- Name: production_calculations production_calculations_change_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER production_calculations_change_trigger AFTER INSERT OR DELETE OR UPDATE OF initial_coverage ON public.production_calculations FOR EACH ROW EXECUTE FUNCTION public.log_table_change();


--
-- Name: production_calculations production_calculations_priority_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER production_calculations_priority_trigger BEFORE INSERT OR UPDATE ON public.production_calculations FOR EACH ROW EXECUTE FUNCTION public.trigger_update_priority();


--
-- Name: sales_forecasts sales_forecasts_budget_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER sales_forecasts_budget_trigger AFTER INSERT OR DELETE OR UPDATE ON public.sales_forecasts FOR EACH ROW EXECUTE FUNCTION public.trigger_update_monthly_budget();


--
-- Name: sales_forecasts trigger_sales_forecasts_variance; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trigger_sales_forecasts_variance BEFORE INSERT OR UPDATE ON public.sales_forecasts FOR EACH ROW EXECUTE FUNCTION public.calculate_sales_forecast_variance();


--
-- Name: stock_movements trigger_stock_movements_variance; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trigger_stock_movements_variance AFTER INSERT OR DELETE OR UPDATE ON public.stock_movements FOR EACH ROW EXECUTE FUNCTION public.calculate_sales_forecast_variance();


--
-- Name: stock_details trigger_update_article_stock_after_delete; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trigger_update_article_stock_after_delete AFTER DELETE ON public.stock_details FOR EACH ROW EXECUTE FUNCTION public.update_article_stock();


--
-- Name: stock_details trigger_update_article_stock_after_insert; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trigger_update_article_stock_after_insert AFTER INSERT ON public.stock_details FOR EACH ROW EXECUTE FUNCTION public.update_article_stock();


--
-- Name: stock_details trigger_update_article_stock_after_update; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trigger_update_article_stock_after_update AFTER UPDATE ON public.stock_details FOR EACH ROW EXECUTE FUNCTION public.update_article_stock();


--
-- Name: articles articles_article_family_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.articles
    ADD CONSTRAINT articles_article_family_id_foreign FOREIGN KEY (article_family_id) REFERENCES public.article_families(id) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- Name: notification_user notification_user_notification_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.notification_user
    ADD CONSTRAINT notification_user_notification_id_foreign FOREIGN KEY (notification_id) REFERENCES public.notifications(id) ON DELETE CASCADE;


--
-- Name: notification_user notification_user_user_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.notification_user
    ADD CONSTRAINT notification_user_user_id_foreign FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: notifications notifications_user_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.notifications
    ADD CONSTRAINT notifications_user_id_foreign FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: product_compositions product_compositions_component_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.product_compositions
    ADD CONSTRAINT product_compositions_component_article_id_foreign FOREIGN KEY (component_article_id) REFERENCES public.articles(id) ON DELETE CASCADE;


--
-- Name: product_compositions product_compositions_parent_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.product_compositions
    ADD CONSTRAINT product_compositions_parent_article_id_foreign FOREIGN KEY (parent_article_id) REFERENCES public.articles(id) ON DELETE CASCADE;


--
-- Name: production_calculations production_calculations_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.production_calculations
    ADD CONSTRAINT production_calculations_article_id_foreign FOREIGN KEY (article_id) REFERENCES public.articles(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: production_orders production_orders_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.production_orders
    ADD CONSTRAINT production_orders_article_id_foreign FOREIGN KEY (article_id) REFERENCES public.articles(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: production_orders production_orders_selected_sf_stock_detail_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.production_orders
    ADD CONSTRAINT production_orders_selected_sf_stock_detail_id_foreign FOREIGN KEY (selected_sf_stock_detail_id) REFERENCES public.stock_details(id);


--
-- Name: sales_forecasts sales_forecasts_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sales_forecasts
    ADD CONSTRAINT sales_forecasts_article_id_foreign FOREIGN KEY (article_id) REFERENCES public.articles(id) ON DELETE CASCADE;


--
-- Name: sales_forecasts sales_forecasts_warehouse_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.sales_forecasts
    ADD CONSTRAINT sales_forecasts_warehouse_id_foreign FOREIGN KEY (warehouse_id) REFERENCES public.warehouses(id) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- Name: stock_details stock_details_article_family_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.stock_details
    ADD CONSTRAINT stock_details_article_family_id_foreign FOREIGN KEY (article_family_id) REFERENCES public.article_families(id) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- Name: stock_details stock_details_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.stock_details
    ADD CONSTRAINT stock_details_article_id_foreign FOREIGN KEY (article_id) REFERENCES public.articles(id) ON DELETE CASCADE;


--
-- Name: stock_details stock_details_warehouse_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.stock_details
    ADD CONSTRAINT stock_details_warehouse_id_foreign FOREIGN KEY (warehouse_id) REFERENCES public.warehouses(id) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- Name: stock_movements stock_movements_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.stock_movements
    ADD CONSTRAINT stock_movements_article_id_foreign FOREIGN KEY (article_id) REFERENCES public.articles(id) ON DELETE CASCADE;


--
-- Name: stock_movements stock_movements_warehouse_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.stock_movements
    ADD CONSTRAINT stock_movements_warehouse_id_foreign FOREIGN KEY (warehouse_id) REFERENCES public.warehouses(id) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- Name: variance_alerts_sent variance_alerts_sent_article_id_foreign; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.variance_alerts_sent
    ADD CONSTRAINT variance_alerts_sent_article_id_foreign FOREIGN KEY (article_id) REFERENCES public.articles(id) ON DELETE CASCADE;


--
--

