-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public IS 'standard public schema';

-- DROP TYPE public.user_status;

CREATE TYPE public.user_status AS ENUM (
	'active',
	'deleted');

-- DROP SEQUENCE public.product_categories_id_seq;

CREATE SEQUENCE public.product_categories_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;

-- Permissions

ALTER SEQUENCE public.product_categories_id_seq OWNER TO postgres;
GRANT ALL ON SEQUENCE public.product_categories_id_seq TO postgres;
-- public."__diesel_schema_migrations" definition

-- Drop table

-- DROP TABLE public."__diesel_schema_migrations";

CREATE TABLE public."__diesel_schema_migrations" (
	"version" varchar(50) NOT NULL,
	run_on timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CONSTRAINT "__diesel_schema_migrations_pkey" PRIMARY KEY (version)
);

-- Permissions

ALTER TABLE public."__diesel_schema_migrations" OWNER TO plum;
GRANT ALL ON TABLE public."__diesel_schema_migrations" TO plum;


-- public.users definition

-- Drop table

-- DROP TABLE public.users;

CREATE TABLE public.users (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	username varchar(50) NOT NULL,
	username_old jsonb NULL,
	"password" text NOT NULL, -- Hashed Password
	date_created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
	date_last_modified timestamp NULL,
	email varchar(254) NOT NULL,
	email_verified bool DEFAULT false NOT NULL,
	verification_token text NULL, -- For verifying emails
	reset_token text NULL, -- For reseting password through email
	reset_token_expires timestamp NULL, -- Reset token expiration date
	CONSTRAINT check_email CHECK (((email)::text ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'::text)),
	CONSTRAINT check_username CHECK (((username)::text ~ '^[a-zA-Z0-9_]+$'::text)),
	CONSTRAINT users_email_ukey UNIQUE (email),
	CONSTRAINT users_id_ukey UNIQUE (id),
	CONSTRAINT users_pk PRIMARY KEY (id)
);
CREATE INDEX idx_users_email ON public.users USING btree (email);

-- Column comments

COMMENT ON COLUMN public.users."password" IS 'Hashed Password';
COMMENT ON COLUMN public.users.verification_token IS 'For verifying emails';
COMMENT ON COLUMN public.users.reset_token IS 'For reseting password through email';
COMMENT ON COLUMN public.users.reset_token_expires IS 'Reset token expiration date';

-- Permissions

ALTER TABLE public.users OWNER TO plum;
GRANT ALL ON TABLE public.users TO plum;


-- public.addresses definition

-- Drop table

-- DROP TABLE public.addresses;

CREATE TABLE public.addresses (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	user_id uuid NOT NULL,
	address_type varchar(20) NULL,
	address_line1 varchar(255) NOT NULL,
	address_line2 varchar(255) NULL,
	city varchar(100) NOT NULL,
	state varchar(100) NOT NULL,
	postal_code varchar(20) NOT NULL,
	country_code bpchar(2) NOT NULL,
	phone varchar(20) NULL,
	is_default_shipping bool DEFAULT false NULL,
	is_default_billing bool DEFAULT false NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz NULL,
	is_active bool DEFAULT true NULL,
	CONSTRAINT addresses_address_type_check CHECK (((address_type)::text = ANY ((ARRAY['billing'::character varying, 'shipping'::character varying])::text[]))),
	CONSTRAINT addresses_pkey PRIMARY KEY (id),
	CONSTRAINT addresses_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE
);
CREATE INDEX idx_addresses_defaults ON public.addresses USING btree (is_default_shipping, is_default_billing);
CREATE INDEX idx_addresses_user ON public.addresses USING btree (user_id);

-- Table Triggers

create trigger address_change_trigger before
update
    on
    public.addresses for each row execute function log_address_changes();

-- Permissions

ALTER TABLE public.addresses OWNER TO postgres;
GRANT ALL ON TABLE public.addresses TO postgres;
GRANT SELECT, DELETE, UPDATE, INSERT ON TABLE public.addresses TO app_user;


-- public.orders definition

-- Drop table

-- DROP TABLE public.orders;

CREATE TABLE public.orders (
	id int4 NOT NULL,
	customer_id uuid NOT NULL,
	shipping_address_id uuid NULL,
	billing_address_id uuid NULL,
	order_status varchar(20) DEFAULT 'pending'::character varying NOT NULL,
	total_amount numeric(10, 2) NOT NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT orders_order_status_check CHECK (((order_status)::text = ANY ((ARRAY['pending'::character varying, 'processing'::character varying, 'shipped'::character varying, 'delivered'::character varying, 'cancelled'::character varying])::text[]))),
	CONSTRAINT orders_pk PRIMARY KEY (id),
	CONSTRAINT orders_billing_address_id_fkey FOREIGN KEY (billing_address_id) REFERENCES public.addresses(id),
	CONSTRAINT orders_shipping_address_id_fkey FOREIGN KEY (shipping_address_id) REFERENCES public.addresses(id)
);
CREATE INDEX idx_orders_customer ON public.orders USING btree (customer_id);
COMMENT ON TABLE public.orders IS 'Orders and their status';

-- Permissions

ALTER TABLE public.orders OWNER TO postgres;
GRANT ALL ON TABLE public.orders TO postgres;
GRANT SELECT, DELETE, UPDATE, INSERT ON TABLE public.orders TO app_user;


-- public.payments definition

-- Drop table

-- DROP TABLE public.payments;

CREATE TABLE public.payments (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	order_id int4 NOT NULL,
	amount numeric(10, 2) NOT NULL,
	payment_method varchar(50) NOT NULL,
	payment_status varchar(20) NULL,
	transaction_id varchar(255) NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT payments_payment_status_check CHECK (((payment_status)::text = ANY ((ARRAY['pending'::character varying, 'completed'::character varying, 'failed'::character varying])::text[]))),
	CONSTRAINT payments_pkey PRIMARY KEY (id),
	CONSTRAINT payments_order_id_fkey FOREIGN KEY (order_id) REFERENCES public.orders(id)
);
CREATE INDEX idx_payments_order ON public.payments USING btree (order_id);

-- Permissions

ALTER TABLE public.payments OWNER TO postgres;
GRANT ALL ON TABLE public.payments TO postgres;


-- public.product_categories definition

-- Drop table

-- DROP TABLE public.product_categories;

CREATE TABLE public.product_categories (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"name" varchar(100) NOT NULL,
	parent_category_id int4 NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	icon_path varchar(255) NULL,
	CONSTRAINT product_categories_name_key UNIQUE (name),
	CONSTRAINT product_categories_pkey PRIMARY KEY (id),
	CONSTRAINT product_categories_parent_category_id_fkey FOREIGN KEY (parent_category_id) REFERENCES public.product_categories(id)
);

-- Permissions

ALTER TABLE public.product_categories OWNER TO postgres;
GRANT ALL ON TABLE public.product_categories TO postgres;
GRANT SELECT, DELETE, UPDATE, INSERT ON TABLE public.product_categories TO app_user;


-- public.products definition

-- Drop table

-- DROP TABLE public.products;

CREATE TABLE public.products (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	sku varchar(50) NOT NULL,
	"name" varchar(255) NOT NULL,
	description text NULL,
	price numeric(10, 2) NOT NULL,
	stock_quantity int4 NOT NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz NULL,
	category_id int4 NOT NULL,
	CONSTRAINT products_pkey PRIMARY KEY (id),
	CONSTRAINT products_price_check CHECK ((price >= (0)::numeric)),
	CONSTRAINT products_sku_key UNIQUE (sku),
	CONSTRAINT products_stock_quantity_check CHECK ((stock_quantity >= 0)),
	CONSTRAINT fk_category_id FOREIGN KEY (category_id) REFERENCES public.product_categories(id)
);

-- Permissions

ALTER TABLE public.products OWNER TO postgres;
GRANT ALL ON TABLE public.products TO postgres;
GRANT SELECT, DELETE, UPDATE, INSERT ON TABLE public.products TO app_user;


-- public.address_history definition

-- Drop table

-- DROP TABLE public.address_history;

CREATE TABLE public.address_history (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	address_id uuid NOT NULL,
	"snapshot" jsonb NOT NULL,
	changed_by uuid NULL,
	changed_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT address_history_pkey PRIMARY KEY (id),
	CONSTRAINT address_history_address_id_fkey FOREIGN KEY (address_id) REFERENCES public.addresses(id),
	CONSTRAINT address_history_changed_by_fkey FOREIGN KEY (changed_by) REFERENCES public.users(id)
);

-- Permissions

ALTER TABLE public.address_history OWNER TO postgres;
GRANT ALL ON TABLE public.address_history TO postgres;


-- public.order_items definition

-- Drop table

-- DROP TABLE public.order_items;

CREATE TABLE public.order_items (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	order_id int4 NOT NULL,
	product_id uuid NOT NULL,
	quantity int4 NOT NULL,
	unit_price numeric(10, 2) NOT NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT order_items_pkey PRIMARY KEY (id),
	CONSTRAINT order_items_quantity_check CHECK ((quantity > 0)),
	CONSTRAINT order_items_order_id_fkey FOREIGN KEY (order_id) REFERENCES public.orders(id),
	CONSTRAINT order_items_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id)
);
CREATE INDEX idx_order_items_order ON public.order_items USING btree (order_id);

-- Permissions

ALTER TABLE public.order_items OWNER TO postgres;
GRANT ALL ON TABLE public.order_items TO postgres;
GRANT SELECT, DELETE, UPDATE, INSERT ON TABLE public.order_items TO app_user;


-- public.product_images definition

-- Drop table

-- DROP TABLE public.product_images;

CREATE TABLE public.product_images (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	product_id uuid NOT NULL,
	"path" varchar(255) NOT NULL,
	alt_text varchar(150) NULL,
	is_primary bool DEFAULT false NULL,
	CONSTRAINT product_images_pkey PRIMARY KEY (id),
	CONSTRAINT product_images_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id)
);

-- Permissions

ALTER TABLE public.product_images OWNER TO postgres;
GRANT ALL ON TABLE public.product_images TO postgres;



-- DROP FUNCTION public.diesel_manage_updated_at(regclass);

CREATE OR REPLACE FUNCTION public.diesel_manage_updated_at(_tbl regclass)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s
                    FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl);
END;
$function$
;

-- Permissions

ALTER FUNCTION public.diesel_manage_updated_at(regclass) OWNER TO plum;
GRANT ALL ON FUNCTION public.diesel_manage_updated_at(regclass) TO plum;

-- DROP FUNCTION public.diesel_set_updated_at();

CREATE OR REPLACE FUNCTION public.diesel_set_updated_at()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF (
        NEW IS DISTINCT FROM OLD AND
        NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at
    ) THEN
        NEW.updated_at := current_timestamp;
    END IF;
    RETURN NEW;
END;
$function$
;

-- Permissions

ALTER FUNCTION public.diesel_set_updated_at() OWNER TO plum;
GRANT ALL ON FUNCTION public.diesel_set_updated_at() TO plum;

-- DROP FUNCTION public.log_address_changes();

CREATE OR REPLACE FUNCTION public.log_address_changes()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO address_history (address_id, snapshot, changed_by)
    VALUES (OLD.id, to_jsonb(OLD), current_setting('app.user_id')::uuid);
    RETURN NEW;
END;
$function$
;

-- Permissions

ALTER FUNCTION public.log_address_changes() OWNER TO postgres;
GRANT ALL ON FUNCTION public.log_address_changes() TO postgres;

-- DROP FUNCTION public.uuid_generate_v1();

CREATE OR REPLACE FUNCTION public.uuid_generate_v1()
 RETURNS uuid
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_generate_v1$function$
;

-- Permissions

ALTER FUNCTION public.uuid_generate_v1() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_generate_v1() TO plum;

-- DROP FUNCTION public.uuid_generate_v1mc();

CREATE OR REPLACE FUNCTION public.uuid_generate_v1mc()
 RETURNS uuid
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_generate_v1mc$function$
;

-- Permissions

ALTER FUNCTION public.uuid_generate_v1mc() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_generate_v1mc() TO plum;

-- DROP FUNCTION public.uuid_generate_v3(uuid, text);

CREATE OR REPLACE FUNCTION public.uuid_generate_v3(namespace uuid, name text)
 RETURNS uuid
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_generate_v3$function$
;

-- Permissions

ALTER FUNCTION public.uuid_generate_v3(uuid, text) OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_generate_v3(uuid, text) TO plum;

-- DROP FUNCTION public.uuid_generate_v4();

CREATE OR REPLACE FUNCTION public.uuid_generate_v4()
 RETURNS uuid
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_generate_v4$function$
;

-- Permissions

ALTER FUNCTION public.uuid_generate_v4() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_generate_v4() TO plum;

-- DROP FUNCTION public.uuid_generate_v5(uuid, text);

CREATE OR REPLACE FUNCTION public.uuid_generate_v5(namespace uuid, name text)
 RETURNS uuid
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_generate_v5$function$
;

-- Permissions

ALTER FUNCTION public.uuid_generate_v5(uuid, text) OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_generate_v5(uuid, text) TO plum;

-- DROP FUNCTION public.uuid_nil();

CREATE OR REPLACE FUNCTION public.uuid_nil()
 RETURNS uuid
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_nil$function$
;

-- Permissions

ALTER FUNCTION public.uuid_nil() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_nil() TO plum;

-- DROP FUNCTION public.uuid_ns_dns();

CREATE OR REPLACE FUNCTION public.uuid_ns_dns()
 RETURNS uuid
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_ns_dns$function$
;

-- Permissions

ALTER FUNCTION public.uuid_ns_dns() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_ns_dns() TO plum;

-- DROP FUNCTION public.uuid_ns_oid();

CREATE OR REPLACE FUNCTION public.uuid_ns_oid()
 RETURNS uuid
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_ns_oid$function$
;

-- Permissions

ALTER FUNCTION public.uuid_ns_oid() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_ns_oid() TO plum;

-- DROP FUNCTION public.uuid_ns_url();

CREATE OR REPLACE FUNCTION public.uuid_ns_url()
 RETURNS uuid
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_ns_url$function$
;

-- Permissions

ALTER FUNCTION public.uuid_ns_url() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_ns_url() TO plum;

-- DROP FUNCTION public.uuid_ns_x500();

CREATE OR REPLACE FUNCTION public.uuid_ns_x500()
 RETURNS uuid
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_ns_x500$function$
;

-- Permissions

ALTER FUNCTION public.uuid_ns_x500() OWNER TO plum;
GRANT ALL ON FUNCTION public.uuid_ns_x500() TO plum;


-- Permissions

GRANT ALL ON SCHEMA public TO pg_database_owner;
GRANT USAGE ON SCHEMA public TO public;