ddl.sql

-- public.categories definition
-- Drop table
-- DROP TABLE public.categories;
CREATE TABLE public.categories (
    id serial4 NOT NULL,
    "name" varchar(50) NULL,
    created_at timestamp NOT NULL DEFAULT now(),
    CONSTRAINT categories_pkey PRIMARY KEY (id)
);
-- public.deliveries definition
-- Drop table
-- DROP TABLE public.deliveries;
CREATE TABLE public.deliveries (
    id serial4 NOT NULL,
    "name" varchar NOT NULL,
    fee int4 NOT NULL DEFAULT 0,
    CONSTRAINT deliveries_pkey PRIMARY KEY (id)
);
-- public.payments definition
-- Drop table
-- DROP TABLE public.payments;
CREATE TABLE public.payments (
    code varchar(10) NOT NULL,
    "name" varchar(255) NULL,
    min_amount int4 NOT NULL DEFAULT 0,
    max_amount int4 NOT NULL DEFAULT 0,
    fee int4 NOT NULL DEFAULT 0,
    id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY(
        INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE
    ),
    CONSTRAINT payments_id_key UNIQUE (id),
    CONSTRAINT payments_pk PRIMARY KEY (code)
);
-- public.product_size definition
-- Drop table
-- DROP TABLE public.product_size;
CREATE TABLE public.product_size (
    id serial4 NOT NULL,
    "name" varchar(4) NOT NULL,
    price numeric(3, 2) NOT NULL DEFAULT 0,
    CONSTRAINT product_size_pkey PRIMARY KEY (id)
);
-- public.roles definition
-- Drop table
-- DROP TABLE public.roles;
CREATE TABLE public.roles (
    id serial4 NOT NULL,
    "name" varchar NOT NULL,
    CONSTRAINT roles_pk PRIMARY KEY (id)
);
-- public.status definition
-- Drop table
-- DROP TABLE public.status;
CREATE TABLE public.status (
    id serial4 NOT NULL,
    "name" varchar NULL,
    CONSTRAINT status_pk PRIMARY KEY (id)
);
-- public.products definition
-- Drop table
-- DROP TABLE public.products;
CREATE TABLE public.products (
    id serial4 NOT NULL,
    "name" varchar(55) NULL,
    price int4 NOT NULL DEFAULT 0,
    created_at timestamp NOT NULL DEFAULT now(),
    category_id int4 NOT NULL DEFAULT 0,
    img varchar(255) NULL,
    "desc" text NULL,
    CONSTRAINT products_pkey PRIMARY KEY (id),
    CONSTRAINT product_category_fk FOREIGN KEY (category_id) REFERENCES public.categories(id)
);
-- public.promo definition
-- Drop table
-- DROP TABLE public.promo;
CREATE TABLE public.promo (
    id serial4 NOT NULL,
    "name" varchar(100) NOT NULL,
    "desc" text NOT NULL,
    discount int4 NULL,
    start_date date NULL,
    end_date date NULL,
    coupon_code varchar(25) NOT NULL,
    product_id int8 NULL,
    img varchar NULL,
    CONSTRAINT promo_disc_check CHECK (
        (
            (discount >= 1)
            AND (discount <= 100)
        )
    ),
    CONSTRAINT promo_pkey PRIMARY KEY (id),
    CONSTRAINT promo_fk FOREIGN KEY (product_id) REFERENCES public.products(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- public.users definition
-- Drop table
-- DROP TABLE public.users;
CREATE TABLE public.users (
    id serial4 NOT NULL,
    email varchar(50) NOT NULL,
    "password" varchar(255) NOT NULL,
    phone_number varchar(18) NULL,
    role_id int4 NOT NULL DEFAULT 1,
    CONSTRAINT phone_number_unique UNIQUE (phone_number),
    CONSTRAINT unique_users_email UNIQUE (email),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_role FOREIGN KEY (role_id) REFERENCES public.roles(id)
);
-- public.carts definition
-- Drop table
-- DROP TABLE public.carts;
CREATE TABLE public.carts (
    user_id int4 NOT NULL,
    product_id int4 NOT NULL,
    size_id int4 NOT NULL,
    count int4 NOT NULL DEFAULT 1,
    id serial4 NOT NULL,
    CONSTRAINT carts_pk PRIMARY KEY (id),
    CONSTRAINT cart_product_fk FOREIGN KEY (product_id) REFERENCES public.products(id),
    CONSTRAINT cart_size_fk FOREIGN KEY (size_id) REFERENCES public.product_size(id),
    CONSTRAINT cart_user_fk FOREIGN KEY (user_id) REFERENCES public.users(id)
);
-- public.fcm_tokens definition
-- Drop table
-- DROP TABLE public.fcm_tokens;
CREATE TABLE public.fcm_tokens (
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    "token" varchar NOT NULL,
    user_id int4 NOT NULL,
    expired_time timestamptz NOT NULL DEFAULT now() + '00:10:00'::interval,
    CONSTRAINT no_duplicate_token_in_1_id UNIQUE (token, user_id),
    CONSTRAINT fcm_tokens_fk FOREIGN KEY (user_id) REFERENCES public.users(id)
);
-- public.reset_password definition
-- Drop table
-- DROP TABLE public.reset_password;
CREATE TABLE public.reset_password (
    id serial4 NOT NULL,
    user_id int4 NOT NULL,
    verify varchar NOT NULL,
    code varchar(8) NOT NULL,
    expired_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP + '00:10:00'::interval),
    CONSTRAINT reset_password_pk PRIMARY KEY (id),
    CONSTRAINT reset_password_fk FOREIGN KEY (user_id) REFERENCES public.users(id)
);
-- public.transactions definition
-- Drop table
-- DROP TABLE public.transactions;
CREATE TABLE public.transactions (
    id serial4 NOT NULL,
    user_id int4 NOT NULL DEFAULT 0,
    promo_id int4 NOT NULL DEFAULT 0,
    shipping_address varchar(255) NULL,
    transaction_time timestamp NULL DEFAULT now(),
    notes text NULL,
    delivery_id int4 NOT NULL DEFAULT 1,
    status_id int4 NOT NULL DEFAULT 1,
    payment_id int4 NULL,
    grand_total int4 NULL DEFAULT 0,
    CONSTRAINT history_pkey PRIMARY KEY (id),
    CONSTRAINT transactions_delivery_id_fkey FOREIGN KEY (delivery_id) REFERENCES public.deliveries(id),
    CONSTRAINT transactions_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES public.payments(id),
    CONSTRAINT transactions_promo_id_fkey FOREIGN KEY (promo_id) REFERENCES public.promo(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT transactions_status_id_fkey FOREIGN KEY (status_id) REFERENCES public.status(id),
    CONSTRAINT transactions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
);
-- public.user_profile definition
-- Drop table
-- DROP TABLE public.user_profile;
CREATE TABLE public.user_profile (
    user_id int4 NOT NULL DEFAULT 0,
    display_name varchar(50) NULL,
    first_name varchar(50) NULL,
    last_name varchar(50) NULL,
    address text NULL,
    birthdate date NULL,
    img varchar(255) NULL,
    created_at timestamp NOT NULL DEFAULT now(),
    gender int4 NOT NULL DEFAULT 1,
    CONSTRAINT profile_user_id_as_pk PRIMARY KEY (user_id),
    CONSTRAINT user_profile_fk FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE
);
-- public.transaction_product_size definition
-- Drop table
-- DROP TABLE public.transaction_product_size;
CREATE TABLE public.transaction_product_size (
    transaction_id int4 NOT NULL,
    product_id int4 NOT NULL DEFAULT 0,
    size_id int4 NOT NULL DEFAULT 0,
    qty varchar NOT NULL DEFAULT 1,
    subtotal int4 NOT NULL DEFAULT 0,
    id serial4 NOT NULL,
    CONSTRAINT transaction_product_size_pk PRIMARY KEY (id),
    CONSTRAINT tps_productid FOREIGN KEY (size_id) REFERENCES public.product_size(id),
    CONSTRAINT tps_sizeid FOREIGN KEY (product_id) REFERENCES public.products(id),
    CONSTRAINT transaction_product_size_fk FOREIGN KEY (transaction_id) REFERENCES public.transactions(id)
);