create table artesano(
	codart	int auto_increment not null PRIMARY KEY,
	nomart	varchar(20) not null,
	apeart	varchar(30) not null,
	fecnac	datetime not null,
	fecins	datetime not null,
	depart	varchar(20) not null,
	proart	varchar(20) not null,
	disart	varchar(20) not null,
	estado	char(1) not null
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into artesano values
(1,'Alberto','Fernandez','1980-01-01','2025-01-01','PUNO','SAN RAMON','JULIACA','A');

create table articulo(
	codarti	int auto_increment not null PRIMARY KEY,
	desart	varchar(50) not null,
	estado	char(1) not null
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into articulo values
(1,'GUANTES','A'),
(2,'CHALINAS','A'),
(3,'SOMBREROS','A'),
(4,'GORROS','A');

create table color(
	codcol	int auto_increment not null PRIMARY KEY,
	descol	varchar(50) not null,
	estado	char(1) not null
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into color values
(1,'NATURAL','A');

create table composicion(
	codcom	int auto_increment not null PRIMARY KEY,
	descom	varchar(50) not null,
	estado	char(1) not null
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into composicion values
(1,'100% VICUÑA','A'),
(2,'95% VICUÑA','A');

create table tipo_documento(
	codtipdoc	int auto_increment not null PRIMARY KEY,
	destipdoc	varchar(50) not null,
	estado	char(1) not null
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into tipo_documento values
(1,'DOCUMENTO NACIONAL DE IDENTIDAD','A'),
(2,'CARNÉT DE EXTRANJERIA','A');

create table cliente(
	codcli	int auto_increment not null PRIMARY KEY,
	nomcli	varchar(20) not null,
	apecli	varchar(30) not null,
	codtipdoc	int not null,
	numdoc	char(8) not null,
	corcli	varchar(20) not null,
	celcli	char(9) not null,
	estado	char(1) not null,
	CONSTRAINT fk_codtipdoc
    FOREIGN KEY (codtipdoc) REFERENCES tipo_documento(codtipdoc)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into cliente values
(1,'Frank','Casas',1,'72255884','prueba1@gmail.com','999888777','A');

create table rucssp(
	rucssp	char(12) not null PRIMARY KEY,
	codart	int not null,
	codarti	int not null,
	codcol	int not null,
	codcom	int not null,
	regcapesq	varchar (100) not null,
	actsup	varchar(50) not null,
	actsuppre	varchar(50) not null,
	firdoc	varchar(50) not null,
	estado	char(1) not null,
	CONSTRAINT fk_codart
    FOREIGN KEY (codart) REFERENCES artesano(codart)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
	CONSTRAINT fk_codarti
    FOREIGN KEY (codarti) REFERENCES articulo(codarti)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
	CONSTRAINT fk_codcol
    FOREIGN KEY (codcol) REFERENCES color(codcol)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
	CONSTRAINT fk_codcom
    FOREIGN KEY (codcom) REFERENCES composicion(codcom)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into rucssp values
('D000132-2025',1,1,1,1,'N° 0000426, 000432 (Campaña 2022)','Descerdado N° 00008-2023 (02.06.2023)','N° 000014-2025 (23.06.2025)',
	'José Edwin Diaz Salas','A'),
('D000133-2025',1,2,1,1,'N° 0000513 (Campaña 2023)','Predescerdado N° 000015-2024 (26.06.2024)','N° 000013-2025 (23.06.2025)',
	'José Edwin Diaz Salas','A'),
('D000134-2025',1,3,1,1,'N° 0000426, 000432 (Campaña 2022)','Descerdado N° 00008-2023 (02.06.2023)','N° 000011-2025 (13.06.2025)',
	'José Edwin Diaz Salas','A');

create table rucssp_prenda(
	rucssp	char(12) not null,
	correlativo	int not null,
	largo	numeric(6,3) not null,
	ancho	numeric(6,3) not null,
	cantidad	int not null,
	pesunipre	numeric(6,3) not null,
	linkqr	varchar(100) not null,
 	PRIMARY KEY (rucssp, correlativo),
	CONSTRAINT fk_rucssp
    FOREIGN KEY (rucssp) REFERENCES rucssp(rucssp)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into rucssp_prenda values
('D000132-2025',1,0.15,0.08,2,0.023,''),
('D000132-2025',2,0.16,0.08,2,0.023,''),
('D000132-2025',3,0.16,0.08,2,0.022,''),
('D000132-2025',4,0.18,0.1,2,0.022,''),
('D000132-2025',5,0.18,0.09,2,0.014,''),
('D000133-2025',1,0.15,0.94,1,0.037,''),
('D000133-2025',2,0.16,1.3,1,0.088,''),
('D000133-2025',3,0.16,1.5,1,0.114,''),
('D000133-2025',4,0.18,1.38,1,0.630,''),
('D000133-2025',5,0.18,1.5,1,0.114,''),
('D000134-2025',1,0.33,0.3,1,0.117,''),
('D000134-2025',2,0.35,0.330,1,0.145,''),
('D000134-2025',3,0.35,0.310,1,0.141,''),
('D000134-2025',4,0.32,0.290,1,0.139,''),
('D000134-2025',5,0.34,0.310,1,0.114,'');

create table tipo_usuario(
	codtipusu	int auto_increment not null PRIMARY KEY,
	destipusu	varchar(50) not null,
	estado	char(1) not null
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into tipo_usuario values
(1,'ADMINISTRADOR','A'),
(2,'VENDEDOR','A'),
(3,'AGENTE AEROPUERTO','A');

create table usuario(
	codusu	int auto_increment not null PRIMARY KEY,
	codtipusu	int not null,
	corusu	varchar(50) not null,
	pasusu	varchar(50) not null,
	estado	char(1),
	CONSTRAINT fk_codtipusu
    FOREIGN KEY (codtipusu) REFERENCES tipo_usuario(codtipusu)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into usuario values
(1,1,'jbazans@uni.pe','123','A');

create table venta(
	codven	int auto_increment not null PRIMARY KEY,
	codcli	int not null,
	fecven	datetime not null,
	estado	char(1) not null,
	CONSTRAINT fk_codcli
    FOREIGN KEY (codcli) REFERENCES cliente(codcli)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

create table venta_detalle(
	codven	int not null,
	rucssp	char(12) not null,
	correlativo	int not null,
	precio	numeric(6,2) not null,
	observacion	varchar(200) not null,
 	PRIMARY KEY (codven,rucssp, correlativo),
	CONSTRAINT fk_codven
    FOREIGN KEY (codven) REFERENCES venta(codven)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
	CONSTRAINT fk_rucssp_correlativo2
    FOREIGN KEY (rucssp,correlativo) REFERENCES rucssp_prenda(rucssp,correlativo)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

create table tipo_movimiento(
	codtipmov	int auto_increment not null PRIMARY KEY,
	destipmov	varchar(50) not null,
	estado	char(1) not null
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

insert into tipo_movimiento values
(1,'CREACION DE QR','A'),
(2,'VENTA','A'),
(3,'SALIDA DEL PAIS','A'),
(4,'INGRESO AL PAIS','A'),
(5,'TRANSFERENCIA DE PROPIEDAD','A');

create table movimiento_prenda(
	codmovpre	int auto_increment not null PRIMARY key,
	codtipmov	int not null,
	rucssp	char(12) not null,
	correlativo	int not null,
	codusu	int not null,
	fecmov	datetime not null,
	estado	char(1) not null,
	CONSTRAINT fk_codtipmov
    FOREIGN KEY (codtipmov) REFERENCES tipo_movimiento(codtipmov)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
	CONSTRAINT fk_rucssp_correlativo
    FOREIGN KEY (rucssp,correlativo) REFERENCES rucssp_prenda(rucssp,correlativo)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
	CONSTRAINT fk_codusu
    FOREIGN KEY (codusu) REFERENCES usuario(codusu)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

serfor1 
serfor2025