model.sql

drop table if exists succursale_saq cascade;
create table succursale_saq (
    no_succursale_saq text primary key, -- text to allow searching
    adresse text,
    ville text
);

drop table if exists representant cascade;
create table representant (
    representant_id serial primary key,
    representant_nom text,
    courriel text,
    no_tel text,
    est_actif bool default true
);

drop table if exists timbre_restaurateur cascade;
create table timbre_restaurateur (
    format_timbre text primary key,
    montant_timbre numeric(1000, 2)
);

drop table if exists client cascade;
create table client (
    no_client serial primary key,
    nom_social text,
    no_civique text,
    rue text,
    ville text,
    province text,
    code_postal text check (code_postal ~ $re$^[A-Z]\d[A-Z] \d[A-Z]\d$$re$), -- 'H0H 0H0'
    nom_responsable text,
    no_tel text,
    no_fax text,
    no_tel_personnel text,
    no_cellulaire text,
    courriel text,
    type_client text check (type_client in ('restaurant', 'particulier')),
    specialite text,
    representant_id integer references representant,
    expedition text check (expedition in ('direct', 'pickup', 'succursale')),
    no_succursale_saq text references succursale_saq,
    note_client text,
    no_client_saq integer,
    no_civique_fact text,
    rue_fact text,
    ville_fact text,
    province_fact text,
    code_postal_fact text check (code_postal_fact ~ $re$^[A-Z]\d[A-Z] \d[A-Z]\d$$re$), -- 'H0H 0H0'
    jours_livraison text[],
    date_ouverture_dossier date,
    mode_facturation text check (mode_facturation in ('courriel', 'poste')) default 'courriel',
    mode_facturation_note text, -- courriel ou autre (devrait etre renomme)
    est_actif bool default true,
    a_probleme_comptabilite bool default false
);

drop table if exists producteur cascade;
create table producteur (
    no_producteur serial primary key,
    nom_producteur text not null,
    nom_domaine text not null,
    no_civique text,
    rue text,
    ville text,
    comte text,
    code_postal text check (code_postal ~ $re$^[A-Z]\d[A-Z] \d[A-Z]\d$|^\d{4,5}$$re$), -- 'H0H 0H0'|'1234'|'12345'
    region text,
    pays text,
    no_tel text,
    no_fax text,
    note_producteur text,
    nom_responsable text,
    courriel_comptabilite text,
    courriel text,
    langue_correspondance text check (langue_correspondance in ('fr', 'en')) default 'fr'
);

drop table if exists produit cascade;
create table produit (
    no_produit_interne serial primary key,
    no_producteur integer not null references producteur,
    type_vin text not null unique,
    --nom_domaine text, transferred to producteur
    format text not null references timbre_restaurateur (format_timbre),
    couleur text not null,
    quantite_par_caisse integer not null,
    --pays text, transferred to producteur
    est_actif bool default true,
    est_en_dispo_reduite bool default false,
    nom_domaine_produit text,
    region_produit text,
    categorie text check (categorie in ('Importation privée', 'SAQ')) default 'Importation privée',
    commission_producteur numeric check (commission_producteur between 0 and 1),
    prix_caisse_producteur numeric(1000, 2),
    prix_net_caisse_producteur numeric(1000, 2), -- has preseance over prix_caisse_producteur
    prix_caisse_producteur_devise text check (prix_caisse_producteur_devise in ('CAD', 'USD', 'AUD', 'EUR')),
    -- WARNING: those 3 next fields are only defined if categorie==SAQ
    prix_particulier_saq numeric(1000, 2), -- equivalent to prix_coutant
    prix_restaurant_saq numeric(1000, 2)   -- prix_particulier - taxes
    millesime_saq integer
);

drop table if exists cepage cascade;
create table cepage (
    cepage_id serial primary key,
    cepage_nom text
);

drop table if exists produit_cepage cascade;
create table produit_cepage (
    produit_cepage_id serial primary key,
    no_produit_interne integer not null references produit on delete cascade,
    cepage_id integer not null references cepage,
    list_rank integer,
    proportion integer check (proportion between 1 and 100) -- %
);

drop table if exists produit_fiche cascade;
create table produit_fiche (
    produit_fiche_id serial primary key,
    no_produit_interne integer not null references produit on delete cascade,
    langue text check (langue in ('fr', 'en')),
    historique text,
    reconnaissance text,
    appellation text,
    oeil text,
    nez text,
    bouche text,
    accords text,
    commentaires text,
    service text,
    garde text,
    en_une_ligne text
);

drop table if exists client_produit cascade;
create table client_produit (
    client_produit_id serial primary key,
    no_client integer not null references client on delete cascade,
    no_produit_interne integer not null references produit
);

drop table if exists commande cascade;
create table commande (
    no_commande_facture serial primary key,
    no_client integer not null not null references client,
    date_commande date,
    expedition text check (expedition in ('direct', 'pickup', 'succursale')),
    no_succursale_saq text references succursale_saq,
    date_pickup date,
    date_direct date,
    date_envoi_saq date,
    sous_total numeric(1000, 2), -- sans taxes
    montant numeric(1000, 2), -- avec taxes
    tps numeric(1000, 2),
    tvq numeric(1000, 2),
    note_commande text,
    jour_livraison text,
    facture_est_envoyee bool default false,
    bon_de_commande_est_envoye bool default false,
    bon_de_commande_heure_envoi timestamp,
    no_commande_saq integer
);

-- IMPORTANT: I renamed the original commande_produit table to commande_item, to put
-- emphasis on the fact that it works at the inventaire record level (no_produit_saq),
-- not at the produit record level (no_produit_interne); in the app code, when manipulating
-- data related to this table, I distinguish between "items", which can only correspond to a
-- single row, and "produit", which can correspond to many.

drop table if exists commande_item cascade;
create table commande_item (
    commande_item_id serial primary key,
    no_commande_facture integer not null references commande,
    no_produit_interne integer not null references produit,
    no_produit_saq integer, -- warning: Roucet BOs have this set to 0 (which is bad because it then joins with inventaire items)
    no_demande_saq text,
    quantite_caisse integer,
    quantite_bouteille integer,
    statut_item text check (statut_item in ('OK', 'BO')),
    --no_client integer, pretty sure this is not used, so to remove
    commission numeric check (commission between 0 and 1), -- added
    montant_commission numeric(1000, 2) -- sans taxes
);

drop table if exists commande_internationale cascade;
create table commande_internationale (
    no_commande_internationale serial primary key,
    no_producteur integer not null not null references producteur,
    date_commande_internationale date,
    no_commande_saq text not null unique check (no_commande_saq ~ $re$^(\d{6} OI)|(OP \d{6})$$re$), -- '123456 IO' ou 'OP 123456'
    no_demande_saq text, -- null when est_roucet_couture is true
    montant_item_total numeric(1000, 2), -- total of all montant for the assoc. commande_internationale_item's
    montant_paiement_total numeric(1000, 2) default 0,
    montant_solde numeric(1000, 2), -- equals to montant_item_total when CI is created
    devise text check (devise in ('CAD', 'USD', 'AUD', 'EUR')),
    facture_est_envoyee bool default false,
    est_roucet_couture bool default false
);

drop table if exists commande_internationale_item cascade;
create table commande_internationale_item (
    commande_internationale_item_id serial primary key,
    no_commande_internationale integer not null references commande_internationale,
    no_produit_interne integer not null references produit,
    quantite_caisse integer,
    -- vv these values below are copied from produit because they could change there vv
    type_vin text,
    format text,
    couleur text,
    millesime integer, -- can be either inventaire.millesime, produit.millesime_saq or user-defined
    no_produit_saq integer,
    prix_caisse numeric(1000, 2), -- corresponds to coalesce(produit.prix_next_caisse_producteur, produit.prix_caisse_producteur)
    devise_item text check (devise_item in ('CAD', 'USD', 'AUD', 'EUR')), -- corresponds to produit.prix_caisse_producteur_devise
    commission numeric check (commission between 0 and 1), -- corresponds to produit.commission_producteur
    montant_item numeric(1000, 2) -- quantite_caisse * prix_caisse * commission
);

drop table if exists commande_internationale_paiement cascade;
create table commande_internationale_paiement (
    commande_internationale_paiement_id serial primary key,
    no_commande_internationale integer not null references commande_internationale,
    montant_paiement numeric(1000, 2),
    devise_paiement text check (devise_paiement in ('CAD', 'USD', 'AUD', 'EUR')),
    date_paiement date,
    methode_paiement text check (methode_paiement in ('chèque', 'transfert de fonds')),
    cheque_numero text
);

drop table if exists inventaire cascade;
create table inventaire (
    no_inventaire serial primary key,
    no_produit_interne integer not null references produit,
    no_produit_saq integer,
    no_demande_saq text not null,
    quantite_commandee_en_caisses integer not null,
    quantite_recue_en_bouteilles integer,
    date_commande_inventaire date not null,
    date_recue date,
    prix_coutant numeric(1000, 2) not null,
    millesime integer,
    statut_inventaire text check (statut_inventaire in ('en attente', 'en réserve', 'actif', 'inactif')) not null,
    solde_bouteille integer, -- en # de bouteilles
    solde_caisse integer,
    prix_restaurant numeric(1000, 2) not null, -- prix_coutant / 1.14975 + 16% + timbre(format)
    prix_particulier numeric(1000, 2) not null, -- prix_coutant + 23%
    last_update_hash text
);

drop table if exists inventaire_note cascade;
create table inventaire_note (
    inventaire_note_id serial primary key,
    note_inventaire text
);

drop table if exists backorder cascade;
create table backorder (
    backorder_id serial primary key,
    --commande_item_id integer not null references commande_item on delete cascade
    no_produit_interne integer not null references produit,
    no_client integer not null references client,
    date_bo date,
    quantite_caisse integer,
    quantite_bouteille integer
);

drop table if exists usager cascade;
create table usager (
    usager_id serial primary key,
    usager_nom text not null unique,
    mdp_hash text not null,
    representant_id integer references representant
);

-- functions
create or replace function age_in_days(date) returns int as $$
    select date_part('day', now() - $1)::int
$$ language sql immutable;

create or replace function verifier_inventaire_demande_producteur_unique()
returns trigger as $body$
begin
    if (select count(distinct no_producteur)
        from inventaire i, produit p
        where i.no_produit_interne = p.no_produit_interne
        and i.no_demande_saq = new.no_demande_saq) > 1 then
        raise exception 'les produits associés la demande % proviennent de plus d''un producteur', new.no_demande_saq;
    else
        return new;
    end if;
end;
$body$
language plpgsql;

create trigger verifier_inventaire_demande_producteur_unique_trigger before insert or update on inventaire
for each row execute procedure verifier_inventaire_demande_producteur_unique();

-- indexes
create index on inventaire (no_produit_interne);
create index on commande (no_client);
create index on client_produit (no_produit_interne);
create index on client_produit (no_client);