
CREATE SCHEMA IF NOT EXISTS commerce;

CREATE TABLE IF NOT EXISTS commerce.citas (
    LIKE public.citas INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS commerce.ventas (
    LIKE public.ventas INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS commerce.transacciones (
    LIKE public.transacciones INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS commerce.comprobantes_transacciones (
    LIKE public.comprobantes_transacciones INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS commerce.modelos_planes_pago (
    LIKE public.modelos_planes_pago INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS commerce.planes_pago (
    LIKE public.planes_pago INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS commerce.pagos_planes (
    LIKE public.pagos_planes INCLUDING ALL
);

CREATE TABLE IF NOT EXISTS commerce.configuraciones_planes_pago (
    LIKE public.configuraciones_planes_pago INCLUDING ALL
);

TRUNCATE TABLE
    commerce.configuraciones_planes_pago,
    commerce.pagos_planes,
    commerce.planes_pago,
    commerce.comprobantes_transacciones,
    commerce.transacciones,
    commerce.ventas,
    commerce.modelos_planes_pago,
    commerce.citas
;

ALTER TABLE IF EXISTS commerce.ventas DROP CONSTRAINT IF EXISTS fk_ventas_plan_pago;
ALTER TABLE IF EXISTS commerce.transacciones DROP CONSTRAINT IF EXISTS fk_transacciones_plan_pago;
ALTER TABLE IF EXISTS commerce.transacciones DROP CONSTRAINT IF EXISTS fk_transacciones_venta;
ALTER TABLE IF EXISTS commerce.comprobantes_transacciones DROP CONSTRAINT IF EXISTS fk_comprobante_transaccion;
ALTER TABLE IF EXISTS commerce.comprobantes_transacciones DROP CONSTRAINT IF EXISTS fk_comprobantes_venta;
ALTER TABLE IF EXISTS commerce.planes_pago DROP CONSTRAINT IF EXISTS fk_planes_pago_transaccion;
ALTER TABLE IF EXISTS commerce.planes_pago DROP CONSTRAINT IF EXISTS fk_planes_pago_venta;
ALTER TABLE IF EXISTS commerce.pagos_planes DROP CONSTRAINT IF EXISTS fk_pago_plan;

INSERT INTO commerce.citas
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.citas;

INSERT INTO commerce.modelos_planes_pago
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.modelos_planes_pago;

INSERT INTO commerce.configuraciones_planes_pago
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.configuraciones_planes_pago;

INSERT INTO commerce.ventas
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.ventas;

INSERT INTO commerce.transacciones
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.transacciones;

INSERT INTO commerce.comprobantes_transacciones
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.comprobantes_transacciones;

INSERT INTO commerce.planes_pago
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.planes_pago;

INSERT INTO commerce.pagos_planes
OVERRIDING SYSTEM VALUE
SELECT
    *
FROM public.pagos_planes;

CREATE SEQUENCE IF NOT EXISTS commerce.citas_id_cita_seq
    AS integer
    OWNED BY commerce.citas.id_cita;

ALTER TABLE commerce.citas
    ALTER COLUMN id_cita
    DROP DEFAULT;

ALTER TABLE commerce.citas
    ALTER COLUMN id_cita
    SET DEFAULT nextval('commerce.citas_id_cita_seq'::regclass);

ALTER SEQUENCE commerce.citas_id_cita_seq
    OWNED BY commerce.citas.id_cita;

SELECT setval(
    'commerce.citas_id_cita_seq',
    COALESCE((SELECT MAX(id_cita) FROM commerce.citas), 1),
    COALESCE((SELECT MAX(id_cita) FROM commerce.citas), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS commerce.ventas_id_venta_seq
    AS integer
    OWNED BY commerce.ventas.id_venta;

ALTER TABLE commerce.ventas
    ALTER COLUMN id_venta
    DROP DEFAULT;

ALTER TABLE commerce.ventas
    ALTER COLUMN id_venta
    SET DEFAULT nextval('commerce.ventas_id_venta_seq'::regclass);

SELECT setval(
    pg_get_serial_sequence('commerce.ventas', 'id_venta'),
    COALESCE((SELECT MAX(id_venta) FROM commerce.ventas), 1),
    COALESCE((SELECT MAX(id_venta) FROM commerce.ventas), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS commerce.transacciones_id_transaccion_seq
    AS integer
    OWNED BY commerce.transacciones.id_transaccion;

ALTER TABLE commerce.transacciones
    ALTER COLUMN id_transaccion
    DROP IDENTITY IF EXISTS;

ALTER TABLE commerce.transacciones
    ALTER COLUMN id_transaccion
    DROP DEFAULT;

ALTER TABLE commerce.transacciones
    ALTER COLUMN id_transaccion
    SET DEFAULT nextval('commerce.transacciones_id_transaccion_seq'::regclass);

ALTER SEQUENCE commerce.transacciones_id_transaccion_seq
    OWNED BY commerce.transacciones.id_transaccion;

SELECT setval(
    'commerce.transacciones_id_transaccion_seq',
    COALESCE((SELECT MAX(id_transaccion) FROM commerce.transacciones), 1),
    COALESCE((SELECT MAX(id_transaccion) FROM commerce.transacciones), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS commerce.comprobantes_transacciones_id_seq
    AS integer
    OWNED BY commerce.comprobantes_transacciones.id;

ALTER TABLE commerce.comprobantes_transacciones
    ALTER COLUMN id
    DROP DEFAULT;

ALTER TABLE commerce.comprobantes_transacciones
    ALTER COLUMN id
    SET DEFAULT nextval('commerce.comprobantes_transacciones_id_seq'::regclass);

SELECT setval(
    pg_get_serial_sequence('commerce.comprobantes_transacciones', 'id'),
    COALESCE((SELECT MAX(id) FROM commerce.comprobantes_transacciones), 1),
    COALESCE((SELECT MAX(id) FROM commerce.comprobantes_transacciones), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS commerce.modelos_planes_pago_id_modelo_seq
    AS integer
    OWNED BY commerce.modelos_planes_pago.id_modelo;

ALTER TABLE commerce.modelos_planes_pago
    ALTER COLUMN id_modelo
    DROP DEFAULT;

ALTER TABLE commerce.modelos_planes_pago
    ALTER COLUMN id_modelo
    SET DEFAULT nextval('commerce.modelos_planes_pago_id_modelo_seq'::regclass);

SELECT setval(
    pg_get_serial_sequence('commerce.modelos_planes_pago', 'id_modelo'),
    COALESCE((SELECT MAX(id_modelo) FROM commerce.modelos_planes_pago), 1),
    COALESCE((SELECT MAX(id_modelo) FROM commerce.modelos_planes_pago), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS commerce.planes_pago_id_plan_seq
    AS integer
    OWNED BY commerce.planes_pago.id_plan;

ALTER TABLE commerce.planes_pago
    ALTER COLUMN id_plan
    DROP DEFAULT;

ALTER TABLE commerce.planes_pago
    ALTER COLUMN id_plan
    SET DEFAULT nextval('commerce.planes_pago_id_plan_seq'::regclass);

SELECT setval(
    pg_get_serial_sequence('commerce.planes_pago', 'id_plan'),
    COALESCE((SELECT MAX(id_plan) FROM commerce.planes_pago), 1),
    COALESCE((SELECT MAX(id_plan) FROM commerce.planes_pago), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS commerce.pagos_planes_id_pago_seq
    AS integer
    OWNED BY commerce.pagos_planes.id_pago;

ALTER TABLE commerce.pagos_planes
    ALTER COLUMN id_pago
    DROP DEFAULT;

ALTER TABLE commerce.pagos_planes
    ALTER COLUMN id_pago
    SET DEFAULT nextval('commerce.pagos_planes_id_pago_seq'::regclass);

SELECT setval(
    pg_get_serial_sequence('commerce.pagos_planes', 'id_pago'),
    COALESCE((SELECT MAX(id_pago) FROM commerce.pagos_planes), 1),
    COALESCE((SELECT MAX(id_pago) FROM commerce.pagos_planes), 0) > 0
);

CREATE SEQUENCE IF NOT EXISTS commerce.configuraciones_planes_pago_id_seq
    AS integer
    OWNED BY commerce.configuraciones_planes_pago.id;

ALTER TABLE commerce.configuraciones_planes_pago
    ALTER COLUMN id
    DROP DEFAULT;

ALTER TABLE commerce.configuraciones_planes_pago
    ALTER COLUMN id
    SET DEFAULT nextval('commerce.configuraciones_planes_pago_id_seq'::regclass);

SELECT setval(
    pg_get_serial_sequence('commerce.configuraciones_planes_pago', 'id'),
    COALESCE((SELECT MAX(id) FROM commerce.configuraciones_planes_pago), 1),
    COALESCE((SELECT MAX(id) FROM commerce.configuraciones_planes_pago), 0) > 0
);

ALTER TABLE commerce.ventas
    DROP CONSTRAINT IF EXISTS fk_ventas_plan_pago;

ALTER TABLE commerce.ventas
    ADD CONSTRAINT fk_ventas_plan_pago
    FOREIGN KEY (id_plan_pago)
    REFERENCES commerce.planes_pago (id_plan)
    ON DELETE SET NULL
    ON UPDATE NO ACTION;

ALTER TABLE commerce.transacciones
    DROP CONSTRAINT IF EXISTS fk_transacciones_plan_pago;

ALTER TABLE commerce.transacciones
    ADD CONSTRAINT fk_transacciones_plan_pago
    FOREIGN KEY (id_plan_pago)
    REFERENCES commerce.planes_pago (id_plan)
    ON DELETE SET NULL
    ON UPDATE NO ACTION;

ALTER TABLE commerce.transacciones
    DROP CONSTRAINT IF EXISTS fk_transacciones_venta;

ALTER TABLE commerce.transacciones
    ADD CONSTRAINT fk_transacciones_venta
    FOREIGN KEY (id_venta)
    REFERENCES commerce.ventas (id_venta)
    ON DELETE SET NULL
    ON UPDATE NO ACTION;

ALTER TABLE commerce.comprobantes_transacciones
    DROP CONSTRAINT IF EXISTS fk_comprobante_transaccion;

ALTER TABLE commerce.comprobantes_transacciones
    ADD CONSTRAINT fk_comprobante_transaccion
    FOREIGN KEY (id_transaccion)
    REFERENCES commerce.transacciones (id_transaccion)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE commerce.comprobantes_transacciones
    DROP CONSTRAINT IF EXISTS fk_comprobantes_venta;

ALTER TABLE commerce.comprobantes_transacciones
    ADD CONSTRAINT fk_comprobantes_venta
    FOREIGN KEY (id_venta)
    REFERENCES commerce.ventas (id_venta)
    ON DELETE SET NULL
    ON UPDATE NO ACTION;

ALTER TABLE commerce.planes_pago
    DROP CONSTRAINT IF EXISTS fk_planes_pago_transaccion;

ALTER TABLE commerce.planes_pago
    ADD CONSTRAINT fk_planes_pago_transaccion
    FOREIGN KEY (id_transaccion_origen)
    REFERENCES commerce.transacciones (id_transaccion)
    ON DELETE SET NULL
    ON UPDATE NO ACTION;

ALTER TABLE commerce.planes_pago
    DROP CONSTRAINT IF EXISTS fk_planes_pago_venta;

ALTER TABLE commerce.planes_pago
    ADD CONSTRAINT fk_planes_pago_venta
    FOREIGN KEY (id_venta)
    REFERENCES commerce.ventas (id_venta)
    ON DELETE SET NULL
    ON UPDATE NO ACTION;

ALTER TABLE commerce.pagos_planes
    DROP CONSTRAINT IF EXISTS fk_pago_plan;

ALTER TABLE commerce.pagos_planes
    ADD CONSTRAINT fk_pago_plan
    FOREIGN KEY (id_plan_pago)
    REFERENCES commerce.planes_pago (id_plan)
    ON DELETE RESTRICT
    ON UPDATE NO ACTION;

ANALYZE commerce.citas;
ANALYZE commerce.ventas;
ANALYZE commerce.transacciones;
ANALYZE commerce.comprobantes_transacciones;
ANALYZE commerce.modelos_planes_pago;
ANALYZE commerce.planes_pago;
ANALYZE commerce.pagos_planes;
ANALYZE commerce.configuraciones_planes_pago;
